RE: SQL Statement length

--- 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.