RE: Handling a null value in a subselect: open cursor returning "variable not found"

Check the library list being used to compile.

Where are your fields coming from?
This error has nothing to do with null values: "column or global variable gpatype not found"


Break apart your SQL statement to find out where your error is a.

Does this run?

SELECT T2.gpatype
FROM gpp001 T2
ORDER BY T2.gpaeff DESC
FETCH FIRST ROW ONLY
;


Having the COALESCE outside the (select...) is what you want.
Qualify your fields so you know where they are coming from.

select gpbrt, gpbrteff,
COALESCE((SELECT T2.gpatype
FROM gpp001 T2
WHERE T1.gpbrt = T2.gpatar
ORDER BY T2.gpaeff DESC
FETCH FIRST ROW ONLY), 'xx')
FROM gpp010 T1 WHERE T1.key = :mykey


--
--
--
Chris Hiebert
Senior Programmer/Analyst
Disclaimer: Any views or opinions presented are solely those of the author and do not necessarily represent those of the company.

From: MIDRANGE-L <midrange-l-bounces@xxxxxxxxxxxxxxxxxx> On Behalf Of Reeve
Sent: Monday, April 17, 2023 5:38 PM
To: midrange-l@xxxxxxxxxxxxxxxxxx
Subject: Handling a null value in a subselect: open cursor returning "variable not found"



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