Re: Inconsistency with SQL in ACS vs RPG

[Daniel Gross <daniel@xxxxxxxx>]

Hi Richard,

do you have an example - even if shortened?

I'm doing a lot of eSQL, and have never encountered something like that. And I'm constantly using CTEs. 

Kind regards,
Daniel


> Am 06.10.2025 um 17:30 schrieb Richard Schoen <richard@xxxxxxxxxxxxxxxxx>:
>
> 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>
>
> --
> This is the RPG programming on IBM i (RPG400-L) mailing list
> To post a message email: RPG400-L@xxxxxxxxxxxxxxxxxx
> To subscribe, unsubscribe, or change list options,
> visit: https://lists.midrange.com/mailman/listinfo/rpg400-l
> or email: RPG400-L-request@xxxxxxxxxxxxxxxxxx
> Before posting, please take a moment to review the archives
> at https://archive.midrange.com/rpg400-l.
>
> Please contact support@xxxxxxxxxxxxxxxxxxxx for any subscription related questions.