tsql - Need to convert the Oracle SQL using aggregate function in subquery to SQL Server (T-SQL) query -

the oracle query

select a.business_unit       , a.ext_org_id       , a.invoice_id       , a.contact_name       , sum(a.line_amt) invoice_amt       , sum(case when a.applied_amt - nvl(( select sum(line_amt) ps_item_line_sf business_unit = a.business_unit , common_id = a.ext_org_id , item_nbr = a.item_nbr , item_type_cd = a.item_type_cd                                             , line_amt*100000+line_seq_nbr>a.line_amt*100000+a.line_seq_nbr),0) < 0 a.line_amt                 when a.applied_amt - nvl(( select sum(line_amt) ps_item_line_sf business_unit = a.business_unit , common_id = a.ext_org_id , item_nbr = a.item_nbr , item_type_cd = a.item_type_cd                                             , line_amt*100000+line_seq_nbr>a.line_amt*100000+a.line_seq_nbr),0) >= a.line_amt 0                  else a.line_amt - a.applied_amt+nvl(( select sum(line_amt) ps_item_line_sf business_unit = a.business_unit , common_id = a.ext_org_id , item_nbr = a.item_nbr , item_type_cd =                                                       a.item_type_cd , line_amt*100000+line_seq_nbr>a.line_amt*100000+a.line_seq_nbr),0) end) invoice_balance        , 0.0 ps_sis_sf_eo_inv_v        group a.business_unit, a.ext_org_id, a.invoice_id, a.contact_name 

converting nvl isnull gave following error. "cannot perform aggregate function on expression containing aggregate or subquery." did research on internet , found cte has applied.

i converted query below, still outcome of query not same oracle query. please help.

with cteline (select sum(b.line_amt) line_amt, a.business_unit ps_item_line_sf b, ps_sis_sf_eo_inv_v                  b.business_unit = a.business_unit    , b.common_id = a.ext_org_id , b.item_nbr = a.item_nbr    , b.item_type_cd = a.item_type_cd                      , b.line_amt*100000 + b.line_seq_nbr > a.line_amt * 100000 + a.line_seq_nbr group a.business_unit                  ),      ctecase (select case when c.applied_amt - isnull((select line_amt cteline  ),0) < 0   c.line_amt                                when c.applied_amt - isnull(( select line_amt cteline ),0) > = c.line_amt 0                              else c.line_amt - c.applied_amt+isnull((  select line_amt cteline ),0)      end cte_invoice_balance                        ,c.business_unit,c.ext_org_id, c.invoice_id ps_sis_sf_eo_inv_v c, cteline ctel  ctel.business_unit = c.business_unit                          group c.business_unit, c.ext_org_id, c.invoice_id, c.contact_name,c.applied_amt,c.line_amt                   ) select a.business_unit   , a.ext_org_id   , a.invoice_id   , a.contact_name   , sum(a.line_amt) invoice_amt  , sum(cte_invoice_balance) invoice_balance , 0.0    ps_sis_sf_eo_inv_v a, ctecase ctec  ctec.business_unit = a.business_unit , a.ext_org_id = ctec.ext_org_id ,     a.invoice_id = ctec.invoice_id    group a.business_unit, a.ext_org_id, a.invoice_id, a.contact_name 

you right re-write orinial query. written way complicated containing 3 identical subqueries. here take on query:

select   business_unit,   ext_org_id,   invoice_id,   contact_name,   sum(line_amt) invoice_amt,   sum   (     case       when applied_amt < sum_line_amt line_amt       when applied_amt >= sum_line_amt + line_amt 0       else line_amt - applied_amt + sum_line_amt     end   ) invoice_balance,   0.0 (   select      a.*,     coalesce     (       (          select sum(il.line_amt)          ps_item_line_sf il         il.business_unit = a.business_unit          , il.common_id = a.ext_org_id          , il.item_nbr = a.item_nbr          , il.item_type_cd = a.item_type_cd          , il.line_amt * 100000 + il.line_seq_nbr > a.line_amt * 100000 + a.line_seq_nbr       ), 0     ) sum_line_amt   ps_sis_sf_eo_inv_v ) mydata group business_unit, ext_org_id, invoice_id, contact_name; 

i kept original table alias a, although don't not being mnemonic. don't know part(s) of name ps_sis_sf_eo_inv_v should taken alias name, of course.

if have made no mistakes, query should same original , easy convert.