Query's Strange Behavior

[pankaj_wolfhunter]

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

[Lennart]

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

[pankaj_wolfhunter]

On Jun 27, 9:08 pm, Lennart <[e-mail]:

[e-mail] 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.