1.  Home
  2. MIDRANGE-L
  3. October 2017

RE: We have a mystery

UTPIMPF3S is the from file?

this the V7.3 knowledge center

SQL0012
Message Text: Correlation without qualification occurred for column &1 to table &2.
Cause Text: Column &1 which occurs in a subselect, is not explicitly qualified, and occurs in table &2 in library &3 specified in the FROM clause of an outer subselect or as the target of an update or delete operation. Consequently, the reference to the column in the subselect is an outer reference, and correlation will occur.
Recovery Text: Ensure you intended to use the correlation. If you did not intend to use the correlation, the column does not exist in any of the tables or views identified in the FROM clause of the same level of the subselect that column &1 was referenced. Since it is a good practice to explicitly qualify any intended correlated references, it is recommended that the statement be changed so that the column &1 is qualified with a table designator.
SQLCODE or SQLCODEs: +012
SQLSTATE or SQLSTATEs: 01545


What release are you at, and can you show the full query?
Jim Franz

-----Original Message-----
From: MIDRANGE-L [mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Mike Cunningham
Sent: Thursday, October 12, 2017 12:46 PM
To: Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxx>
Subject: RE: We have a mystery

We solved our mystery. Or at least figured out how to deal with it This is a short section of the source code for the SQL SELECT in this program

If its coded like this it will fail as part of the larger CL process but runs when run by itself in a submitted job

0285.34 SELECT
0285.35 coalesce(trim(Data01),'0') as SltsRefId
0285.36 ,CROSSREF.SLIDPCTID
0285.37 ,coalesce(trim(Data04),'') as SltSchGUId
0285.38 ,coalesce(trim(Data09),'') as SltSchType
0285.39 ,coalesce(trim(Data05),'') as SltSchCode
0285.40 ,coalesce(trim(Data03),'') as SltFullNam
0285.41 ,coalesce(trim(Data41),'') as SltScrtDtx
0285.42 ,coalesce(trim(Data42),'') as SltSUpdDtx
0285.43 ,coalesce(trim(Data06),'') as SltSchName
0285.44 ,coalesce(trim(Data07),'') as SltOrgCat
0285.45 ,coalesce(trim(Data08),'') as SltOrgType
0285.46 ,coalesce(trim(Data11),'0') as SltScFromx
0285.47 ,coalesce(trim(Data12),'0') as SltSchTox

When like this it runs fine in both processes

0285.34 SELECT
0285.35 coalesce(trim(temp.Data01),'0') as SltsRefId
0285.36 ,CROSSREF.SLIDPCTID
0285.37 ,coalesce(trim(temp.Data04),'') as SltSchGUId
0285.38 ,coalesce(trim(temp.Data09),'') as SltSchType
0285.39 ,coalesce(trim(temp.Data05),'') as SltSchCode
0285.40 ,coalesce(trim(temp.Data03),'') as SltFullNam
0285.41 ,coalesce(trim(temp.Data41),'') as SltScrtDtx
0285.42 ,coalesce(trim(temp.Data42),'') as SltSUpdDtx
0285.43 ,coalesce(trim(temp.Data06),'') as SltSchName
0285.44 ,coalesce(trim(temp.Data07),'') as SltOrgCat
0285.45 ,coalesce(trim(temp.Data08),'') as SltOrgType
0285.46 ,coalesce(trim(temp.Data11),'0') as SltScFromx
0285.47 ,coalesce(trim(temp.Data12),'0') as SltSchTox


Without the temp. it would issue this error.,

Sqlstate = 01545 An unqualified column name has been interpreted as a correlated reference.
Error message = DATA01 UTPIMPF3S SLATE
Current FUNC =
PCTID = 0
SCHOOL NAME =
SCHOOL ADDRS1 =

And since the read loop was coded like this it did no fetch

dow (SqlStt = '00000' or SqlStt = '01004');

exec sql fetch next from SchData into
:SltsRefId
, :SltsPCTId


Still don’t know why it only issues that 01545 state when run in the larger CL.


-----Original Message-----
From: MIDRANGE-L [mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Darren Strong
Sent: Saturday, October 07, 2017 7:25 AM
To: Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxx>
Subject: RE: We have a mystery

We have an issue with a vendor system writing data to a file using traditional RPG, and then opening a cursor against that file pretty quickly afterwords. Intermitently, the cursor finds no data, but if I have it retry it sometimes works. This began after we applied some PTF's in March 2017 to our i7.3 system. Neither IBM nor the vendor have been able to reproduce the issue, so it continues. Through the vendor, we found out another user with a similar system to our is also having this issue. I'm not saying its the cause of yours, but I'd be really interested if you found a solution and to see if it applies to us.

___________________________________
Darren Strong
Dekko





From: Mike Cunningham <mike.cunningham@xxxxxxx>
To: Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxx>
Date: 10/05/2017 02:33 PM
Subject: RE: We have a mystery
Sent by: "MIDRANGE-L" <midrange-l-bounces@xxxxxxxxxxxx>



Not just before in the same application but they are downloaded using some middleware (Go Anywhere) and converted to a Physical File. A dump (CPYF
*PRINT) of that file just before the RPG call shows data in the file.

-----Original Message-----
From: MIDRANGE-L [mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Darren Strong
Sent: Thursday, October 05, 2017 2:13 PM
To: Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxx>
Subject: Re: We have a mystery

Are these records perhaps written just before they're read? If so, what method is being used to write the records?

___________________________________
Darren Strong
Dekko





From: Mike Cunningham <mike.cunningham@xxxxxxx>
To: Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxx>
Date: 10/05/2017 12:45 PM
Subject: We have a mystery
Sent by: "MIDRANGE-L" <midrange-l-bounces@xxxxxxxxxxxx>



We have an RPG with embedded SQL program running in a CL program that has calls to multiple RPG programs. This CL is run from the job scheduler late at night. We were having issues with this night job that looked like it was not running the one RPG app but when run during the day by itself it would work perfect. So we added a trace to the program to see what it was doing at night. It showed that no data was being read from the SQL fetch command. When it was run by itself during the day it showed it read thousands of records. The next debug step we did was to have the main CL that runs at night CALL the RPG app and then also do a SBMJOB to call the same RPG app. When run from the main CL with a CALL it read nothing. 2 minutes later when it was submitted it ran fine and read thousands of records.

We are stumped. Has anyone ever seen this happen before? There are no
overrides or explicit sharing of open data paths.

Mike Cunningham

________________________________
This email may contain confidential information about a Pennsylvania College of Technology student. It is intended solely for the use of the recipient. This email may contain information that is considered an "educational record" subject to the protections of the Family Educational Rights and Privacy Act Regulations. The regulations may be found at 34 C.F.R. Part 99 for your reference. The recipient may only use or disclose the information in accordance with the requirements of the Federal Educational Rights and Privacy Act Regulations. If you have received this transmission in error, please notify the sender immediately and permanently delete the email.
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list To post a message email: MIDRANGE-L@xxxxxxxxxxxx To subscribe, unsubscribe, or change list options,
visit: https://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx 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: http://amzn.to/2dEadiD


--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list To post a message email: MIDRANGE-L@xxxxxxxxxxxx To subscribe, unsubscribe, or change list options,
visit: https://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx 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: http://amzn.to/2dEadiD

________________________________
This email may contain confidential information about a Pennsylvania College of Technology student. It is intended solely for the use of the recipient. This email may contain information that is considered an “educational record” subject to the protections of the Family Educational Rights and Privacy Act Regulations. The regulations may be found at 34 C.F.R. Part 99 for your reference. The recipient may only use or disclose the information in accordance with the requirements of the Federal Educational Rights and Privacy Act Regulations. If you have received this transmission in error, please notify the sender immediately and permanently delete the email.
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list To post a message email: MIDRANGE-L@xxxxxxxxxxxx To subscribe, unsubscribe, or change list options,
visit: https://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx 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: http://amzn.to/2dEadiD



--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list To post a message email: MIDRANGE-L@xxxxxxxxxxxx To subscribe, unsubscribe, or change list options,
visit: https://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx 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: http://amzn.to/2dEadiD

________________________________
This email may contain confidential information about a Pennsylvania College of Technology student. It is intended solely for the use of the recipient. This email may contain information that is considered an “educational record” subject to the protections of the Family Educational Rights and Privacy Act Regulations. The regulations may be found at 34 C.F.R. Part 99 for your reference. The recipient may only use or disclose the information in accordance with the requirements of the Federal Educational Rights and Privacy Act Regulations. If you have received this transmission in error, please notify the sender immediately and permanently delete the email.
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list To post a message email: MIDRANGE-L@xxxxxxxxxxxx To subscribe, unsubscribe, or change list options,
visit: https://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx 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: http://amzn.to/2dEadiD


As an Amazon Associate we earn from qualifying purchases.

This thread ...
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.