production - query
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],
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],
0
-
Hy, you should consider the baseline to avoid unwanted duplicated lines.
look at this
Iniciar sesión para dejar un comentario.
Comentarios
1 comentario