production - query

DCastro

• February 14, 2025 16:21

Hello, I have a small doubt with this query in the position name line, it is duplicating the lines for the queries. I have tried several ways but I cannot solve this. This happens to me when an order has several suborders in the same order number.

SELECT 

    BEAS_FTHAUPT.AUFTRAG  AS "No Orden Interno",

    BEAS_FTHAUPT.BELNR_ID  AS "No de Orden",

    BEAS_FTHAUPT.Knd_id AS "Cod. de Cliente",

    BEAS_FTHAUPT.kndname AS "Nombre de Cliente",

    BEAS_FTPOS.ItemCode AS "Código de PT",

    BEAS_FTPOS.ItemName AS "Descripción de PT",

    MAX(OITM_MAIN.FrgnName) AS "TEST",

    MAX(OITM_MAIN.SLength1) AS "Largo",

    MAX(OITM_MAIN.SWidth1) AS "Ancho",

    MAX(OITM_MAIN.SHeight1) AS "Altura",

BEAS_FTPOS.menge_verbrauch as "Cantidad",

 

  MAX(BEAS_FTHAUPT.BELDAT) AS "Fecha de la Orden",

    MAX(OITM_PROCESS.BLength1) AS "Largo del Proceso A",

    MAX(OITM_PROCESS.BWidth1) AS "Ancho del Proceso A",

    MAX(T1.Price) AS "Precio",

    MAX(OITM_PROCESS.PurFactor2) AS "Score 1",

    MAX(OITM_PROCESS.PurFactor3) AS "Score 2",

    MAX(OITM_PROCESS.PurFactor4) AS "Score 3",

    MAX(OITM_PROCESS.PurFactor1) AS "No Cortes",

    BEAS_FTHAUPT.LFGDAT AS "Fecha de Entrega",

  BEAS_FTHAUPT.TYP AS "Tipo de orden",

  ORDR.NumAtCard AS "PO. Cliente",

 

   

 

    

    -- Columna 'Nombre Posicion' con los primeros 4 caracteres de cada valor de 'ART1_ID'

    STRING_AGG(LEFT(ISNULL(BEAS_FTSTL.ART1_ID, ''), 4), ', ') AS [Nombre Posicion],

    

   

    RIGHT(

        LEFT(

            STRING_AGG(ISNULL(BEAS_FTSTL.ART1_ID, ''), ', '),

            CASE 

                WHEN CHARINDEX(',', STRING_AGG(ISNULL(BEAS_FTSTL.ART1_ID, ''), ', ')) > 0 

                THEN CHARINDEX(',', STRING_AGG(ISNULL(BEAS_FTSTL.ART1_ID, ''), ', ')) - 1 

                ELSE LEN(STRING_AGG(ISNULL(BEAS_FTSTL.ART1_ID, ''), ', '))

            END

        ),

        4

    ) AS [Ancho],

 

(

    SELECT STRING_AGG(ISNULL(BEAS_STL.POS_TEXT, ''), ', ') 

           WITHIN GROUP (ORDER BY BEAS_STL.POS_TEXT ASC)

    FROM BEAS_STL

    WHERE BEAS_STL.ItemCode = OITM_PROCESS.ItemCode

) AS "No. Posicion",

BEAS_FTHAUPT.ZUSATZTEXT AS "Información"

 

 

 

 

 

FROM BEAS_FTPOS

INNER JOIN BEAS_FTHAUPT 

    ON BEAS_FTPOS.BELNR_ID = BEAS_FTHAUPT.BELNR_ID

INNER JOIN OITM AS OITM_MAIN

    ON BEAS_FTPOS.ItemCode = OITM_MAIN.ItemCode

INNER JOIN OITM AS OITM_PROCESS

    ON OITM_PROCESS.ItemCode = CONCAT('A-', BEAS_FTPOS.ItemCode)

INNER JOIN ITM1 T1 

    ON OITM_MAIN.ItemCode = T1.ItemCode

 

LEFT Join ORDR

        ON ORDR.DocEntry = BEAS_FTPOS.DocEntry

 

 

LEFT JOIN BEAS_FTSTL 

    ON BEAS_FTSTL.BELNR_ID = BEAS_FTHAUPT.BELNR_ID

    AND BEAS_FTSTL.ART1_ID NOT LIKE '[A-I]%'

 

WHERE BEAS_FTPOS.ItemCode LIKE 'CE%'       

  AND T1.PriceList = '1'                  

  AND OITM_PROCESS.ItemCode LIKE 'A-CE%'  

  AND BEAS_FTPOS.ABGKZ = 'n'       

 

GROUP BY 

BEAS_FTHAUPT.AUFTRAG,

    BEAS_FTHAUPT.BELNR_ID, 

    BEAS_FTHAUPT.Knd_id, 

    BEAS_FTHAUPT.kndname, 

    BEAS_FTPOS.ItemCode, 

    BEAS_FTPOS.ItemName,

    BEAS_FTHAUPT.ItemCode,

BEAS_FTPOS.menge_verbrauch,

OITM_PROCESS.ItemCode,

ORDR.NumAtCard,

  BEAS_FTHAUPT.TYP,

    BEAS_FTHAUPT.LFGDAT,

BEAS_FTHAUPT.ZUSATZTEXT

 

ORDER BY

    BEAS_FTHAUPT.BELNR_ID ASC

 

I have the duplication with this left join LEFT JOIN BEAS_FTSTL 
    ON BEAS_FTSTL.BELNR_ID = BEAS_FTHAUPT.BELNR_ID
    AND BEAS_FTSTL.ART1_ID NOT LIKE '[A-I]%' in field -- Column 'Position Name' with the first 4 characters of each 'ART1_ID' value
    STRING_AGG(LEFT(ISNULL(BEAS_FTSTL.ART1_ID, ''), 4), ', ') AS [Position Name],

 

Vote

0

• 

  MassimoSardi

  • February 17, 2025 07:26

  Hy, you should consider the baseline to avoid unwanted duplicated lines.

  look at this

  

Please sign in to leave a comment.