case when的嵌套使用

脚本分析

case when可以嵌套使用，写出更复杂的逻辑实现

select                 trunc(a.out_date) out_date, 
                       nvl((select x.dept
                             from neubi_work.dim_dept x
                                where x.dept_code =a.DEPT_CODE),
                                 '其它') out_dept_name,
                       '' pact_area, 
                       b.pact_code,        
                       a.inpatient_no,
                       a.name,
                       '髋关节' item_type, 
                       sum(c.tot_cost) hc_cost, 
                       nvl((select x.dept
                             from neubi_work.dim_dept x
                               where x.dept_code =c.recipe_dpcd),
                                 '其它') recipe_dept_name,
                       b.pub_cost,
                       b.tot_cost, 
                       (case when (case when count( distinct case when c.qty=1
                                        then  c.tot_cost
                                        else null
                                         end)<>count(case when c.qty=1
                                                      then  c.tot_cost
                                                       else null
                                                       end) 
                                    then 2
                                      else 1
                                        end)=2
                             then    (select cost
                                from neubi_work.dim_ONEICD_HC e
                               where e.hc_name='2个人工髋关节') 
                            else (select cost
                                from neubi_work.dim_ONEICD_HC e
                               where e.hc_name='人工髋关节')  
                                     end)                     avg_pay,
                       b.pub_cost - sum(c.tot_cost)           recipe_cost,
                       b.pub_cost- sum(c.tot_cost)-(case when (case when count( distinct case when c.qty=1
                                                              then  c.tot_cost
                                                              else null
                                                               end)<>count(case when c.qty=1
                                                                            then  c.tot_cost
                                                                             else null
                                                                             end) 
                                                                        then 2
                                                                          else 1
                                                                            end)=2
                                                         then  (select cost  from neubi_work.dim_ONEICD_HC e
                                                           where e.hc_name='2个人工髋关节') 
                                                        else (select cost  from neubi_work.dim_ONEICD_HC e
                                                           where e.hc_name='人工髋关节') 
                                                             end)                  over_pay,
                       trunc(b.balance_date) balance_date
                  from inh_ipr_inmaininfo  a, 
                       inh_ipb_balancehead b,
                       inh_pay c
                 where a.inpatient_no = b.inpatient_no
                   and a.inpatient_no = c.inpatient_no
                   and b.waste_opercode is null
                   and c.DRUG_FLAG = 0
                   and b.trans_type = '1'
                   and b.waste_flag='1'
                   and b.pact_code = '2'
                   and c.fee_code in ('027', '030')
                   and c.item_code in
                       (select t.his_code
                          from neubi_work.dim_compare t
                         where t.pact_code in('2','99')
                           and t.center_name in
                               ('人工关节(髋关节)', '人工股骨头')
                               )
                  and a.inpatient_no='ZY020000635248'             
                ---and trunc(b.balance_date) >= to_Date($begin_time, 'YYYY-MM-DD HH24:MI:SS')
                ---AND trunc(b.balance_date) <= to_Date($end_time, 'YYYY-MM-DD HH24:MI:SS')
                 group by a.inpatient_no, 
                          c.recipe_dpcd,
                          trunc(a.out_date),
                          a.DEPT_CODE,
                          a.name,
                          b.pub_cost,
                          b.tot_cost,
                          trunc(b.balance_date),
                        --  c.qty,
                          b.pact_code