CODEWITHSUNDEEP
sql-servert-sqloracle-sqldevelopercommon-table-expression
Prince Kumar
Prince Kumar

Reputation: 13

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

The Oracle query is 

SELECT A.BUSINESS_UNIT 
     , A.EXT_ORG_ID 
     , A.INVOICE_ID 
     , A.CONTACT_NAME 
     , SUM(A.LINE_AMT) AS INVOICE_AMT 
     , SUM(CASE WHEN A.APPLIED_AMT - Nvl(( SELECT SUM(LINE_AMT) FROM PS_ITEM_LINE_SF WHERE BUSINESS_UNIT = A.BUSINESS_UNIT AND COMMON_ID = A.EXT_ORG_ID AND ITEM_NBR = A.ITEM_NBR AND ITEM_TYPE_CD = A.ITEM_TYPE_CD 
                                           AND LINE_AMT*100000+LINE_SEQ_NBR>A.LINE_AMT*100000+A.LINE_SEQ_NBR),0) < 0 THEN A.LINE_AMT
                WHEN A.APPLIED_AMT - Nvl(( SELECT SUM(LINE_AMT) FROM PS_ITEM_LINE_SF WHERE BUSINESS_UNIT = A.BUSINESS_UNIT AND COMMON_ID = A.EXT_ORG_ID AND ITEM_NBR = A.ITEM_NBR AND ITEM_TYPE_CD = A.ITEM_TYPE_CD 
                                           AND LINE_AMT*100000+LINE_SEQ_NBR>A.LINE_AMT*100000+A.LINE_SEQ_NBR),0) >= A.LINE_AMT THEN 0 
                ELSE A.LINE_AMT - A.APPLIED_AMT+Nvl(( SELECT SUM(LINE_AMT) FROM PS_ITEM_LINE_SF WHERE BUSINESS_UNIT = A.BUSINESS_UNIT AND COMMON_ID = A.EXT_ORG_ID AND ITEM_NBR = A.ITEM_NBR AND ITEM_TYPE_CD =
                                                      A.ITEM_TYPE_CD AND LINE_AMT*100000+LINE_SEQ_NBR>A.LINE_AMT*100000+A.LINE_SEQ_NBR),0) END) AS INVOICE_BALANCE 
      , 0.0 FROM PS_SIS_SF_EO_INV_V A 
      GROUP BY A.BUSINESS_UNIT, A.EXT_ORG_ID, A.INVOICE_ID, A.CONTACT_NAME

Converting the NVL to ISNULL gave the following error. "Cannot perform an aggregate function on an expression containing an aggregate or a subquery." So I did some research on internet and found that CTE has to be applied.

I converted the query as below, but still the outcome of query is not same as Oracle query. Please help.

WITH CTELINE AS (SELECT SUM(B.LINE_AMT) AS LINE_AMT, A.BUSINESS_UNIT FROM PS_ITEM_LINE_SF B, PS_SIS_SF_EO_INV_V A
                 WHERE B.BUSINESS_UNIT = A.BUSINESS_UNIT    AND B.COMMON_ID = A.EXT_ORG_ID AND B.ITEM_NBR = A.ITEM_NBR    AND B.ITEM_TYPE_CD = A.ITEM_TYPE_CD    
                 AND B.LINE_AMT*100000 + B.LINE_SEQ_NBR > A.LINE_AMT * 100000 + A.LINE_SEQ_NBR GROUP BY A.BUSINESS_UNIT
                 ),
     CTECASE AS (SELECT CASE WHEN C.APPLIED_AMT - ISNULL((SELECT LINE_AMT FROM CTELINE  ),0) < 0   THEN C.LINE_AMT  
                             WHEN C.APPLIED_AMT - ISNULL(( SELECT LINE_AMT FROM CTELINE ),0) > = C.LINE_AMT THEN 0
                             ELSE C.LINE_AMT - C.APPLIED_AMT+ISNULL((  SELECT LINE_AMT FROM CTELINE ),0)      END AS CTE_INVOICE_BALANCE
                       ,C.BUSINESS_UNIT,C.EXT_ORG_ID, C.INVOICE_ID FROM PS_SIS_SF_EO_INV_V C, CTELINE CTEL WHERE  CTEL.BUSINESS_UNIT = C.BUSINESS_UNIT 
                        GROUP BY 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) AS INVOICE_AMT 
, SUM(CTE_INVOICE_BALANCE) AS INVOICE_BALANCE
, 0.0 
  FROM PS_SIS_SF_EO_INV_V A, CTECASE CTEC WHERE  CTEC.BUSINESS_UNIT = A.BUSINESS_UNIT AND A.EXT_ORG_ID = CTEC.EXT_ORG_ID AND 
   A.INVOICE_ID = CTEC.INVOICE_ID 
  GROUP BY A.BUSINESS_UNIT, A.EXT_ORG_ID, A.INVOICE_ID, A.CONTACT_NAME

Upvotes: 1

Views: 87

Answers (1)

Thorsten Kettner
Thorsten Kettner

Reputation: 94969

You were right to re-write the orinial query. It was written way too complicated containing three identical subqueries. Here is my take on the query:

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

I kept the original table alias A, although I don't like it for not being mnemonic. But I don't know which part(s) of the name ps_sis_sf_eo_inv_v should be taken for the alias name, of course.

If I have made no mistakes, then this query should do exactly the same as the original and be easy to convert.

Upvotes: 0

Related Questions