Re: My Dynamic SQL statement is not working

[Jose Perez <joseenocperez.jp@xxxxxxxxx>]

Besides all the other solutions my colleagues gave you you can use EXTFILE
too and count with a variable file name.

On Fri, Oct 23, 2020 at 4:57 PM Paul Therrien <
paultherrien@xxxxxxxxxxxxxxxxxx> wrote:

> Sorry - forgot the into
>
> select number_rows
>   into :count
>   from qsys2.systablestat
> where table_name = trim(upper(:thefile));
>
> It helps if you know the library, (table_schema)
>
> select number_rows
>   into :count
>  from qsys2.systablestat
> where table_name = trim(upper(:thefile))
>    and table_schema = trim(upper(:thelibrary));
>
> Paul
>
> -----Original Message-----
> From: RPG400-L <rpg400-l-bounces@xxxxxxxxxxxxxxxxxx> On Behalf Of Paul
> Therrien
> Sent: Friday, October 23, 2020 4:52 PM
> To: 'RPG programming on IBM i' <rpg400-l@xxxxxxxxxxxxxxxxxx>
> Subject: RE: My Dynamic SQL statement is not working
>
> However, you can use system catalog:
>
>  select number_rows
>   from qsys2.systablestat
> where table_name = trim(upper(:thefile));
>
> It helps if you know the library, (table_schema)
>
> select number_rows
>   from qsys2.systablestat
> where table_name = trim(upper(:thefile))
>    and table_schema = trim(upper(:thelibrary));
>
>
> Paul
>
> -----Original Message-----
> From: RPG400-L <rpg400-l-bounces@xxxxxxxxxxxxxxxxxx> On Behalf Of
> Therrien,
> Paul via RPG400-L
> Sent: Friday, October 23, 2020 2:49 PM
> To: RPG programming on IBM i <rpg400-l@xxxxxxxxxxxxxxxxxx>
> Cc: Therrien, Paul <ptherrien@xxxxxxxxxxx>
> Subject: RE: My Dynamic SQL statement is not working
>
> You cannot do a dynamic select like that.
> Dynamic selects are for cursors.
> You need to define a cursor  and fetch the cursor result into your field.
> 'Execute immediate'  does not return data it updates/deletes/drops/inserts
> etc.
>
>
> -----Original Message-----
> From: RPG400-L [mailto:rpg400-l-bounces@xxxxxxxxxxxxxxxxxx] On Behalf Of
> Tim
> Feldmeier
> Sent: Friday, October 23, 2020 2:07 PM
> To: 'RPG400-L@xxxxxxxxxxxxxxxxxx' <RPG400-L@xxxxxxxxxxxxxxxxxx>
> Subject: My Dynamic SQL statement is not working
>
> The program log says COUNT is not defined or usable.  Any idea's?
>
> Dcl-s   stmt  char(100) ;
> Dcl-s   x     char(2)  inz('10')   ;
> Dcl-s  count  zoned(5) ;
> Dcl-s  thefile char(10);
>
>  thefile = ('co' + x);
>
>       stmt = 'select count(*) into :count' +
>              ' from ' +  %trim(thefile)  ;
>
>       exec sql  execute immediate :stmt ;
>
>       if count  > 0 ;
>       endif ;
>
>
>
>
> Thank You
>
> Tim Felmeier
> Programmer Analyst For Coyote System
> Roadrunner Transportation Systems
> 1431 Opus Place, Suite 530
> Downers Grove, IL 60515
> Phone# (331)-2337921
>
>
> The information transmitted is intended only for the person or entity to
> which it is addressed and may contain proprietary, business-confidential
> and/or privileged material. If you are not the intended recipient of this
> message you are hereby notified that any use, review, retransmission,
> dissemination, distribution, reproduction or any action taken in reliance
> upon this message is prohibited. If you received this in error, please
> contact the sender and delete the material from any computer.
> --
> 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.
>
> Help support midrange.com by shopping at amazon.com with our affiliate
> link:
> https://amazon.midrange.com
>
> --
> 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.
>
> Help support midrange.com by shopping at amazon.com with our affiliate
> link:
> https://amazon.midrange.com
>
> --
> 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.
>
> Help support midrange.com by shopping at amazon.com with our affiliate
> link:
> https://amazon.midrange.com
>
> --
> 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.
>
> Help support midrange.com by shopping at amazon.com with our affiliate
> link: https://amazon.midrange.com