You are viewing a plain text version of this content. The canonical link for it is here.
Posted to derby-user@db.apache.org by Sameer Mehta <Sa...@symantec.com> on 2011/02/22 19:40:07 UTC
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|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
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 .....
Posted by Bryan Pendleton <bp...@gmail.com>.
> 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.
I don't know much about com.vontu.lookup.common, nor have I seen the message
"Failed to initialize Csv lookup" before, but since that's not Derby code, that's
not surprising. Probably the vontu.com library is generating and issuing an invalid query.
Can you post the full stacktrace from your derby.log file?
thanks,
bryan
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 .....
Posted by Knut Anders Hatlen <kn...@oracle.com>.
Sameer Mehta <Sa...@symantec.com> writes:
> 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 ?
Hi Sameer,
I'm not aware of any bug that prevents using '|' as a delimiter. In
fact, I just created a file with fields separated by pipes and called
SYSCS_IMPORT_DATA, and it worked fine for me.
If you have a test case that you can share and that shows this problem,
it would be great if you could file a bug report at
https://issues.apache.org/jira/browse/DERBY and upload the test case.
Thanks,
--
Knut Anders
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 .....
Posted by Sameer Mehta <Sa...@symantec.com>.
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: 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 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|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