ODBC PxPlus SQL ODBC Driver UNION

[chadl]

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.

[martinp]

What error is being returned?  MSG(-1) ?

[chadl]

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

[martinp]

"
Another method for getting data from two or more tables is to union select statements together. A union combines the results of two or more select statements. The select statements within the union must have the same number of columns. The columns must also have similar data types. In addition, the columns in each select statement must be in the same order."

To union three or more tables together, you can chain unions together one after another.

I saw this in the docs not sure if it helps.

[Devon Austen]

1) A good way to try to narrow down the issue may to be simplify the SQL statement by removing elements one at a time until it works then you know what it has a problem with. I know you said a simple select * from a union all select * from b works. Try adding the joins one by one to see if the issue is there.

2) Another thought is try using the old JOIN syntax to see if that helps { OJ tablea join tableb on a=b }

3) You could also test with the latest version to see if this is an issue that was fixed.

[chadl]

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!

[Devon Austen]

Check out the Example SQL documentation page for examples of the join syntax: https://manual.pvxplus.com?odbc/using_odbc_driver/example_sql.htm

The latest version is version 8.00.0000: https://pvxplus.com/pgsrvr.pvp?pg=dl_optional