Re: My Dynamic SQL statement is not working

[Alan Campin <alan0307d@xxxxxxxxx>]

You cannot do a Select Into with a dynamic SQL statement. You must declare
a cursor, Prepare the statement, open the cursor and fetch the result and
close the cursor.

You might be better to use an Alias. .



On Fri, Oct 23, 2020 at 11:11 AM Alan Shore via RPG400-L <
rpg400-l@xxxxxxxxxxxxxxxxxx> wrote:

> Hi Tim
>
> Because the word
> count
> is a reserved word (I think)
> You will need to change the stand alone field - count - to a different name
> As well as in the SQL
> Alan Shore
> E-mail : ASHORE@xxxxxxxx
> Phone [O] : (631) 200-5019
> Phone [C] : (631) 880-8640
> 'If you're going through hell, keep going.'
> Winston Churchill
>
>
> -----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: [EXTERNAL] 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