Inconsistency with SQL in ACS vs RPG

Hey All,

I am finding I have to double-test my SQL statements that get created in ACS Run SQL. Once they work in ACS, I copy/paste to RPG and have to compile, test and debug there as well.

This is normal testing of course but I have seen a couple anomalies/features perhaps ? 😊

I have found:
ACS handles join field size mismatches just fine. Embedded SQL in RPG does not always handle mismatched joins correctly. It will burp on mismatched field size, so I have to cast my join fields. Seems to work, but really…… ?

When you use CTEs and have a table alias name used at multiple levels, ACS handles the same alias name being used in multiple levels without complaining. RPG does not like this so each level needs to always unique table alias names on joins.

There are probably more anomalies, but I’ve wasted a chunk of time trying to debug some SQL statements that someone else created in ACS and why they were not working correctly in RPG.

Insights welcome from those doing lots of RPG embedded SQL.

Thanks in advance.

Regards,
Richard Schoen
Web: http://www.richardschoen.net
Email: richard@xxxxxxxxxxxxxxxxx<mailto:richard@xxxxxxxxxxxxxxxxx>