Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » ElevateDB Technical Support » Support Forums » ElevateDB SQL » View Thread |
Messages 1 to 3 of 3 total |
get a value out of a script? |
Wed, Jul 25 2018 2:21 AM | Permanent Link |
Ian Branch | Hi Team,
Given the following script: {sql} SCRIPT (IN EndDate Date, OUT RecCount Integer) BEGIN EXECUTE IMMEDIATE 'insert into ajobtickets select * from jobtickets where (jobstatus = ''CL'' or jobstatus = ''CA'') and Created < ?' using EndDate; execute immediate 'delete from JobTickets where (jobstatus = ''CL'' or jobstatus = ''CA'') and Created < ?' using EndDate; execute immediate 'INSERT INTO ALineItems SELECT * from LineItems WHERE LineItems.JobNo IN (SELECT JobNo FROM AJobTickets)'; execute immediate 'DELETE FROM LineItems WHERE JobNo IN (SELECT JobNo from ALineItems)'; execute immediate 'INSERT INTO ATechData SELECT * from TechData WHERE TechData.JobNo IN (SELECT JobNo FROM AJobTickets)'; execute immediate 'DELETE FROM TechData WHERE JobNo IN (SELECT JobNo from ALineItems)'; END {sql} How do I populate RecCount with the number of records processed by the First or Second EXECUTE please? Regards & TIA, Ian |
Wed, Jul 25 2018 2:52 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | Ian
I think you're looking for the ROWSAFFECTED function but you may have to rewrite a little to use EXECUTE rather than EXECUTE IMMEDIATE. There's a good example in the OLH in EDBManager. Roy Lambert |
Wed, Jul 25 2018 4:09 AM | Permanent Link |
Ian Branch | Hi Roy,
Thanks for the pointer. Ended up with this.. {sql} SCRIPT (IN EndDate Date, OUT RecCount Integer) BEGIN DECLARE sEndDate VARCHAR(10) DEFAULT cast(EndDate as VARCHAR); DECLARE InsertStmt Statement; PREPARE InsertStmt from 'insert into ajobtickets select * from jobtickets where (jobstatus = ''CL'' or jobstatus = ''CA'') and Created < DATE '+QUOTEDSTR(sEndDate); EXECUTE InsertStmt; SET RecCount = RowsAffected(InsertStmt); .... .... {sql} Regards & Tks again, Ian |
This web page was last updated on Saturday, May 4, 2024 at 09:18 PM | Privacy PolicySite Map © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? E-mail us at info@elevatesoft.com |