RE: How can I do the following using execute immediate?

dcl-s stmt varchar(500) inz;
dcl-s valueToSearchFor varchar(20) inz;
dcl-s valueFound int(10) inz;
dcl-s library varchar(10) inz;
dcl-s file varchar(10) inz;
dcl-s member varchar(10) inz;

// valueToSearchFor = 'your search';
//library = ...
//file = ...
//member = ...

stmt = 'CREATE ALIAS QTEMP/SRCREF FOR '
+ %trim(library)
+ '/'+ %trim(file)
+'("' + %trim(member) + '")' ;
EXEC SQL
EXECUTE IMMEDIATE :stmt;
if SQLSTATE = '00000';
value1Found = 0;
stmt = ' VALUES '
+ '(SELECT '
+ ' count(*) '
+ ' FROM QTEMP/SRCREF'
+ ' WHERE REGEXP_LIKE(SRCDTA, ?,''i'')'
+ ') INTO ?' ;

EXEC SQL PREPARE PS_member FROM :stmt;
EXEC SQL EXECUTE PS_member
USING :valueToSearchFor
,:valueFound;

if valueFound > 0;
//do it
endif;


EXEC SQL DROP ALIAS QTEMP/SRCREF;
endif;
Juan Concepcion | CU*Answers | Applications Programmer
P: 616.285.5711 x249 | T: 800.327.3478 | F: 616.285.5735 | juan.concepcion@xxxxxxxxxxxxx | The information contained in this message or any attached document is confidential and intended only for individuals to whom it is addressed.  If you received this message in error, please inform me immediately. Then delete the email message and any attachments.  Any unauthorized use, distribution, or copying of this information is prohibited.

-----Original Message-----
From: RPG400-L <rpg400-l-bounces@xxxxxxxxxxxxxxxxxx> On Behalf Of Charles Wilt
Sent: Tuesday, March 1, 2022 9:39 AM
To: RPG programming on IBM i <rpg400-l@xxxxxxxxxxxxxxxxxx>
Subject: Re: How can I do the following using execute immediate?

WARNING: EXTERNAL EMAIL. Do NOT CLICK on links or open attachments unless you are expecting it or have verified with the sender.


Daniel has the correct syntax with the parameter marker...

Didn't mean to mislead you, I shouldn't try to answer questions quickly at the end of the day. :)

Charles

On Mon, Feb 28, 2022 at 3:43 PM Daniel Gross <daniel@xxxxxxxx> wrote:

You can use something similar to „into“ with dynamic SQL:

sqlStr = ‘values (select count(*) …) into ?‘;
exec sql prepare stmt from :sqlStr;
exec sql execute stmt using :wCount;

I use this technique all the time.

HTH
Daniel

Von meinem iPhone gesendet

Am 28.02.2022 um 22:52 schrieb dfreinkel@xxxxxxxxxxxxxxxxx:

I need to scan a source member and so I have to process the
members. The member name is random so it cannot be performed as a
standard SQL

statement.

The SQL command would be:



Select count(*) into :wCount

From qtemp.alias

Where srcdta like '%some_value%';



I am using EXECUTE IMMEDIATE :wcommand.



How does one code the command to get the execute immediate to return
the value of count into :wCount?



TIA,

Darryl Freinkel

A4G

Telephone: 770.321.8562 Mobile: 678.355.8562



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