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