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


ReplyForward