Query's Strange Behavior

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

pankaj_wolfhunter@yahoo.co.in - 27 Jun 2007 16:46 GMT

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

Reply to this Message

Lennart - 27 Jun 2007 17:08 GMT

> Greetings,
> Now this query is making me crazy. Working on it for almost 2 hrs
[quoted text clipped - 61 lines]
>
> 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

Reply to this Message

pankaj_wolfhunter@yahoo.co.in - 27 Jun 2007 18:10 GMT

> pankaj_wolfhun...@yahoo.co.in wrote:
> > Greetings,
[quoted text clipped - 107 lines]
>
> - Show quoted text -

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

Reply to this Message

Dave Hughes - 29 Jun 2007 18:38 GMT

[snip]

> > Since the colname is in lowercase in the catalog you will have to
> > continue using that. Try:
[quoted text clipped - 27 lines]
> It was eating my head from a long time. Actually still learning.
> Thanks again.

Just to clarify:

SQL is a case *sensitive* language, despite appearances to the
contrary. In other words, all identifiers (column names, table names,
etc.) are compared in a case sensitive manner.

Where this gets confusing for people new to SQL is that unquoted
identifiers are "folded" to uppercase automatically. Placing double
quotes around an identifier prevents this folding (and also permits
inclusion of other "unsafe" characters such as spaces in identifiers).

Hence:

identifier == IDENTIFIER
Identifier == IDENTIFIER
"IDENTIFIER" == IDENTIFIER
"Identifier" != IDENTIFIER

Just to confuse things even further, while the folding and quoting
mentioned above is from the SQL standard, it's one of the many areas in
which database implementations differ.

For example, MS SQL Server traditionally used square brackets [] to
delimit identifiers instead of double quotes (although I believe it now
permits double quotes too), however case sensitivity of identifiers
depends not on quoting, but on the collation order of the database.
MySQL is case sensitive regardless of quoting (unless it's on Windows
in which case it's case insensitive because the file-system is).
PostgreSQL is *nearly* standard in that it folds unquoted identifiers,
and uses double-quotes to prevent folding, but it folds to *lowercase*
instead of uppercase. Oracle, Firebird, and DB2 follow the SQL standard.

What a mess.

Cheers,

Dave.

--

Reply to this Message