RE: SQL for distinction between not valid and not there

Chuck,
Thanks for the example BUT I cannot get this statement below to
run. As is and replacing the "?" parameters with values I get the
following error,
SQL State: 42601
Vendor Code: -199
Message: [SQL0199] Keyword SELECT not expected. Valid tokens: )

I have attempted to "alter" the statement but I have not had success. I
used the reference manual for V5r4 (page 419-422) for a guide of what is
going on.

In the original statement the CTE section looks like it missing two
closing parentheses. But when I add them I get more errors.

Error after adding the sixth parenthesis,
-With findstore as
-(select * From TABLE(values(Cast(11 as Decimal(10,0))
- ,Cast( 1 as Decimal( 3,0))))
SQL State: 42601
Vendor Code: -104
Message: [SQL0104] Token IFNULL was not valid. Valid tokens: ) FETCH
ORDER UNION EXCEPT.

Then when I add the seventh parenthesis,
-With findstore as
-(select * From TABLE(values(Cast(11 as Decimal(10,0))
- ,Cast( 1 as Decimal( 3,0)))))
SQL State: 42601
Vendor Code: -104
Message: [SQL0104] Token ) was not valid. Valid tokens: AS IN OUT
<IDENTIFIER>.

Then if I add the "as xxx" (correlation clause) between
parenthesis 6 and 7 I get (which I did just for kicks),
-With findstore as
-(select * From TABLE(values(Cast(11 as Decimal(10,0))
- ,Cast( 1 as Decimal( 3,0))))As xxx)

SQL State: 42704
Vendor Code: -204
Message: [SQL0204] VALUES in *LIBL type *N not found.


However, replacing your CTE with,
(Select Dec(11,10,0), Dec( 1,3,0) From sysibm/sysdummy1) produces the
results I think you meant for me to see. Unfortunately, it looks like I
will have to go another route. When I run the latest statement I get
NULL results for either situation again and I cannot tell the difference
between there being no product records (meaning available for all
stores) and no product record for that store (meaning not available for
that store).

Thanks for all the help, Matt

-----Original Message-----
Does the following work for both 11, 1 and 1, 1 as replacements for
the question marks? Create a table with the single row to replace the
TABLE() function if necessary. ifnull can also be eliminated, so that
the representation of each text string is the NULL value.

with
findstore (p, s) as
(select * from TABLE(values(cast(? as decimal(10, 0))
,cast(? as decimal( 3, 0)) )
select ifnull(T. STOF#, 'Product does not exist')
, ifnull(T.STSTORE, 'Store does not receive')
from findstore F
left outer join DTKSTRPF T
on F.P = T.STOF# and F.S = T.STSTORE

Regards, Chuck