You have to qualify the field you're referring to within the subselect that is not part of the file you're subselecting. It should read as this if gpatar is the outside reference. Some SQL interfaces will let you get away with it, and some will give a warning, such as STRSQL. I'd guess that cleaning this up will either resolve your issue or highlight another issue that is hidden by this.
select gpbrt, gpbrteff,
COALESCE((SELECT gpatype
FROM gpp001
WHERE gpbrt = gpp001.gpatar
ORDER BY gpaeff DESC
FETCH FIRST ROW ONLY), 'xx')
FROM gpp010 WHERE key = :mykey
-----Original Message-----
From: MIDRANGE-L <midrange-l-bounces@xxxxxxxxxxxxxxxxxx> On Behalf Of Reeve
Sent: Monday, April 17, 2023 7:38 PM
To: midrange-l@xxxxxxxxxxxxxxxxxx
Subject: Handling a null value in a subselect: open cursor returning "variable not found"
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.
This seems as though it ought to be easy. Alas...
When I do this:
exec SQL declare x4 cursor for select gpbrt, gpbrteff,
(SELECT gpatype
FROM gpp001
WHERE gpbrt = gpatar
ORDER BY gpaeff DESC
FETCH FIRST ROW ONLY)
FROM gpp010 WHERE key = :mykey...
...everything works fine. But I can't figure how to handle a null value in the GPP001 subselect. I've tried these two approaches:
select gpbrt, gpbrteff,
(SELECT COALESCE(gpatype, 'xx')
FROM gpp001
WHERE gpbrt = gpatar
ORDER BY gpaeff DESC
FETCH FIRST ROW ONLY)
FROM gpp010 WHERE key = :mykey
...and...
select gpbrt, gpbrteff,
COALESCE((SELECT gpatype
FROM gpp001
WHERE gpbrt = gpatar
ORDER BY gpaeff DESC
FETCH FIRST ROW ONLY), 'xx')
FROM gpp010 WHERE key = :mykey
In both cases, I get an error when opening the cursor, calling out "column or global variable gpatype not found" even when the null condition isn't met in the test. However, the second example does work in Run SQL Scripts and STRSQL (I see "xx" and I don't get any errors)...but then I'm not declaring a cursor.
I have tried correlation once (both tables) and adding an indicator value on the FETCH another time but get the same results. Just adding the indicator variable (an INT(5)) causes known good tests to fail with the same error. Everything works (I've run well over 200,000 test cases) until I try to deal with that pesky null variable. I don't see any other unexpected messages in the job log. I'm on V7R5 (PTF status unknown) and have recompiled two levels back to test.
If I can't figure this out, I'll try using a function to fetch the variable and call the function on the SELECT.
Thank you for your suggestions (and hopefully a solution!).
--reeve
ReplyForward
--
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.
midrange.com
