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.

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

This thread ...


Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2020 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].