case expression
when value1 then result1
when value2 then result2
"searched case form"
case
when booleanexpression1 then result1
when booleanexpression2 then result2
first, get the quotedetailid data fields
if productdescription is NULL then prodpartno = productidname
else prodpartno = NULL
SELECT quotedetailid,
CASE
WHEN productdescription IS NULL THEN productidname
ELSE NULL
END AS prodpartno,
CASE
WHEN productdescription IS NULL
THEN
(SELECT FilteredProduct.new_productdescription
FROM FilteredProduct
WHERE FilteredQuoteDetail.productid = FilteredProduct.productid)
ELSE productdescription
END AS proddesc, quantity, priceperunit, baseamount, manualdiscountamount, extendedamount
FROM FilteredQuoteDetail
WHERE (quoteid = @QuoteId)
###########################################################
PERHAPS CLEANER / BETTER as this is just a simple IF statement!
CASE
WHEN productdescription IS NULL
THEN productidname
WHEN productdescription IS NOT NULL
THEN productdescription
ELSE NULL
END AS prodpartno,
(because productdescription is inaccessible for an Existing Product, so when productdescription contains data it must be the Write In Product name and appear in the produtpartno field area)