Reserved Word Problem

keanhoo168 · #1 (**permalink**) 04-14-10, 03:53

I have a table in my database which having a field named "user".
When i try to issue a SQL query:
SELECT * FROM table_name WHERE user = 'ABC';
it always display me all the data in the table.
When i check through the reserved word list, i found that user is a reserved word.
Anyway i can retrieve the data with user = 'ABC'?

dr_te_z · #2 (**permalink**) 04-14-10, 04:25

Try this:

Code:

SELECT * FROM table_name WHERE "user"= 'ABC';

stolze · #3 (**permalink**) 04-14-10, 04:42

USER is a special register. If you don't use delimited identifiers or qualify the column with the table name (or the table's correlation name), DB2 will resolve the expression to the special register. So you have the following options:

Code:

SELECT * FROM table_name WHERE "USER"= 'ABC'

SELECT * FROM table_name WHERE table_name.user = 'ABC'

SELECT * FROM table_name AS t WHERE t.user = 'ABC'

Notes:
* the trailing ';' is not part of the SQL statement itself
* do not use lower-case in the delimited identifier, i.e. "user" because that will do a case-sensitive search for the column name is bound to fail if you didn't use delimited identifiers when creating the table