× The internal search function is temporarily non-functional. The current search engine is no longer viable and we are researching alternatives.
As a stop gap measure, we are using Google's custom search engine service.
If you know of an easy to use, open source, search engine ... please contact support@midrange.com.


  1.  Home
  2. MIDRANGE-L
  3. January 2021

RE: Problem with SQL variables

Okay
I ran my CLLE program in debug mode up to and including the RUNSQLSTM on line 95.00
I then ran the following in STRSQL
select
nbjdta.HIRADINTERFACENAMESHPD,
nbjdta.HIRADFILENAMESHPD,
nbjdta.HIRADAS400FILENAMESHPD,
nbjdta.HIRADBATCH#SHPD,
nbjdta.HIRADSTATUSSHPD,
nbjdta.HIRADBATCHTIMESTAMPSHPD,
nbjdta.HIRADODITIMESTAMPSHPD from
SYSIBM.SYSDUMMY1

But this is from the spool file created by running the RUNSQLSTM on line 95.00
Source member changed on 12/16/20 17:44:35
5770SS1 V7R3M0 160422 Run SQL Statements HRVARJA03A 01/07/21 15:19:25 PAGE
Record *...+... 1 ...+... 2 ...+... 3 ...+... 4 ...+... 5 ...+... 6 ...+... 7 ...+... 8 SEQNBR Last change
1 CREATE or REPLACE VARIABLE NBJDTA.HIRADAS400FILENAMESHPD CHARACTER (10);
2 SET NBJDTA.HIRADAS400FILENAMESHPD = 'HRSHIPPEDD';
3 CREATE or REPLACE VARIABLE NBJDTA.HIRADSTATUSSHPD CHARACTER (1);
4 SET NBJDTA.HIRADSTATUSSHPD = 'N';
* * * * * E N D O F S O U R C E * * * * *
5770SS1 V7R3M0 160422 Run SQL Statements HRVARJA03A 01/07/21 15:19:25 PAGE
Record *...+... 1 ...+... 2 ...+... 3 ...+... 4 ...+... 5 ...+... 6 ...+... 7 ...+... 8 SEQNBR Last change
MSG ID SEV RECORD TEXT
SQL79A0 0 1 Position 1 Variable HIRADAS400FILENAMESHPD was created
in NBJDTA.
SQL7948 0 2 Position 1 SET statement complete.
SQL79A0 0 3 Position 1 Variable HIRADSTATUSSHPD was created in
NBJDTA.
SQL7948 0 4 Position 1 SET statement complete.
Message Summary
Total Info Warning Error Severe Terminal
4 4 0 0 0 0
00 level severity errors found in source
* * * * * E N D O F L I S T I N G * * * * *

This is just NOT making any sense

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: Alan Shore
Sent: Thursday, January 7, 2021 3:04 PM
To: Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxxxxxxxx>
Subject: RE: Problem with SQL variables

Thanks for your reply Rob
Joblog icon in Run SQL scripts?
Can you expand on this?

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:59 PM
To: Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxxxxxxxx>
Subject: [EXTERNAL] RE: Problem with SQL variables

If it continues to fail then I would use the joblog icon in Run SQL scripts.
Perhaps you have a variable type mismatch or some such thing on the insert.

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 Rob Berendt
Sent: Thursday, January 7, 2021 2:55 PM
To: Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxxxxxxxx>
Subject: 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
--
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


As an Amazon Associate we earn from qualifying purchases.

This thread ...
Follow-Ups:
Replies:
Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2024 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].

Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.