"Unable to Connect" to IBM DB2 Database via SQLStatement

[comish4lif]

I am trying to pull data from a DB2 data warehouse. I have set up the connection string and database name. I am using the following very simple query:

select *

from AEHRDW.FEMPRFDEM

where FEMPRFDEM_effto_dt = '9999-12-31'

AND FEMPRFDEM_CURR_IND = 'Y'

WITH ur;

And this is the text from the error that I am receiving:

The way that I read the error, it is breaking up my code in the middle of my where clause.

Does anyone know the way around this?

[v-shex-msft]

Hi @comish4lif,

Based on error message, your issue seems related to 'WITH' function.

Please check below link to know more about 'WITH' function or try to remove 'with ur' part and try again.

WITH - Common Table Expressions

Regards,

Xiaoxin Sheng

[comish4lif]

Thanks for the help, I've removed the "With UR" line of code but the query still will not execute. It presents the same error, but breaks the text at a different position in the Where clause.

Details: "Microsoft Db2 Client: An unexpected token "" was found following "". Expected tokens may include: "FDEM_CURR_IND = 'Y'
". SQLSTATE=42601 SQLCODE=-104"

If I just connect to my DB2 source, I can see the list of tables and view the previews. But my tables are large, and I can not load any of them, which is why I am trying to load some smaller extracts from the larger tables. Is there a more detailed DB2 tutorial - and not just the generic "Connect to a database" help?

[v-shex-msft]

Hi @comish4lif,

Based on research, this issue caused by syntax exception, you can try to copy current query to DB2 client and run it.

DB2 client will highlight the syntax that caused the error, you can modify on db2 client and copy the right query to power bi.

Regards,
Xiaoxin Sheng

[comish4lif · Accepted solution]

Thanks for all of the comments and suggestions. The SQL that I begin with was written and tested in IBM Data Studio.

But here's what solved it for me - I removed the semi-colon at the end of the SQL, and it works just fine now.