Hello All, I found a workaround to this issue i.e. using comma "," as a delimiter instead 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 ________________________________________ From: Sameer Mehta Sent: Tuesday, February 22, 2011 10:40 AM To: [email protected] 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 of 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 error message i.e. 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 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 and is outside 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 evaluating an expression. Here's what I am trying to do - 1. Create an in-memory database in derby using "jdbc:derby:memory:CSV_LOOKUP_DATABASE; create=true" 2. Create a table using SQL "CREATE TABLE LOOKUP(SID varchar(50),EMAIL_ADDRESS varchar(50),FIRST_NAME varchar(50),LAST_NAME varchar(50))" 3. Create a table index using SQL "CREATE INDEX EMAIL_ADDRESSIndex ON LOOKUP(EMAIL_ADDRESS)" 4. Get JDBC connection using the following URL "jdbc:derby:memory:CSV_LOOKUP_DATABASE" 5. Import data from csv c:/vontu/protect/plugins/senders.csv to LOOKUP table which has columns SID,EMAIL_ADDRESS,FIRST_NAME,LAST_NAME (csv file has columns 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 any table in the FROM list or appears within a join specification and is outside 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 evaluating 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.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 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. com.vontu.lookup.common.InitializationException: The exception 'java.sql.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 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. My CSV file has the following format - SID|EMAIL_ADDRESS|FIRST_NAME|LAST_NAME U639854|[email protected]|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
