Can someone please me help with this query? Here's what I'm trying to do. I have a report that shows: item number, item description, qty, etc. The problem I’m having is that I want to show an alias for the item if it has one, if not I want the item description to show instead, but only the alias will show up. Here is the code that I am using.
SELECT coitem_linenumber AS linenumber,
formatQty(coitem_qtyshipped) AS qtytobill,
item_invuom,
formatQty(coitem_qtyord) as qtyord,
formatQty(coitem_qtyord - coitem_qtyshipped) AS qtybackord,
item_number AS itemnumber,
CASE WHEN itemalias_descrip1 is null THEN
item_descrip1
ELSE
itemalias_descrip1
END as itemdescrip,
formatPrice(coitem_price) AS unitprice,
formatExtPrice(coitem_price * cobill_qty) AS extprice
FROM cobmisc, cobill, coitem, itemsite, item, itemalias
WHERE ((cobill_cobmisc_id=cobmisc_id)
AND (cobill_coitem_id=coitem_id)
AND (coitem_itemsite_id=itemsite_id)
AND (itemsite_item_id=item_id)
AND (itemalias_item_id=item_id)
AND (cobmisc_id=<? value("cobmisc_id") ?>))
ORDER BY coitem_linenumber;
Thanks
SELECT coitem_linenumber AS linenumber,
formatQty(coitem_qtyshipped) AS qtytobill,
item_invuom,
formatQty(coitem_qtyord) as qtyord,
formatQty(coitem_qtyord - coitem_qtyshipped) AS qtybackord,
item_number AS itemnumber,
CASE WHEN itemalias_descrip1 is null THEN
item_descrip1
ELSE
itemalias_descrip1
END as itemdescrip,
formatPrice(coitem_price) AS unitprice,
formatExtPrice(coitem_price * cobill_qty) AS extprice
FROM cobmisc, cobill, coitem, itemsite, item, itemalias
WHERE ((cobill_cobmisc_id=cobmisc_id)
AND (cobill_coitem_id=coitem_id)
AND (coitem_itemsite_id=itemsite_id)
AND (itemsite_item_id=item_id)
AND (itemalias_item_id=item_id)
AND (cobmisc_id=<? value("cobmisc_id") ?>))
ORDER BY coitem_linenumber;
Thanks