Link to home
Start Free TrialLog in
Avatar of anumoses
anumosesFlag for United States of America

asked on

Oracle query - Case statement

Select 
           name,	
			NVL(SUM(CASE WHEN vp.inv_product_type = 'RBC' THEN id.order_qty ELSE 0 END ), 0),-- 'RBC',	
			NVL(SUM(CASE WHEN vp.inv_product_type = 'LRBC' THEN id.order_qty ELSE 0 END), 0),-- 'LRBC',		
			NVL(SUM(CASE WHEN vp.inv_product_type in ('PHER','VPHER', 'LPHER') THEN id.order_qty ELSE 0 END), 0),-- 'PHERESIS',		
			NVL(SUM(CASE WHEN vp.inv_product_type in ('FP24','FFP') THEN id.order_qty ELSE 0 END), 0),-- 'FFPUTIL',	
			NVL(SUM(CASE WHEN vp.inv_product_type = 'CPPLS' THEN id.order_qty ELSE 0 END), 0),-- 'CPPUTIL',	
			NVL(SUM(CASE WHEN vp.inv_product_type = 'CRYP' THEN id.order_qty ELSE 0 END), 0),-- 'CRYO',	
			NVL(SUM(CASE WHEN vp.inv_product_type = 'POCRY' THEN id.order_qty ELSE 0 END), 0)-- 'POCRY'      
from    invoice_header ih,
        invoice_detail id,
		item_profile ip,
		customers@plab.world c,
		valid_products@plab.world vp
where id.invoice_number = ih.invoice_number
and   id.item_id = ip.item_id
and   ip.item_type in ('P')
and   ih.customer_id = c.customer_id
and   trunc(ih.invoice_date) between  '01-jun-2015' and '30-jun-2015'
and id.item_id = vp.product_code
and ih.customer_id = 'WAD-RG0005'
group by name,vp.inv_product_type

Open in new window


Required Format

Name                                                                               RBC   LRBC     PHERESIS     FFPUTIL    CPPUTIL    CRYO   POCRY    

ST. ANTHONY-CROWN POINT FRANCISCAN HEALTH    138       0             26                29              0             0              1

My case statement gives an error when I say RBC after END. I have commented it. The above query also gives 5 rows of data instead of one line data. Help appreciated
ASKER CERTIFIED SOLUTION
Avatar of johnsone
johnsone
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of anumoses

ASKER

Now column headers are fixed. Get 5 lines instead of one.User generated image
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Thanks
Why the B grade?  What information was lacking in the answers?

See grading guidelines -> http://support.experts-exchange.com/customer/portal/articles/481419-what-grade-should-i-award-
Sorry that was by mistake. Not intentional. If the moderator opens I can grade as A.
Thanks
Thanks.