RE: iACS Run SQL Scripts -- MIDRANGE-L

Something like this?

set schema = mpms30;

create or replace variable @Begin Int;
set mpms30.@Begin =20190104;

select * from mpat where ptdate > mpms30.@Begin;

drop variable @Begin;

-----Original Message-----
From: MIDRANGE-L [mailto:midrange-l-bounces@xxxxxxxxxxxxxxxxxx] On Behalf Of Bob Cagle
Sent: Monday, January 21, 2019 10:04 AM
To: Midrange Systems Technical Discussion
Subject: RE: iACS Run SQL Scripts

I don't think it's possible to create a variable "on-the-fly" inside Run SQL Scripts. If I'm wrong about that, I'd also be interested in know how to accomplish this.

Thanks

Bob Cagle
IT Manager
Lynk

-----Original Message-----
From: MIDRANGE-L <midrange-l-bounces@xxxxxxxxxxxxxxxxxx> On Behalf Of Don Brown
Sent: Monday, January 21, 2019 2:29 AM
To: Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxx>
Subject: iACS Run SQL Scripts

After some of Rob's comments I have been playing with the iACS Run SQL Scripts and have been having great fun.

I now wanted to set up a script that has a variable defined for a date.

The error I am getting with this is as follows; SQL State: 42601 Vendor Code: -29 Message: [SQL0029] INTO clause missing
from embedded statement. Cause . . . . . : SELECT and VALUES INTO
statements embedded in a program must have an INTO clause to specify where
the results of the statement are to be placed. Recovery . . . : Add the
INTO clause to the statement and try the request again.

Here is the simplified version of the SQL

begin
declare @PeriodEnd integer;
set @PeriodEnd =
20180901;

select OD.LODTPL as Rego,
LI.TSLTSL as TSL,
LI.TSLOSD as
Start_Date,
OD.LODOED as
End_Date

from scccdbdta/CDBTSLPF LI

where LI.TSLLTY not in ('TST','LIM','ST') and
LI.TSLSTD > @PeriodEnd

order by LI.TSLTSL;
end;

Any suggestions greatly appreciated as google is not my friend today.

Thanks

Don Brown

--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list To post a message email: MIDRANGE-L@xxxxxxxxxxxxxxxxxx To subscribe, unsubscribe, or change list options,
visit: https://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives at https://archive.midrange.com/midrange-l.

Please contact support@xxxxxxxxxxxx for any subscription related questions.

Help support midrange.com by shopping at amazon.com with our affiliate link: https://amazon.midrange.com

[https://www.medtronsoftware.com/img/MedtronMinilogo.bmp] Kevin Bucknum
Senior Programmer Analyst
MEDDATA / MEDTRON
120 Innwood Drive
Covington LA 70433
Local: 985-893-2550
Toll Free: 877-893-2550
https://www.medtronsoftware.com

CONFIDENTIALITY NOTICE

This document and any accompanying this email transmission contain confidential information, belonging to the sender that is legally privileged. This information is intended only for the use of the individual or entity named above. The authorized recipient of this information is prohibited from disclosing this information to any other party and is required to destroy the information after its stated need has been fulfilled. If you are not the intended recipient, or the employee of agent responsible to deliver it to the intended recipient, you are hereby notified that any disclosure, copying, distribution or action taken in reliance on the contents of these documents is STRICTLY PROHIBITED. If you have received this email in error, please notify the sender immediately to arrange for return or destruction of these documents.