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

asked on

decoade in oracle

SELECT customer_id,  
       NAME,  
         account1,
         account2,
         CASE,
       SUM(cnt) sum_cnt,
         sum(amt2) amt3
 FROM( SELECT c.customer_id, c.NAME,  c.ar_account account1, ih.ar_account account2,
  CASE            WHEN vp.inv_product_type = 'CRYO' THEN 'CRYO'
                          WHEN vp.inv_product_type ='POCRY' THEN 'POOLED CRYO'
                          WHEN vp.inv_product_type ='FRBC'THEN 'FRBC'
                          WHEN vp.inv_product_type ='LRBC' THEN 'LRBC'
                          WHEN vp.inv_product_type ='RBC' THEN 'RBC'
                            WHEN vp.inv_product_type ='WB'THEN 'WB'
                  WHEN vp.inv_product_type ='WRBC'THEN 'WRBC'
                            WHEN vp.inv_product_type ='FFP'THEN 'FFP'
                          WHEN vp.inv_product_type ='FP24'THEN 'FFP'
                            WHEN vp.inv_product_type ='PLSM'THEN 'FP24'
                          WHEN vp.inv_product_type ='PHER'THEN 'PHER'
                             WHEN vp.inv_product_type ='LPHER'THEN 'LPHER'
                          WHEN vp.inv_product_type ='PFFP' THEN 'PFFP'
                          WHEN vp.inv_product_type ='PLT'THEN 'PLT'      
                          WHEN vp.inv_product_type ='RPFF'THEN 'RPFF'      
                          WHEN vp.inv_product_type ='VCAPH'THEN 'VCAPH'
                          WHEN vp.inv_product_type ='VPHER'THEN 'VPHER'                   
                    ELSE 'OTHERS'
                    END CASE,
                      sum(ID.order_qty) cnt,
            sum(id.amount) amt,
            sum((ID.order_qty)*(id.amount)) amt2                          
FROM INVOICE_DETAIL ID,
     INVOICE_HEADER ih,
       customers@plab.world c,
       valid_products@plab.world vp,
     hospital_system_cust hsc
WHERE ih.invoice_date BETWEEN :start_date and :end_date
AND ih.invoice_number = ID.invoice_number
AND ID.item_id = vp.product_code
AND ih.ar_account = c.ar_account
AND c.customer_type = 'RG'
and c.term_date is null
and ih.ar_account not in ('0070', '0353', '0352')
and hsc.customer_id = c.customer_id
and hsc.hospital_system_code = :p_hospital_system_code
and c.customer_id = :p_customer_id
     GROUP BY c.customer_id, c.NAME, c.ar_account, ih.ar_account, vp.inv_product_type)
GROUP BY customer_id, NAME,  account1, account2, CASE
ORDER BY 3,4

The lov for customer has ALL' I want to write a decode
if c,customer_id = 'ALL' then run for all customers else run for p_customer_id
same for p_hospital_system_code
Avatar of David VanZandt
David VanZandt
Flag of United States of America image

Which version of the database please?  It may affect the syntax.
Avatar of anumoses

ASKER

Oracle9i Enterprise Edition Release 9.2.0.8.0 - 64bit Production
ASKER CERTIFIED SOLUTION
Avatar of slightwv (䄆 Netminder)
slightwv (䄆 Netminder)

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
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
Instead of ALL is there a way to say exclude p_customer_id.
Like p_hospital_system_code = 'ALL'
and if this is ALL then p_customer_id is null
you can use this instead in place of your last two conditions

and (hsc.hospital_system_code = :p_hospital_system_code or :p_hospital_system_code='ALL')
and (c.customer_id = :p_customer_id or :p_customer_id='ALL')
thanks