Re: SQL for distinction between not valid and not there

Tyler, Matt wrote:

Coalesce will not work for me in that if I look for a row for product
#1 and store #1 it will not be found so a null row is returned (but
this is good because there are no rows for product #1) but if I look
for product #11 and store #1 I also get a null row returned (many rows for product #11). I just cannot get my mind around away to code this.

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