production - query

DCastro

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],
 

Comments

1 comment

Date Votes
  • Comment author
    MassimoSardi

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

    look at this

    0

Please sign in to leave a comment.