Re: SQL Statement length

[Jose Perez <joseenocperez.jp@xxxxxxxxx>]

Maybe you are getting this error because your assigning the result of the
query to a host variable and the  result is too large to be returned in
this variable.

On Thu, May 12, 2016 at 4:24 PM, Tyler, Matt <matt.tyler@xxxxxxxxxxxxxx>
wrote:

> There is no trick for the length if you set up your program correctly.
> The system allows for a 32740 length statement.  I admit to coding a 10K
> size statement but nothing that comes close to the max.   It would be nice
> to know more about the "variable truncated" error, I could not find a
> standard message with that phrase.
>
> DCL-S Stmtsql Varchar( 32740 );
> DCL-S Wherestmt Varchar( 8000 );
> DCL-S Filter Char(1) INZ('A');
>
> Stmtsql = 'select * from MYTABLE';
> If Filter > ' ';
>   If %LEN( Wherestmt) > 0;
>     Wherestmt += ' And ';
>   Endif;
>   Wherestmt += 'FILTER = ''?'';
> Endif;
>
> Stmtsql += ' ' +Wherestmt;
>
> Exec sql prepare PREP_SQL from :Stmtsql;
> If Sqlstate <> '00000';
>   // Do some error routine
> Endif;
>
> exec sql declare MYCURSOR for PREP_SQL;
>
> exec sql open MYCURSOR using :Filter;
> If Sqlstate <> '00000';
>   // Do some error routine
> Endif;
>
> etc...
>
> Thanks, Matt
>
>
> -----Original Message-----
> From: RPG400-L [mailto:rpg400-l-bounces@xxxxxxxxxxxx] On Behalf Of
> Graves, Chuck
> Sent: Thursday, May 12, 2016 2:03 PM
> To: rpg400-l@xxxxxxxxxxxx
> 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.