WITH TMP AS (SELECT
T3.PML_ITEM_NO,
nvl(T2.MODIFY_DRAW_PAGE ,T2.PAGE_NO) AS PAGE_NO,
T2.BLOCK,
ROW_NUMBER () OVER (
PARTITION BY T2.PROJ_NO,
T2.SPOOL_CODE, T3.MAT_ID
ORDER BY
T3.PML_ITEM_NO
) AS idx,
T1.PROJ_NO,
T4.PROJ_ID,
T2.DRAW_ID,
T1.SPOOL_CODE,
T1.SPOOL_VERSION,
T2.HOLD_NO,
T2.PIPE_CLASS,
NVL(T3.ORI_LENGTH,T3.LENGTH) AS "LENGTH",
T3.CUT_REMARK,
T3.SHOP_MAT_ORDER_NO,
T4.PROJ_ID ||' ' ||T4.PROJ_NAME AS PRINT_NAME,
T1.ORDER_NO,
T3.BENDING_FLAG,
CASE
WHEN T3.BENDING_FLAG = '1' THEN nvl(T3.GROOVE_NUM,0) ||'弯'
ELSE
TO_CHAR (nvl(T3.GROOVE_NUM,0))
END GROOVE,
T3.HEAT_NO,
T2.IS_PENETRATION_PIECE,
T2.REVISION_TYPE,
T3.MAT_ID,
nvl(T5.DESCRIPTION, T3.mat_Desc) AS MAT_DESC
FROM
CP_WORK_ORDER_PIPESPOOL T1
JOIN PML_ITEM_MFGLIST_PIPESPOOL T2 ON T1.SPOOL_CODE = T2.SPOOL_CODE
AND T1.SPOOL_VERSION = T2.SPOOL_VERSION
AND T1.PROJ_NO = T2.PROJ_NO
JOIN PML_ITEM_MFGLIST_PIPEPART T3 ON T2.SPOOL_CODE = T3.SPOOL_CODE
AND T2.SPOOL_VERSION = T3.SPOOL_VERSION
AND T2.PROJ_NO = T3.PROJ_NO
LEFT JOIN PROJ T4 ON T1.PROJ_NO = T4.PROJ_NO
LEFT JOIN
ERP_INVENTORY_PART T5
ON T5.PART_NO = T3.MAT_ID
WHERE
T2.TOP_VERSION_FLAG = '8'
AND T2.DELETE_FLAG != '8'
AND T3.DELETE_FLAG != '8'
AND T3.MAT_ID IS NOT NULL
AND T3.WORKPIECE_FLAG != '1'
AND T2.IS_PENDING = '9'
<if test="process == '下料'">
AND (T3.PART_TYPE = '管材' or T3.PART_TYPE = '管材/PIPE')
</if>
<if test="process == '装配'">
AND T3.PART_TYPE = '附件'
</if>
AND T1.ORDER_NO = #{orderNo}
ORDER BY
T3.MAT_DESC,
T2.HOLD_NO,
T2.SPOOL_CODE)
SELECT
T1.PML_ITEM_NO,
T1.PAGE_NO,
T1.BLOCK,
T1.IDX,
T1.PROJ_NO,
T1.PROJ_ID,
T1.DRAW_ID,
T1.SPOOL_CODE,
T1.SPOOL_VERSION,
T1.HOLD_NO,
T1.PIPE_CLASS,
T1."LENGTH",
T1.CUT_REMARK,
T2.SHOP_MAT_ORDER_NO,
T1.PRINT_NAME,
T1.ORDER_NO,
T1.BENDING_FLAG,
T1.GROOVE,
T1.HEAT_NO,
T1.IS_PENETRATION_PIECE,
T1.REVISION_TYPE,
T1.MAT_ID,
T1.MAT_DESC
FROM
TMP T1
LEFT JOIN
(SELECT
ORDER_NO,
regexp_replace((LISTAGG(SHOP_MAT_ORDER_NO,',') WITHIN GROUP (ORDER BY SHOP_MAT_ORDER_NO)), '([^,]+)(,\1)*(,|$)', '\1\3') AS SHOP_MAT_ORDER_NO
FROM (select distinct ORDER_NO,SHOP_MAT_ORDER_NO from TMP )
WHERE SHOP_MAT_ORDER_NO IS NOT NULL
GROUP BY ORDER_NO
) T2
ON T1.ORDER_NO = T2.ORDER_NO
不要用with as直接换成select
最新发布
SQL格式设置陷阱
探讨了在SQL语句中使用连接符“||”时，如何影响输出格式设置的有效性，特别指出setnumformat与colCODE_PROVformat999990.99在特定情况下的无效性。
扫一扫
935
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
