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

Thanks for your replies...but this is not a problem with the field's
definition or library list. The field is properly defined globally (this
code is in a service program) and I tried defining the field locally, to no
avail. My library list doesn't change.

The code (including a reference to GPATYPE) works *as expected* when I
don't encounter a null value in the subselect; this behavior suggests
GPATYPE is properly defined.

I have new test cases causing the subselect to return a null value and I'm
trying to address that situation. Merely adding COALESCE (in the two
examples noted) causes the code to break when the cursor is opened. Adding
an indicator variable to the field on the FETCH statement causes the
program to fail when the cursor is opened as well. The first code snippet
is footnoted as working properly and it has GPATYPE. The next two
examples, with changes on how GPATYPE is handled in a null situation, fail.

*The subselect return value is used in ordering the result set and its use
in ORDER BY may be the problem.* My solution: a function returning
GPATYPE, adding it to the ORDER BY clause, and removing references to
GPATYPE in the SELECT and FETCH statements. The program is now working as
expected. Fortunately, I'm working with a small set of records and
performance isn't an issue.

One additional thought: this might be the best, the only, or the worst
execution-time error message; there's no guarantee it's the right error
message! I go back to Model 10 CCP and have learned from experience.

--rf

On Tue, Apr 18, 2023 at 7:20 AM Hiebert, Chris <chris.hiebert@xxxxxxxxxxxxxx>
wrote:

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




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