SyBase SQL Anywhere v10
I have 4 tables. POSDetail, product, promo and refundreasons.
When POSdetail.prodtype = 0 or 1 I want to return product.descript where posdetail.prodnum = product.prodnum
When POSdetail.prodtype = 100 I want to return promo.descript where posdetail.prodnum = promo.promonum
When POSdetail.prodtype = 101 I want to return 2 columns
product.descript where posdetail.prodnum = product.prodnum AND refundreasons.descript where posdetail.howpaid = refundreaons.refnum
It's ok if the first two cases have a blank column next to them when there is no prodtype 101.
This is what I have so far.....
select t1.quan as Qty,
case when t1.prodtype in (0,1, 101) then t3.descript
when t1.prodtype = 100 then t2.descript
else 'no desc avail' end as Description,
t1.costeach as 'Cost Each',
(t1.quan * t1.costeach) as 'Extended Cost'
from dba.posdetail as t1 left join dba.promo as t2 on t1.prodnum = t2.promonum
left join dba.product as t3 on t1.prodnum = t3.prodnum
where t1.transact = 899330
Also, I cant figure our how to round Cost Each and Extended Cost to two decimal places :/
Thank you in advance!