By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
437,610 Members | 1,672 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 437,610 IT Pros & Developers. It's quick & easy.

Query's Strange Behavior

P: n/a
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
Share this Question
Share on Google+
2 Replies


P: n/a
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

P: n/a
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.