467,154 Members | 788 Online
Bytes | Developer Community
Ask Question

Home New Posts Topics Members FAQ

Post your question to a community of 467,154 developers. It's quick & easy.

Query's Strange Behavior

Greetings,
Now this query is making me crazy. Working on it for almost 2 hrs
but no output

Query:

SELECT ACTIONEMAILTRANSID \
,ITEMTRANSID \
,COMPANYID \
,UPDATEDDATE \
,UPDATEDBY \
,DELETEFLAG \
FROM schema.tablename

The above query is generating dynamically in a script and submitted to
db2
But its giving the following error

SQL0206N "ACTIONEMAILTRANSID" is not valid in the context where it is
used.
SQLSTATE=42703

If I remove ACTIONEMAILTRANSID column and try again it works fine.

Also if I do a
select *
from schema.tablename
it works fine.

Moreover if I give just

select ACTIONEMAILTRANSID
from schema.tablename

it fails.

What is wrong here?

schema.tablename structure:

Column Type Type
name schema name Length
Scale Nulls
------------------------------ --------- ------------------ --------
----- ------
ActionEmailTransId SYSIBM INTEGER
4 0 No
ITEMTRANSID SYSIBM INTEGER
4 0 No
COMPANYID SYSIBM INTEGER
4 0 No
UPDATEDDATE SYSIBM TIMESTAMP
10 0 No
UPDATEDBY SYSIBM VARCHAR
128 0 No
DELETEFLAG SYSIBM CHARACTER
1 0 No

DB Version:

Database server = DB2/6000 8.2.2

Any help would be appreciated

TIA

Jun 27 '07 #1
  • viewed: 1362
Share:
2 Replies
pa***************@yahoo.co.in wrote:
Greetings,
Now this query is making me crazy. Working on it for almost 2 hrs
but no output

Query:

SELECT ACTIONEMAILTRANSID \
,ITEMTRANSID \
,COMPANYID \
,UPDATEDDATE \
,UPDATEDBY \
,DELETEFLAG \
FROM schema.tablename

The above query is generating dynamically in a script and submitted to
db2
But its giving the following error

SQL0206N "ACTIONEMAILTRANSID" is not valid in the context where it is
used.
SQLSTATE=42703

If I remove ACTIONEMAILTRANSID column and try again it works fine.

Also if I do a
select *
from schema.tablename
it works fine.

Moreover if I give just

select ACTIONEMAILTRANSID
from schema.tablename

it fails.

What is wrong here?

schema.tablename structure:

Column Type Type
name schema name Length
Scale Nulls
------------------------------ --------- ------------------ --------
----- ------
ActionEmailTransId SYSIBM INTEGER
4 0 No
ITEMTRANSID SYSIBM INTEGER
4 0 No
COMPANYID SYSIBM INTEGER
4 0 No
UPDATEDDATE SYSIBM TIMESTAMP
10 0 No
UPDATEDBY SYSIBM VARCHAR
128 0 No
DELETEFLAG SYSIBM CHARACTER
1 0 No

DB Version:

Database server = DB2/6000 8.2.2

Any help would be appreciated

TIA
I think the problem is that you have forced db2 to store the name of
ActionEmailTransId in mixed case in the catalog. You can do that by
using " around your column during creation, i.e. (not recomended)

[lelle@53dbd181 lelle]$ db2 "create table test ( \"MixedCaseColumn\"
int, UppercaseColumn int)"

[lelle@53dbd181 lelle]$ db2 "select colname from syscat.columns where
tabname = 'TEST'"

COLNAME

--------------------------------------------------------------------------------------------------------------------------------
MixedCaseColumn

UPPERCASECOLUMN
Since the colname is in lowercase in the catalog you will have to
continue using that. Try:

SELECT "ActionEmailTransId" \
,ITEMTRANSID \
,COMPANYID \
,UPDATEDDATE \
,UPDATEDBY \
,DELETEFLAG \
FROM schema.tablename

and see how that goes. Note that:

SELECT ActionEmailTransId \
,ITEMTRANSID \
,COMPANYID \
,UPDATEDDATE \
,UPDATEDBY \
,DELETEFLAG \
FROM schema.tablename

wont work because db2 will transform it to uppercase.
HTH
/Lennart
Jun 27 '07 #2
On Jun 27, 9:08 pm, Lennart <erik.lennart.jons...@gmail.comwrote:
pankaj_wolfhun...@yahoo.co.in wrote:
Greetings,
Now this query is making me crazy. Working on it for almost 2 hrs
but no output
Query:
SELECT ACTIONEMAILTRANSID \
,ITEMTRANSID \
,COMPANYID \
,UPDATEDDATE \
,UPDATEDBY \
,DELETEFLAG \
FROM schema.tablename
The above query is generating dynamically in a script and submitted to
db2
But its giving the following error
SQL0206N "ACTIONEMAILTRANSID" is not valid in the context where it is
used.
SQLSTATE=42703
If I remove ACTIONEMAILTRANSID column and try again it works fine.
Also if I do a
select *
from schema.tablename
it works fine.
Moreover if I give just
select ACTIONEMAILTRANSID
from schema.tablename
it fails.
What is wrong here?
schema.tablename structure:
Column Type Type
name schema name Length
Scale Nulls
------------------------------ --------- ------------------ --------
----- ------
ActionEmailTransId SYSIBM INTEGER
4 0 No
ITEMTRANSID SYSIBM INTEGER
4 0 No
COMPANYID SYSIBM INTEGER
4 0 No
UPDATEDDATE SYSIBM TIMESTAMP
10 0 No
UPDATEDBY SYSIBM VARCHAR
128 0 No
DELETEFLAG SYSIBM CHARACTER
1 0 No
DB Version:
Database server = DB2/6000 8.2.2
Any help would be appreciated
TIA

I think the problem is that you have forced db2 to store the name of
ActionEmailTransId in mixed case in the catalog. You can do that by
using " around your column during creation, i.e. (not recomended)

[lelle@53dbd181 lelle]$ db2 "create table test ( \"MixedCaseColumn\"
int, UppercaseColumn int)"

[lelle@53dbd181 lelle]$ db2 "select colname from syscat.columns where
tabname = 'TEST'"

COLNAME

---------------------------------------------------------------------------*-----------------------------------------------------
MixedCaseColumn

UPPERCASECOLUMN

Since the colname is in lowercase in the catalog you will have to
continue using that. Try:

SELECT "ActionEmailTransId" \
,ITEMTRANSID \
,COMPANYID \
,UPDATEDDATE \
,UPDATEDBY \
,DELETEFLAG \
FROM schema.tablename

and see how that goes. Note that:

SELECT ActionEmailTransId \
,ITEMTRANSID \
,COMPANYID \
,UPDATEDDATE \
,UPDATEDBY \
,DELETEFLAG \
FROM schema.tablename

wont work because db2 will transform it to uppercase.

HTH
/Lennart- Hide quoted text -

- Show quoted text -
Thanks a zillion Lennart.
It was eating my head from a long time. Actually still learning.
Thanks again.

Jun 27 '07 #3

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

4 posts views Thread by Rik Moed | last post: by
39 posts views Thread by Chetan Raj | last post: by
8 posts views Thread by Evan Smith | last post: by
reply views Thread by salimshahzad@gmail.com | last post: by
reply views Thread by Hans Kesting | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.