RE: iACS Run SQL Scripts -- MIDRANGE-L

Birgitta,

Interesting. With it's popularity in email I assumed that the @ symbol was invariant.

-----Original Message-----
From: MIDRANGE-L <midrange-l-bounces@xxxxxxxxxxxxxxxxxx> On Behalf Of Birgitta Hauser
Sent: Monday, January 21, 2019 12:21 PM
To: 'Midrange Systems Technical Discussion' <midrange-l@xxxxxxxxxxxxxxxxxx>
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.

There is already a variable defined within this example:
Declare @PeriodEnd Integer

Here the dynamic compound statement with SELECT .... INTO and variable definitions.
begin
declare xPeriodEnd integer Not NULL default;
Declare xRego VarChar(50) Not NULL Default;
Declare xTSL VarChar(50) Not NULL Default;
Declare xStart_Date Date Not NULL Default;
Declare xEnd_Date Date Not NULL Default;

set xPeriodEnd = 20180901;
select OD.LODTPL as Rego,
LI.TSLTSL as TSL,
LI.TSLOSD as Start_Date,
OD.LODOED as End_Date
into xRego, xTSL, xStart_Date, xEnd_Date
from scccdbdta/CDBTSLPF LI
where LI.TSLLTY not in ('TST','LIM','ST')
and LI.TSLSTD > xPeriodEnd
order by LI.TSLTSL;
end;

... but what do you want to do?
Execiting the SELECT Statement and see the result?

If so just execute it directly and replace the @PeriodEnd with the real Period End.
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 > 2080901;

You don't need (and it even does not make sense) to execute the SELECT statement within an dynamic compound statement.

A dynamic compound statement will create a (temporary) stored procedure, execute the stored procedure immediately after it is created and then delete the stored procedure again.
Dynamic compound statements are very helpful if you want to execute a block of SQL statements and handle some errors within these statements.

BTW avoid special characters within your table, column, variable names, because they are not international.
For example on my German machine I cannot use a variable containing the @ character, but I'm able to handle variables containing the § character.

Mit freundlichen Grüßen / Best regards

Birgitta Hauser

"Shoot for the moon, even if you miss, you'll land among the stars." (Les
Brown)
"If you think education is expensive, try ignorance." (Derek Bok) "What is worse than training your staff and losing them? Not training them and keeping them!"
"Train people well enough so they can leave, treat them well enough so they don't want to." (Richard Branson)

-----Original Message-----
From: MIDRANGE-L <midrange-l-bounces@xxxxxxxxxxxxxxxxxx> On Behalf Of Bob Cagle
Sent: Montag, 21. Januar 2019 17:04
To: Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxxxxxxxx>
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

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