Column or global variable not found

Question

The issue is when querying a table created using Aqua Data Studio in our IBM DB2 database, column names can't be specified unless I use the system name. Tables made a short time ago don't have this issue. The query can find the column names just fine in them. There also is not a problem using wildcards and the column names display just fine. For clarity:

this works : SELECT * FROM WUNITS.DVIRS
this works : SELECT ID___00001 FROM WUNITS.DVIRS
This doesn't : SELECT Id FROM WUNITS.DVIRS
This does work for older tables created the same way : SELECT Id FROM WUNITS.DVIRS

Here is a screenshot of the column definitions

What am I doing wrong?

Are you able to connect to the database without this client (Aqua Data Studio)? Say through linux console? It seems that this client is creating aliases to show the table structure instead of the real table structure. Try running on it: describe table WUNITS.DVIRS and edit your question with the result — Jorge Campos
– Jorge Campos, Commented Jan 4, 2018 at 0:15
select "Id" from mytable. Looks like the lower case has been enabled. — danny117
– danny117, Commented Jan 4, 2018 at 17:10

Charles · Accepted Answer · 2018-01-04 00:27:10Z

3

Since you mention system name, I'll assume you're using Db2 for IBM i....

I suspect you've created the table with quoted column names...you wouldn't see mixed case column names otherwise, they would be all CAPITALS.

For example:

create table dtcwilt.dvirs (
  "Id" bigint
  , "AssetNumber" varchar(25)
  , WoNumber bigint
  , WoStatusId int
)

Not sure what tool you're using to display the columns, but the ACS schema tool would show the quoted name.

The quotes are actually part of the name, you'd need to use the quoted name, with proper capitalization, in your select:

This should work:

SELECT "Id" FROM WUNITS.DVIRS

This would not:

SELECT "ID" FROM WUNITS.DVIRS

answered Jan 4, 2018 at 0:27

Charles

24.2k3 gold badges23 silver badges50 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Hoodlum Over a year ago

That's it! I've spent too much time in the Microsoft world. Now I just have to go fix a half dozen tables.

Collectives™ on Stack Overflow

Column or global variable not found

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related