× The internal search function is temporarily non-functional. The current search engine is no longer viable and we are researching alternatives.
As a stop gap measure, we are using Google's custom search engine service.
If you know of an easy to use, open source, search engine ... please contact support@midrange.com.



--- Is there a "trick" to programming a long complex (6000 character) SQL statement in RPG?

AFAIK, The maximum string you can use to "contain" a dynamic SQL statement is 32740.

Are you doing Dynamic SQL?
Are you loading a field with the sql statement you are attempting to run?

Declare the statement variable as a varying length field of length 32740.

D STMTFIELD S 32740A VARYING


Load the field using small chucks of the statement.

Clear STMTFIELD;

STMTFIELD += ' WITH t1 as ( '
+'select * from sometable'
+'), t2 as (select * from table2)'
+' select * from t1 join t2 on t1.field=t2.field';

STMTFIELD +=' load part 2 of the statement';

STMTFIELD +=' load part 3 of the statement';

STMTFIELD +=' load part 4 of the statement';


You may be having that issue because the code is not closing the strings in every line.
I suggest closing the string on *every* line.
I prefer to never use the string continuation feature of RPGLE.
I find the code to be more error prone as enhancements are made.

This is the format I would suggest avoiding:

STMTFIELD =' WITH t1 as ( -
select * from sometable -
), t2 as (select * from table2) -
select * from t1 join t2 on t1.field=t2.field';

Note the use of "-" at the end of each line.
This could also have been a "+".
I find this code more error prone.

Chris Hiebert
Senior Programmer/Analyst
Disclaimer: Any views or opinions presented are solely those of the author and do not necessarily represent those of the company.

As an Amazon Associate we earn from qualifying purchases.

This thread ...

Replies:

Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2024 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].

Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.