Re: SQL Statement length

[Charles Wilt <charles.wilt@xxxxxxxxx>]

I voted for that...

You might want to include a link to the wiki definiation
https://en.wikipedia.org/wiki/Here_document

Charles


On Fri, May 13, 2016 at 9:20 AM, Mark Murphy/STAR BASE Consulting Inc. <
mmurphy@xxxxxxxxxxxxxxx> wrote:

> I prefer coding dynamic sql statements in the following way:
>
>  stmt = '+
>    select field1, field2 +
>      from table +
>      where code = ''1'' +
>        and field1 >= 9 + 1 +
>      order by fields';
>
> This can be a problem some times as you can exceed the RPG string literal
> length limits, and there are readability issues for quotes and those extra
> plus signs at the end of each line. When I run into literal length limits I
> will take that string and break it up into smaller pieces like this:
>
> dcl-s stmt     Varchar(2048) Inz('');
>
> stmt  = 'select field1, field2';
> stmt += ' from table';
> stmt += ' where code = ''1''';
> stmt += '   and field1 >= 9 + 1';
> stmt += ' order by field';
>
> The problem I have with this is it can be even less readable with all the
> additional cruft around the actual sql statement, and it still has the
> quote escaping issues of the previous method. I have submitted an RFE to
> allow me to use a syntax called heredoc in some other programming
> languages. This would allow me to code something like the following:
>
> stmt = >>>DOC
>   select field1, field2
>     from table
>     where code = '1'
>       and field1 >= 9 + 1
>     order by fields
> DOC;
>
> The RFE is here:
> http://www.ibm.com/developerworks/rfe/execute?use_case=viewRfe&CR_ID=88357
>
> Mark Murphy
> STAR BASE Consulting, Inc.
> mmurphy@xxxxxxxxxxxxxxx
>
>
> -----"Graves, Chuck" <cgraves@xxxxxxxxxxxxxx> wrote: -----
> To: "rpg400-l@xxxxxxxxxxxx" <rpg400-l@xxxxxxxxxxxx>
> From: "Graves, Chuck" <cgraves@xxxxxxxxxxxxxx>
> Date: 05/12/2016 04:03PM
> Subject: SQL Statement length
>
>
> Is there a "trick" to programming a long complex (6000 character) SQL
> statement in RPG?
>
> We keep getting a "variable truncated" error message.
>
>
>
> [Rodda Paint Company]
> Chuck Graves
> Director of Information Systems
> Rodda Paint Co.<http://www.roddapaint.com>
> 6107 N. Marine Drive
> Portland, Oregon 97203
> (503) 737-6042
>
> --
> This is the RPG programming on the IBM i (AS/400 and iSeries) (RPG400-L)
> mailing list
> To post a message email: RPG400-L@xxxxxxxxxxxx
> To subscribe, unsubscribe, or change list options,
> visit: http://lists.midrange.com/mailman/listinfo/rpg400-l
> or email: RPG400-L-request@xxxxxxxxxxxx
> Before posting, please take a moment to review the archives
> at http://archive.midrange.com/rpg400-l.
>
> Please contact support@xxxxxxxxxxxx for any subscription related
> questions.
> --
> This is the RPG programming on the IBM i (AS/400 and iSeries) (RPG400-L)
> mailing list
> To post a message email: RPG400-L@xxxxxxxxxxxx
> To subscribe, unsubscribe, or change list options,
> visit: http://lists.midrange.com/mailman/listinfo/rpg400-l
> or email: RPG400-L-request@xxxxxxxxxxxx
> Before posting, please take a moment to review the archives
> at http://archive.midrange.com/rpg400-l.
>
> Please contact support@xxxxxxxxxxxx for any subscription related
> questions.