Help with query

[Twillz]

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

 

[skuhlman]

Try this out:

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 isnull(itemalias_descrip1,'')='' THEN
    item_descrip1 
 ELSE
     itemalias_descrip1 
 END as itemdescrip,
    formatPrice(coitem_price) AS unitprice,
       formatExtPrice(coitem_price * cobill_qty) AS extprice
  FROM cobmisc
    LEFT JOIN cobbill ON cobill_cobmisc_id=cobmisc_id
    LEFT JOIN cobitem ON cobill_coitem_id=coitem_id
    LEFT JOIN itemsite ON coitem_itemsite_id=itemsite_id
    LEFT JOIN item ON itemsite_item_id=item_id
    LEFT JOIN itemalias itemalias_item_id=item_id
  WHERE (cobmisc_id=<? value("cobmisc_id") ?>))
ORDER BY coitem_linenumber

 

[Twillz]

I tried your code and now nothing is come up on the report.


Thank
Twillz

 

[Twillz]

I got the coe to work thanks for your help. Here is the working code.

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 NOT NULL THEN itemalias_descrip1 ELSE
item_descrip1 END AS descrip1,
formatPrice(coitem_price) AS unitprice,
formatExtPrice(coitem_price * cobill_qty) AS extprice
FROM cobmisc, cobill, coitem, itemsite, item LEFT OUTER JOIN itemalias
ON (itemalias_item_id=item_id)
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 (cobmisc_id=<? value("cobmisc_id") ?>))
ORDER BY coitem_linenumber;