Why wouldn't DB2 let me have a column in WHERE clause?

Question

I have a remote DB2 database that I'm accessing through ODBC. When I have a query like

SELECT t.foo, t.bar, t.problemcolumn
FROM problemtable t
WHERE t.bar < 60;

it works like a charm, so the table and columns obviously exist.

But if I specify the problem column in the WHERE clause

SELECT t.foo, t.bar, t.problemcolumn
FROM problemtable t
WHERE t.problemcolumn = 'x'
AND t.bar < 60;

it gives me an error

Table "problemtable" does not exist.

What could possibly be the reason for this? I've double checked the spellings and I can trigger the problem just by including the problemcolumn in the where-clause.

Answer 1

Sorry for the obvious answer, but does the problemtable exist? Your code looks like pseudo code because of the table/column names, but be sure to double check your spelling. It's not a view which might even consist of joined tables across different databases/servers?

Answer 2

What is the actual SQL you're using? I don't see anything wrong with the example you put up. Try looking for misplaced commas and/or quotes that could be triggering the error.

Answer 3

Does it work with just:

SELECT t.foo, t.bar, t.problemcolumn
FROM problemtable t
WHERE t.problemcolumn = 'x'

Answer 4

Please run the next SQL statements. For me it works fine. If you still have this strange error, it will be a DB2 bug. I had some problems once with copying code from UNIX editors into Windows and vice versa. The SQL would not run, although it looked ok. Retyping the statement fixed my problem then.

create table problemtable ( foo varchar(10), bar int, problemcolumn varchar(10) );

SELECT t.foo, t.bar, t.problemcolumn FROM problemtable t WHERE t.bar < 60;

SELECT t.foo, t.bar, t.problemcolumn FROM problemtable t WHERE t.problemcolumn = 'x' AND t.bar < 60;

Answer 5

It think it should be work in DB2. What is your font-ent software?

Answer 6

DB2 sometimes gives misleading errors. You can try these troubleshooting steps:

1. Try executing the code through DBArtisan or DB2 Control Center and see if you get a proper result/ error message.
2. Try using schema_name.problemtable instead of just problemtable
3. Make sure that problemcolumn is of the same data type that you are comparing it with.