Re: iACS Run SQL Scripts -- MIDRANGE-L

Rob,

You know what they say about assumptions :)

In CCSIDs 500 and 37 the @ is code point x'7C', but in CCSIDs such as 273
(German, Austria) the @ is x'B5'. In order to get the x'7C' code point in a
German setting you would indeed type § (x'7C' in CCSID 273.

From the RPG Reference manual:

Note: The $, #, and @ may appear as different symbols on some codepages. For
more information, see the iSeries Information Center globalization topic.

On Mon, Jan 21, 2019 at 11:24 AM Rob Berendt <rob@xxxxxxxxx> wrote:

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