Použití CTE v SQL označit DUPLIKÁTY a odkaz v sub-dotaz

Question 1

Takže mám následující 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
)

Existují duplicity ve výše uvedené tabulce ([ln106].[dbo].tcisli305100) , proto CTE, aby si jednotlivé hodnoty. Chci formátovat právě tyto hodnoty v níže dotaz (s předponou s ---)


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'

Dotazu mě nemá rád odkazů v hlavním dotazu. Může mi někdo pomoci, nebo navrhnout lepší nápad?

Question 2

Váš poslední dotaz by měl vypadat nějak takto:

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'

David Maddox · Answer 1 · 2021-11-15T15:53:23

Váš poslední dotaz by měl vypadat nějak takto:

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'

Použití CTE v SQL označit DUPLIKÁTY a odkaz v sub-dotaz

Otázka

Nejlepší odpověď

V jiných jazycích

Tato stránka je v jiných jazycích

Populární v této kategorii

Oblíbené položky v této kategorii