Hi Rob
Thanks for your reply
The file that these variables are being entered into is not a dummy file, but a control file, which is to be used by other programs
However - looking at your code doesn't seem to be that much different from mine
Except in mine - when attempting to insert the SQL variables - its failing because the variable is null - when according to my understanding - it shouldn't be
Alan Shore
E-mail : ASHORE@xxxxxxxx
Phone [O] : (631) 200-5019
Phone [C] : (631) 880-8640
'If you're going through hell, keep going.'
Winston Churchill
-----Original Message-----
From: MIDRANGE-L [mailto:midrange-l-bounces@xxxxxxxxxxxxxxxxxx] On Behalf Of Rob Berendt
Sent: Thursday, January 7, 2021 2:55 PM
To: Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxxxxxxxx>
Subject: [EXTERNAL] RE: Problem with SQL variables
Forget using the dummy file and use the variables directly.
This is a working run sql scripts session:
CREATE or REPLACE VARIABLE rob.HIRADAS400FILENAMESHPD CHARACTER (10);
SET rob.HIRADAS400FILENAMESHPD = 'HRSHIPPEDD';
values rob.HIRADAS400FILENAMESHPD;
create table rob.HRCTRLFP (
testcolOne char(10)
);
insert into rob.hrctrlfp (
testcolOne)
values (
rob.HIRADAS400FILENAMESHPD
);
select * from rob.hrctrlfp;
Rob Berendt
--
IBM Certified System Administrator - IBM i 6.1 Group Dekko Dept 1600 Mail to: 7310 Innovation Blvd, Suite 104
Ft. Wayne, IN 46818
Ship to: 7310 Innovation Blvd, Dock 9C
Ft. Wayne, IN 46818
http://www.dekko.com
-----Original Message-----
From: MIDRANGE-L <midrange-l-bounces@xxxxxxxxxxxxxxxxxx> On Behalf Of Alan Shore via MIDRANGE-L
Sent: Thursday, January 7, 2021 2:35 PM
To: midrange-l@xxxxxxxxxxxxxxxxxx
Cc: Alan Shore <ashore@xxxxxxxx>
Subject: Problem with SQL variables
CAUTION: This email originated from outside of the organization. Do not click links or open attachments unless you recognize the sender and know the content is safe.
Hi everyone
We are on V7r3
I thought I understood how SQL variables worked Now Im thinking that I don't Im testing a CLLE program Here is the code that I am having a problem with
0093.00 /* The file NBJDTA/HRSHIPPEDD is the delta for the Customer extract */
0094.00 /* This first job initializes the SQL variables for the Customer extract */
0095.00 RUNSQLSTM SRCFILE(AXSLIB/QTXTSRC) SRCMBR(HRVARJA03A) +
0096.00 COMMIT(*NONE)
0097.00 RUNSQLSTM SRCFILE(AXSLIB/QTXTSRC) SRCMBR(HRVARJB03A) +
0098.00 COMMIT(*NONE)
0099.00 /* The next job inserts the SQL Variables into the control file */
0100.00 RUNSQL SQL('insert into NBJDTA.HRCTRLFP (select +
0101.00 nbjdta.HIRADINTERFACENAMESHPD, +
0102.00 nbjdta.HIRADFILENAMESHPD, +
0103.00 nbjdta.HIRADAS400FILENAMESHPD, +
0104.00 nbjdta.HIRADBATCH#SHPD, +
0105.00 nbjdta.HIRADSTATUSSHPD, +
0106.00 nbjdta.HIRADBATCHTIMESTAMPSHPD, +
0107.00 nbjdta.HIRADODITIMESTAMPSHPD from +
0108.00 SYSIBM.SYSDUMMY1)') COMMIT(*NONE)
0109.00
Here is HRVARJA03A
CREATE or REPLACE VARIABLE NBJDTA.HIRADAS400FILENAMESHPD CHARACTER (10);
SET NBJDTA.HIRADAS400FILENAMESHPD = 'HRSHIPPEDD';
CREATE or REPLACE VARIABLE NBJDTA.HIRADSTATUSSHPD CHARACTER (1);
SET NBJDTA.HIRADSTATUSSHPD = 'N';
Here is HRVARJB03A
CREATE or REPLACE VARIABLE NBJDTA.HIRADINTERFACENAMESHPD CHARACTER (50);
SET NBJDTA.HIRADINTERFACENAMESHPD =
'HIGH RADIUS SHIPMENT EXTRACT';
CREATE or REPLACE VARIABLE NBJDTA.HIRADFILENAMESHPD CHARACTER (100);
SET NBJDTA.HIRADFILENAMESHPD = ' ';
CREATE or REPLACE VARIABLE NBJDTA.HIRADBATCH#SHPD DECIMAL (12,0);
SET NBJDTA.HIRADBATCH#SHPD =
( NBTYGPL.NEXTNUMBERSQL('HIGH RADIUS INTERFACE'));
CREATE or REPLACE VARIABLE NBJDTA.HIRADFINALSTATUSSHPD CHARACTER (1);
SET NBJDTA.HIRADFINALSTATUSSHPD = 'U';
CREATE or REPLACE VARIABLE NBJDTA.HIRADBATCHTIMESTAMPSHPD TIMESTAMP;
SET NBJDTA.HIRADBATCHTIMESTAMPSHPD = CURRENT_TIMESTAMP;
CREATE or REPLACE VARIABLE NBJDTA.HIRADODITIMESTAMPSHPD TIMESTAMP;
SET NBJDTA.HIRADODITIMESTAMPSHPD = CURRENT_TIMESTAMP;
When I test this code (interactively) - both HRVARJA03A & HRVARJB03A are successful Well - the spool file that is generated shows the variables have been created and populated However - the insert fails with Null values not allowed in column or variable HRINERFNAM.
Which is being populated from the variable nbjdta.HIRADINTERFACENAMESHPD
If I then run the following in a STRSQL session select nbjdta.HIRADINTERFACENAMESHPD, nbjdta.HIRADFILENAMESHPD, nbjdta.HIRADAS400FILENAMESHPD, nbjdta.HIRADBATCH#SHPD, nbjdta.HIRADSTATUSSHPD, nbjdta.HIRADBATCHTIMESTAMPSHPD, nbjdta.HIRADODITIMESTAMPSHPD from
SYSIBM.SYSDUMMY1
All the SQL variables come back null
Which verifies the error - but it NOT what I expected If anyone can point out what I am doing wrong - or explain why this wont work - I would be VERY grateful
Alan Shore
E-mail : ASHORE@xxxxxxxx<mailto:ASHORE@xxxxxxxx>
Phone [O] : (631) 200-5019
Phone [C] : (631) 880-8640
'If you're going through hell, keep going.'
Winston Churchill
--
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@xxxxxxxxxxxxxxxxxxxx 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@xxxxxxxxxxxxxxxxxxxx for any subscription related questions.
Help support midrange.com by shopping at amazon.com with our affiliate link:
https://amazon.midrange.com
midrange.com
