From derby-user-return-13426-apmail-db-derby-user-archive=db.apache.org@db.apache.org Thu Feb 24 19:21:03 2011 Return-Path: Delivered-To: apmail-db-derby-user-archive@www.apache.org Received: (qmail 99864 invoked from network); 24 Feb 2011 19:21:03 -0000 Received: from hermes.apache.org (HELO mail.apache.org) (140.211.11.3) by minotaur.apache.org with SMTP; 24 Feb 2011 19:21:03 -0000 Received: (qmail 2246 invoked by uid 500); 24 Feb 2011 19:21:03 -0000 Delivered-To: apmail-db-derby-user-archive@db.apache.org Received: (qmail 2089 invoked by uid 500); 24 Feb 2011 19:21:02 -0000 Mailing-List: contact derby-user-help@db.apache.org; run by ezmlm Precedence: bulk list-help: list-unsubscribe: List-Post: List-Id: Reply-To: "Derby Discussion" Delivered-To: mailing list derby-user@db.apache.org Received: (qmail 2082 invoked by uid 99); 24 Feb 2011 19:21:01 -0000 Received: from athena.apache.org (HELO athena.apache.org) (140.211.11.136) by apache.org (qpsmtpd/0.29) with ESMTP; Thu, 24 Feb 2011 19:21:01 +0000 X-ASF-Spam-Status: No, hits=-0.0 required=5.0 tests=SPF_PASS X-Spam-Check-By: apache.org Received-SPF: pass (athena.apache.org: domain of Sameer_Mehta@symantec.com designates 216.10.195.243 as permitted sender) Received: from [216.10.195.243] (HELO tus1smtoutpex03.symantec.com) (216.10.195.243) by apache.org (qpsmtpd/0.29) with ESMTP; Thu, 24 Feb 2011 19:20:54 +0000 X-AuditID: d80ac3f3-b7b64ae00000342e-76-4d66af81b9ad Received: from tus1opsmtapin01.ges.symantec.com (tus1opsmtapin01.ges.symantec.com [192.168.214.43]) by tus1smtoutpex03.symantec.com (Symantec Brightmail Gateway) with SMTP id 22.6E.13358.18FA66D4; Thu, 24 Feb 2011 12:20:33 -0700 (MST) Received: from [155.64.220.139] (helo=TUS1XCHHUBPIN03.SYMC.SYMANTEC.COM) by tus1opsmtapin01.ges.symantec.com with esmtp (Exim 4.72) (envelope-from ) id 1Psgjp-0007Bc-Mc for derby-user@db.apache.org; Thu, 24 Feb 2011 11:20:33 -0800 Received: from TUS1XCHEVSPIN34.SYMC.SYMANTEC.COM ([155.64.220.148]) by TUS1XCHHUBPIN03.SYMC.SYMANTEC.COM ([155.64.220.139]) with mapi; Thu, 24 Feb 2011 11:20:33 -0800 From: Sameer Mehta To: Sameer Mehta , "derby-user@db.apache.org" CC: David Van Couvering , Matt Munz Date: Thu, 24 Feb 2011 11:18:02 -0800 Subject: RE: SQLException - Column 'COLUMN2' is either not in any table in the FROM list or appears within a join specification and is outside the scope of the join specification ..... Thread-Topic: SQLException - Column 'COLUMN2' is either not in any table in the FROM list or appears within a join specification and is outside the scope of the join specification ..... Thread-Index: AQHL0r33vPi9i9Ds80OTEx5fEhTQ6pQRCZtH Message-ID: <3E6DF46E67210942B7919419E636D02914AA514306@TUS1XCHEVSPIN34.SYMC.SYMANTEC.COM> References: <3E6DF46E67210942B7919419E636D02914AA5142FC@TUS1XCHEVSPIN34.SYMC.SYMANTEC.COM> In-Reply-To: <3E6DF46E67210942B7919419E636D02914AA5142FC@TUS1XCHEVSPIN34.SYMC.SYMANTEC.COM> Accept-Language: en-US Content-Language: en-US X-MS-Has-Attach: X-MS-TNEF-Correlator: acceptlanguage: en-US Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: quoted-printable MIME-Version: 1.0 X-Brightmail-Tracker: H4sIAAAAAAAAA+NgFprCIsWRmVeSWpSXmKPExsVyYMU1bd3G9Wm+BneuGFssvdTN4sDocarj HXsAYxSXTUpqTmZZapG+XQJXxukXsQWXVSvWTZ/J3MD4SK6LkZNDQsBE4lnDW1YIW0ziwr31 bF2MXBxCAm8YJS7duscI4XQySbSeOMYC4axilGhaO5kNpIVNQE/i3rxfLCC2iECqxOknf5hA bGaBBIk5X8+CjWURUJX4cv4OE0izsMBeRokTl26wgzgiAvsYJd4f3cAM0W0kceTyT3YQm1cg SmLirw6wuBCQ/W5LNyOIzSkQLTH/1QuwqYxAx34/tQZqm7jErSfzmSCeEJBYsuc8M4QtKvHy 8T+oelGJO+3rGSHqdSQW7P7EBmFrSyxb+JoZYq+gxMmZT1gmMIrPQjJ2FpKWWUhaZiFpWcDI sopRpqS02LA4tyS/tKQgtcLAWK+4MjcRGFPJesn5uZsYgXF1g+vw5x2MO1dpH2IU4GBU4uHN WprmK8SaWAZUeYhRgoNZSYR39TqgEG9KYmVValF+fFFpTmrxIUZpDhYlcd666dy+QgLpiSWp 2ampBalFMFkmDk6pBsai/0ol+/8Gf5gbe7qyX03m0+yIGwstlA/tKpvzxfPeeu9p+7133w77 4zHNU+rg98L+/QwT+u5P+SWQ2hr3feOC8hm/Aj9cE2eblTl32ReBvEbb5JU3lx2znxnZXOuc HftEgb/c8PbttK29vry5r57dZdOY9G6Fg9urM/8/mc36UsZ3QdOB/9g6JZbijERDLeai4kQA 61pCW6cCAAA= Hello All,=20 I found a workaround to this issue i.e. using comma "," as a delimiter inst= ead of a pipe. Is this a known issue for CALL SYSCS_UTIL.SYSCS_IMPORT_DATA = stored procedure ? Or which are valid delimiters to SYSCS_UTIL.SYSCS_IMPORT= _DATA ? thanks, ~sameer=20 ________________________________________ From: Sameer Mehta Sent: Tuesday, February 22, 2011 10:40 AM To: derby-user@db.apache.org Cc: David Van Couvering; Matt Munz Subject: SQLException - Column 'COLUMN2' is either not in any table in the = FROM list or appears within a join specification and is outside the scope o= f the join specification ..... Hello All, I am trying to import data from a csv file to a table using CALL SYSCS_UTIL= .SYSCS_IMPORT_DATA with the following arguments - arg 1 - schema name (I tried using null as well but it gave me the same err= or message i.e. Column 'COLUMN2' is either not in any table in the FROM li= st or appears within a join specification and is outside the scope of the j= oin specification or appears in a HAVING clause and is not in the GROUP BY = list. If this is a CREATE or ALTER TABLE statement then 'COLUMN2' is not a= column in the target table.' was thrown while evaluating an expression. ) arg 2 - table name arg 3 - columns to be imported arg 4 - null arg 5 - csv file name ( c:/vontu/protect/plugins/senders.csv ) arg 6 - csv file delimited ("|") arg 7 - character delimiter (null) arg 8 - charset of the input data (utf-8) arg 9 - 0 (since I am inserting data and not replacing) In doing so, I always get the following error - Column 'COLUMN2' is either = not in any table in the FROM list or appears within a join specification an= d is outside the scope of the join specification or appears in a HAVING cla= use and is not in the GROUP BY list. If this is a CREATE or ALTER TABLE st= atement then 'COLUMN2' is not a column in the target table.' was thrown whi= le evaluating an expression. Here's what I am trying to do - 1. Create an in-memory database in derby using "jdbc:derby:memory:CSV_LOOKU= P_DATABASE; create=3Dtrue" 2. Create a table using SQL "CREATE TABLE LOOKUP(SID varchar(50),EMAIL_ADD= RESS varchar(50),FIRST_NAME varchar(50),LAST_NAME varchar(50))" 3. Create a table index using SQL "CREATE INDEX EMAIL_ADDRESSIndex ON LOOKU= P(EMAIL_ADDRESS)" 4. Get JDBC connection using the following URL "jdbc:derby:memory:CSV_LOOKU= P_DATABASE" 5. Import data from csv c:/vontu/protect/plugins/senders.csv to LOOKUP tab= le which has columns SID,EMAIL_ADDRESS,FIRST_NAME,LAST_NAME (csv file has c= olumns structured in this format as well) Step 5 gives the following exception - 18 Feb 2011 18:17:35,215- Thread: 15 SEVERE [com.vontu.lookup.csv.CsvLookup= ] The exception 'java.sql.SQLException: Column 'COLUMN2' is either not in a= ny table in the FROM list or appears within a join specification and is out= side the scope of the join specification or appears in a HAVING clause and = is not in the GROUP BY list. If this is a CREATE or ALTER TABLE statement = then 'COLUMN2' is not a column in the target table.' was thrown while evalu= ating an expression. 18 Feb 2011 18:17:35,218- Thread: 15 SEVERE [com.vontu.lookup.csv.CsvLookup= ] Failed to initialize Csv lookup. Cause: com.vontu.lookup.common.InitializationException: The exception 'java.sql.SQ= LException: Column 'COLUMN2' is either not in any table in the FROM list or= appears within a join specification and is outside the scope of the join s= pecification or appears in a HAVING clause and is not in the GROUP BY list.= If this is a CREATE or ALTER TABLE statement then 'COLUMN2' is not a colu= mn in the target table.' was thrown while evaluating an expression. com.vontu.lookup.common.InitializationException: The exception 'java.sql.SQ= LException: Column 'COLUMN2' is either not in any table in the FROM list or= appears within a join specification and is outside the scope of the join s= pecification or appears in a HAVING clause and is not in the GROUP BY list.= If this is a CREATE or ALTER TABLE statement then 'COLUMN2' is not a colu= mn in the target table.' was thrown while evaluating an expression. My CSV file has the following format - SID|EMAIL_ADDRESS|FIRST_NAME|LAST_NAME U639854|WFEAW.L.CAEMG@JPMCHASE.COM|LANCE|FEHER Manifest file as part of the derby jar contains -- Manifest-Version: 1.0 Ant-Version: Apache Ant 1.7.1 Created-By: 1.5.0_19-137 (Apple Inc.) Bundle-Vendor: Apache Software Foundation Bundle-Name: Apache Derby 10.5 Bundle-Version: 10.5.3000000.802917 Sealed: true Bundle-Activator: org.apache.derby.osgi.EmbeddedActivator Bundle-SymbolicName: derby DynamicImport-Package: * Is this a known issue OR am I missing something ? thanks, ~sameer