Thank you all for the tips. I came up with the same information in the docs about the joins which is why I thought how I did them it would work.

I did try removing joins and such and making it as simple as possible. I do know if I run the top select (all the stuff before the UNION ALL) it runs perfectly fine and the same is true for the bottom part. The problem comes in when I add in the UNION ALL in between them. So I must have the syntax wrong for the UNION.

I am interested about that {OJ tablea join tableb on a=b} but I am unfamiliar with how to do that. Could you give me an example or some more pointers on how I should do that?

What is the latest version of the driver?

Thank you all for the time so far!

I am using DBeaver as the database manager. The error message is:

SQL Error [1015] [37000]: [1015][37000]: [PxPlus][SQL ODBC Driver]Expected lexical element not found: <identifier>

In SSRS report builder its:

An error occurred while executing the query.
ERROR [37000][PxPlus][SQL ODBC Driver]Expected lexical element not found: <identifier>

Thank you for any help, much appreciated

I am having trouble with a UNION between two select statements. The SELECTs work fine independently but when I try and combine with a UNION it does not work. A generic statement like this select customer_num from ar_cust_mast union all select vendor_num from ap_vendor_mast works fine. However the one attached in the txt file fails with what appears to be a syntax issue that I cannot figure out.

If anyone has any ideas, I would be forever grateful.

PxPlus SQL ODBC Driver v6.10.0000 is the version if thats helpful.