Use CTE in SQL to flag DUPLICATES and reference in sub-query-CodePudding

So I have the following CTE:

with dupeinv AS (
select * from (
select
tci.t_idoc,
tci.t_idat,
ROW_NUMBER() OVER (partition by tci.t_idoc ORDER BY tci.t_idoc, tci.t_idat DESC) as rn
from [ln106].[dbo].tcisli305100 tci
) as t
where t.rn = 1
)

There are duplicates in the above table ([ln106].[dbo].tcisli305100) , hence the CTE to get single values. I want to format just these values in the below query (prefixed with ---)


select 'JCI' as BU,
    RTRIM(LTRIM(cl.t_orno)) AS SALES_ORDER_NUMBER
    , cl.t_pono AS SALES_ORDER_LINE_NUMBER
    , CONCAT(cl.t_shpm, cl.t_pono, cl.t_idoc)  AS SHIPPING_RECORD_ID
    ,CASE WHEN cl.t_dqua = 0 or cl.t_dqua is null THEN cl.t_amti ELSE
     cl.t_amti / cl.t_dqua END AS AR_INVOICE_LINE_ITEM_PRICE_LOCAL
    , cl.t_line AS AR_INVOICE_LINE_NUMBER
    , cl.t_dqua AS AR_INVOICE_LINE_ITEM_QUANTITY
--- , concat(dupeinv.t_idoc,'|',format(dupeinv.t_idat,'MMddyyyy') ---
     ,ci.t_ccur AS AR_INVOICE_CURRENCY
    , ci.t_idat AS AR_INVOICE_DATE
    FROM [ln106].[dbo].tcisli310100 cl
    LEFT JOIN [ln106].[dbo].tcisli305100 ci ON cl.t_idoc = ci.t_idoc
    LEFT JOIN t di on cl.t_doc = di_t_doc
    LEFT JOIN (SELECT t_orno,t_pono FROM [ln106].[dbo].ttdsls401100 WHERE t_oltp <> 1 group by t_orno,t_pono) as l --Jed 10162020 Changed the join to prevent duplicate records
    ON l.t_orno=cl.t_orno COLLATE SQL_Latin1_General_CP1_CI_AS AND l.t_pono=cl.t_pono
    LEFT JOIN dupeinv tci on cl.r_idoc = ci.t_doc
    WHERE ci.t_idat > '2017'

Query doesn't like me referencing it in the main query. Can anyone help, or suggest a better idea?

CodePudding user response：

Your final query should look something like this:

WITH dupeinv AS
  (SELECT *
   FROM
     (SELECT tci.t_idoc,
             tci.t_idat,
             ROW_NUMBER() OVER (PARTITION BY tci.t_idoc
                                ORDER BY tci.t_idoc,
                                         tci.t_idat DESC) AS rn
      FROM [ln106].[dbo].tcisli305100 tci) AS t
   WHERE t.rn = 1 )
SELECT 'JCI' AS BU,
       RTRIM(LTRIM(cl.t_orno)) AS SALES_ORDER_NUMBER ,
       cl.t_pono AS SALES_ORDER_LINE_NUMBER ,
       CONCAT(cl.t_shpm, cl.t_pono, cl.t_idoc) AS SHIPPING_RECORD_ID ,
       CASE
           WHEN cl.t_dqua = 0
                OR cl.t_dqua IS NULL THEN cl.t_amti
           ELSE cl.t_amti / cl.t_dqua
       END AS AR_INVOICE_LINE_ITEM_PRICE_LOCAL ,
       cl.t_line AS AR_INVOICE_LINE_NUMBER ,
       cl.t_dqua AS AR_INVOICE_LINE_ITEM_QUANTITY ,
       concat(dupeinv.t_idoc,
              '|',
              format(dupeinv.t_idat, 'MMddyyyy')) ,
       ci.t_ccur AS AR_INVOICE_CURRENCY ,
       ci.t_idat AS AR_INVOICE_DATE
FROM [ln106].[dbo].tcisli310100 cl
LEFT JOIN [ln106].[dbo].tcisli305100 ci ON cl.t_idoc = ci.t_idoc
LEFT JOIN t di ON cl.t_doc = di_t_doc
LEFT JOIN
  (SELECT t_orno,
          t_pono
   FROM [ln106].[dbo].ttdsls401100
   WHERE t_oltp <> 1
   GROUP BY t_orno,
            t_pono) AS l --Jed 10162020 Changed the join to prevent duplicate records
 ON l.t_orno=cl.t_orno COLLATE SQL_Latin1_General_CP1_CI_AS
AND l.t_pono=cl.t_pono
LEFT JOIN dupeinv tci ON cl.r_idoc = ci.t_doc
WHERE ci.t_idat > '2017'