Originally posted by: SystemAdmin

I am trying to use the Database Interface Designer. I have successfully used it to define a connection to my database (using ODBC), and examine table structures from the database. However, after defining some queries (which include variables), when I come to generate a type tree from a defined query I get a pop-up error message stating "The column definitions for the query could not be accessed".

What does this error mean ? The query in question is very simple:

SELECT * FROM EVENT.ADDRESS WHERE CUSTID = #CID2#

If I remove the WHERE clause altogether, then I can successfully generate. The column name in the WHERE clause looks correct ("CUSTID") ... How can I check the column definitions that WTX is aware of for the query ?!

Any assistance you guys can provide would be very much appreciated!

Thanks in advance,
Ben
#IBM-Websphere-Transformation-Extender
#DataExchange
#IBMSterlingTransformationExtender

Originally posted by: SystemAdmin

Is EVENT a qualifier? Shouldn't it be WHERE EVENT.CUSTID? I could be wrong, but that stood out. Is EVENT a qualifier? And/or maybe you need quotes around the #CD2#.
#IBM-Websphere-Transformation-Extender
#DataExchange
#IBMSterlingTransformationExtender

Originally posted by: LaurentB

Hi,

This message means that either the DID was unable to connect (but if you see the tables this is not the case) or it was unable to execute the query.

Since you can import when you remove the WHEN part, and your WHEN part uses a variable, I'd bet that the variable's value is undefined. When you use a variable in a query, you have to define a value (any value is fine, the query does not need to bring actual data, but executing it makes DID able to check it's validity).

So I'd recommend you define a value for CID2 in the "variables" part of your MDQ, and try again. This should do the trick.
#DataExchange
#IBMSterlingTransformationExtender
#IBM-Websphere-Transformation-Extender

Originally posted by: SystemAdmin

Thanks for the advice Guys ...

1. EVENT is the schema name of the database where the table resides. Given that I use this qualifier successfully with another query, I don't think this part of my syntax is at fault.

2. Thanks for the advice about defining a value for the variable. I had previously set a "dummy" value of 0 for CID2 in the variables section of MDQ. I then attempt to overwrite this at runtime.

3. I tried the single quotes around the variable name and that seems to have done the trick.

Thank you both for your help!
#IBM-Websphere-Transformation-Extender
#DataExchange
#IBMSterlingTransformationExtender