[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1158
  • Last Modified:

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_SPEND"
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
0
jmartin_v
Asked:
jmartin_v
  • 9
  • 5
1 Solution
 
Kent OlsenData Warehouse Architect / DBACommented:
Hi jmartin,

Have you done a "db2 connect" prior to this call to db2?  You need an active connection to the database.


Kent
0
 
jmartin_vAuthor Commented:
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 "
0
 
Kent OlsenData Warehouse Architect / DBACommented:
Ok.  Try the SQL below:

db2  "select * from sysibm.tables where table_schema = 'COGNOS' and table_name = 'COMPANY_CODE_CURRENCY'"


Kent
0
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 
jmartin_vAuthor Commented:
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_SPEND or

select count(*) from cognos.DIM_COMPANY_CODE_SPEND or

select COMPANY_CODE, COMPANY_CODE_SKEY from cognos.DIM_COMPANY_CODE_SPEND

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_SPEND"
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???


0
 
momi_sabagCommented:
it's pretty far fetch but try

db2 "select COMPANY_CODE_CURRENCY from cognos.DIM_COMPANY_CODE_SPEND"
0
 
jmartin_vAuthor Commented:
I have pasted the output of the query qith you sent me above :

$ db2 "select COMPANY_CODE_CURRENCY from COGNOS.DIM_COMPANY_CODE_SPEND"

SQL0206N  "COMPANY_CODE_CURRENCY" is not valid in the context where it is
used.  SQLSTATE=42703
0
 
jmartin_vAuthor Commented:
The issue is with the column  "COMPANY_CODE_CURRENCY" only

db2 "select COMPANY_CODE_CURRENCY from COGNOS.DIM_COMPANY_CODE_SPEND"

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

0
 
Kent OlsenData Warehouse Architect / DBACommented:
Hi Jmartin,

The "description" that you posted above is for a table (view) in the SYSIBM schema.  You're querying the COGNOS schema.


Kent
0
 
jmartin_vAuthor Commented:
yes, its the cognos schema..I tried to query using the cognos schema
0
 
jmartin_vAuthor Commented:
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_SPEND     COGNOS          T     2011-02-08-13.29.52.750385
DIM_CLEARING_DOCUMENT_SPEND_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???

0
 
jmartin_vAuthor Commented:
DIM_COMPANY_CODE_SPEND          COGNOS          T     2011-02-09-09.11.54.975016
0
 
Kent OlsenData Warehouse Architect / DBACommented:
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
0
 
jmartin_vAuthor Commented:
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
0
 
Kent OlsenData Warehouse Architect / DBACommented:
Ahhh...

One of the gotchas of quoting names.....


Glad that you found it,
Kent
0
 
jmartin_vAuthor Commented:
i have found the solution for the issue
0

Featured Post

Upgrade your Question Security!

Add Premium security features to your question to ensure its privacy or anonymity. Learn more about your ability to control Question Security today.

  • 9
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now