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 ACTIONEMAILTRAN SID \
,ITEMTRANSID \
,COMPANYID \
,UPDATEDDATE \
,UPDATEDBY \
,DELETEFLAG \
FROM schema.tablenam e

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

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

If I remove ACTIONEMAILTRAN SID column and try again it works fine.

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

Moreover if I give just

select ACTIONEMAILTRAN SID
from schema.tablenam e

it fails.

What is wrong here?

schema.tablenam e structure:

Column Type Type
name schema name Length
Scale Nulls
------------------------------ --------- ------------------ --------
----- ------
ActionEmailTran sId 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 ACTIONEMAILTRAN SID \
,ITEMTRANSID \
,COMPANYID \
,UPDATEDDATE \
,UPDATEDBY \
,DELETEFLAG \
FROM schema.tablenam e

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

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

If I remove ACTIONEMAILTRAN SID column and try again it works fine.

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

Moreover if I give just

select ACTIONEMAILTRAN SID
from schema.tablenam e

it fails.

What is wrong here?

schema.tablenam e structure:

Column Type Type
name schema name Length
Scale Nulls
------------------------------ --------- ------------------ --------
----- ------
ActionEmailTran sId 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
ActionEmailTran sId 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 ( \"MixedCaseColu mn\"
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 "ActionEmailTra nsId" \
,ITEMTRANSID \
,COMPANYID \
,UPDATEDDATE \
,UPDATEDBY \
,DELETEFLAG \
FROM schema.tablenam e

and see how that goes. Note that:

SELECT ActionEmailTran sId \
,ITEMTRANSID \
,COMPANYID \
,UPDATEDDATE \
,UPDATEDBY \
,DELETEFLAG \
FROM schema.tablenam e

wont work because db2 will transform it to uppercase.
HTH
/Lennart

[pankaj_wolfhunter]

On Jun 27, 9:08 pm, Lennart <erik.lennart.j [e-mail] mwrote:

pankaj_wolfhun. [e-mail] wrote:

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

Query:

SELECT ACTIONEMAILTRAN SID \
,ITEMTRANSID \
,COMPANYID \
,UPDATEDDATE \
,UPDATEDBY \
,DELETEFLAG \
FROM schema.tablenam e

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

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

If I remove ACTIONEMAILTRAN SID column and try again it works fine.

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

Moreover if I give just

select ACTIONEMAILTRAN SID
from schema.tablenam e

it fails.

What is wrong here?

schema.tablenam e structure:

Column Type Type
name schema name Length
Scale Nulls
------------------------------ --------- ------------------ --------
----- ------
ActionEmailTran sId 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
ActionEmailTran sId 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 ( \"MixedCaseColu mn\"
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 "ActionEmailTra nsId" \
,ITEMTRANSID \
,COMPANYID \
,UPDATEDDATE \
,UPDATEDBY \
,DELETEFLAG \
FROM schema.tablenam e

and see how that goes. Note that:

SELECT ActionEmailTran sId \
,ITEMTRANSID \
,COMPANYID \
,UPDATEDDATE \
,UPDATEDBY \
,DELETEFLAG \
FROM schema.tablenam e

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.