John
asked on
sql error:SQL0206N in db2 v9.1
Hi
I am getting the below error which selecting a column from the table
$ db2 "select COMPANY_CODE_CURRENCY from COGNOS.DIM_COMPANY_CODE_SP
SQL0206N "COMPANY_CODE_CURRENCY" is not valid in the context where it is
used. SQLSTATE=42703
But, the same column is accesable when I select by using a * or count(*)..Its giving result
Please suggets me a solution for this
ASKER
yes, db2 is connected and
As I mentioned, I am able to get the result with the select * and select count(*) and also able to query other columns except the above mentioned column "COMPANY_CODE_CURRENCY "
As I mentioned, I am able to get the result with the select * and select count(*) and also able to query other columns except the above mentioned column "COMPANY_CODE_CURRENCY "
Ok. Try the SQL below:
db2 "select * from sysibm.tables where table_schema = 'COGNOS' and table_name = 'COMPANY_CODE_CURRENCY'"
Kent
db2 "select * from sysibm.tables where table_schema = 'COGNOS' and table_name = 'COMPANY_CODE_CURRENCY'"
Kent
ASKER
Hi guys,
let me post the issue in details
the table description is : tablename = "DIM_COMPANY_CODE_SPEND"
Column Type Type
name schema name Length Scale Nulls
-------------------------- ---- --------- ------------------ -------- ----- ------
COMPANY_CODE_SKEY SYSIBM INTEGER 4 0 No
COMPANY_CODE SYSIBM CHARACTER 10 0 Yes
COMPANY_CODE_CURRENCY SYSIBM CHARACTER 3 0 Yes
COMPANY_CODE_DESCRIPTION SYSIBM CHARACTER 100 0 Yes
PARENT_COMPANY_SKEY SYSIBM INTEGER 4 0 No
CREATE_DATE SYSIBM DATE 4 0 No
CREATED_BY SYSIBM CHARACTER 10 0 Yes
LAST_UPDATE_DATE SYSIBM DATE 4 0 Yes
LAST_UPDATED_BY SYSIBM CHARACTER 10 0 Yes
when I query the table "DIM_COMPANY_CODE_SPEND" using the below queries
select * from cognos.DIM_COMPANY_CODE_SP END or
select count(*) from cognos.DIM_COMPANY_CODE_SP END or
select COMPANY_CODE, COMPANY_CODE_SKEY from cognos.DIM_COMPANY_CODE_SP END
I am able to get the result(even with other columns except the COMPANY_CODE_CURRENCY column)
when I query the db using the below query:
$ db2 "select COMPANY_CODE_CURRENCY from COGNOS.DIM_COMPANY_CODE_SP END"
SQL0206N "COMPANY_CODE_CURRENCY" is not valid in the context where it is
used. SQLSTATE=42703
I get the above error...
i tried reorg the table and set integrity with no use...Are there any solution to make this column accesable???
let me post the issue in details
the table description is : tablename = "DIM_COMPANY_CODE_SPEND"
Column Type Type
name schema name Length Scale Nulls
--------------------------
COMPANY_CODE_SKEY SYSIBM INTEGER 4 0 No
COMPANY_CODE SYSIBM CHARACTER 10 0 Yes
COMPANY_CODE_CURRENCY SYSIBM CHARACTER 3 0 Yes
COMPANY_CODE_DESCRIPTION SYSIBM CHARACTER 100 0 Yes
PARENT_COMPANY_SKEY SYSIBM INTEGER 4 0 No
CREATE_DATE SYSIBM DATE 4 0 No
CREATED_BY SYSIBM CHARACTER 10 0 Yes
LAST_UPDATE_DATE SYSIBM DATE 4 0 Yes
LAST_UPDATED_BY SYSIBM CHARACTER 10 0 Yes
when I query the table "DIM_COMPANY_CODE_SPEND" using the below queries
select * from cognos.DIM_COMPANY_CODE_SP
select count(*) from cognos.DIM_COMPANY_CODE_SP
select COMPANY_CODE, COMPANY_CODE_SKEY from cognos.DIM_COMPANY_CODE_SP
I am able to get the result(even with other columns except the COMPANY_CODE_CURRENCY column)
when I query the db using the below query:
$ db2 "select COMPANY_CODE_CURRENCY from COGNOS.DIM_COMPANY_CODE_SP
SQL0206N "COMPANY_CODE_CURRENCY" is not valid in the context where it is
used. SQLSTATE=42703
I get the above error...
i tried reorg the table and set integrity with no use...Are there any solution to make this column accesable???
it's pretty far fetch but try
db2 "select COMPANY_CODE_CURRENCY from cognos.DIM_COMPANY_CODE_SP END"
db2 "select COMPANY_CODE_CURRENCY from cognos.DIM_COMPANY_CODE_SP
ASKER
I have pasted the output of the query qith you sent me above :
$ db2 "select COMPANY_CODE_CURRENCY from COGNOS.DIM_COMPANY_CODE_SP END"
SQL0206N "COMPANY_CODE_CURRENCY" is not valid in the context where it is
used. SQLSTATE=42703
$ db2 "select COMPANY_CODE_CURRENCY from COGNOS.DIM_COMPANY_CODE_SP
SQL0206N "COMPANY_CODE_CURRENCY" is not valid in the context where it is
used. SQLSTATE=42703
ASKER
The issue is with the column "COMPANY_CODE_CURRENCY" only
db2 "select COMPANY_CODE_CURRENCY from COGNOS.DIM_COMPANY_CODE_SP END"
only when I query this particular column seperately I get an error:
SQL0206N "COMPANY_CODE_CURRENCY" is not valid in the context where it is
used. SQLSTATE=42703
db2 "select COMPANY_CODE_CURRENCY from COGNOS.DIM_COMPANY_CODE_SP
only when I query this particular column seperately I get an error:
SQL0206N "COMPANY_CODE_CURRENCY" is not valid in the context where it is
used. SQLSTATE=42703
Hi Jmartin,
The "description" that you posted above is for a table (view) in the SYSIBM schema. You're querying the COGNOS schema.
Kent
The "description" that you posted above is for a table (view) in the SYSIBM schema. You're querying the COGNOS schema.
Kent
ASKER
yes, its the cognos schema..I tried to query using the cognos schema
ASKER
hi Kdo,
the schema is cognos only,
Table/View Schema Type Creation time
-------------------------- ----- --------------- ----- --------------------------
DIM_ADDRESSABLE_SPEND COGNOS T 2011-02-08-13.29.50.693383
DIM_CATEGORY_LEVEL_SPEND COGNOS T 2011-02-08-13.29.51.531685
DIM_CLEARING_DOCUMENT_SPEN D COGNOS T 2011-02-08-13.29.52.750385
DIM_CLEARING_DOCUMENT_SPEN D_EX COGNOS T 2011-02-11-06.33.17.404292
the table schema is cognos only
i'm little confused here, Do you mean that the columns should also be under the cognos schema???
the schema is cognos only,
Table/View Schema Type Creation time
--------------------------
DIM_ADDRESSABLE_SPEND COGNOS T 2011-02-08-13.29.50.693383
DIM_CATEGORY_LEVEL_SPEND COGNOS T 2011-02-08-13.29.51.531685
DIM_CLEARING_DOCUMENT_SPEN
DIM_CLEARING_DOCUMENT_SPEN
the table schema is cognos only
i'm little confused here, Do you mean that the columns should also be under the cognos schema???
ASKER
DIM_COMPANY_CODE_SPEND COGNOS T 2011-02-09-09.11.54.975016
Do you have any user level authorities defined that could restrict access to column data?
Can you run the query from the schema owner? How about from the db2 administrator?
Kent
Can you run the query from the schema owner? How about from the db2 administrator?
Kent
ASKER
Hi guys the issue was with the column name, there was a space after the name while created so cud not query it..
thanks for the help
thanks for the help
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
i have found the solution for the issue
Have you done a "db2 connect" prior to this call to db2? You need an active connection to the database.
Kent