SQL问题 - 我的帆软

SQL问题

SELECT

F.MATERIAL_CODE 产品编码,

F.MATERIAL_NAME 产品名称,

F.OM_CLASS_L3_NAME 类别,

F.ORGANIZATION_NAME 库存组织,

F.SUBINVENTORY_NAME 子库存,

SUM(F.STD_INV_AM)/10000 AS STD_INV_AM,

SUM(F.STD_INV_NM) AS STD_INV_NM,

SUM(F.CVT_INV_NM) AS CVT_INV_NM,

F.EXPIRY_AGING_DESC INV_AGE,

F.EXPIRY_AGING_NUM INV_AGE_ORD

FROM

ADS_EBS_INV_HQTY_DETAIL F

WHERE

1 = 1

AND F.MATERIAL_CLASS_L1_NAME = '${ty}'

AND ( F.OU_ID = 102 OR F.OU_ID = 127 )

AND F.YM_ID = SUBSTR( '${P_S_DATE}', 1, 7 )

AND F.INV_CLASS_01_NAME = '成品库'

AND (TO_DATE('${P_S_DATE}'||'-01', 'YYYY-MM-DD') - NVL(F.PRODUCE_DATE, TO_DATE('${P_S_DATE}'||'-01', 'YYYY-MM-DD'))) >=1200

GROUP BY

F.MATERIAL_CODE,

F.MATERIAL_NAME,

F.OM_CLASS_L3_NAME,

F.ORGANIZATION_NAME,

F.SUBINVENTORY_NAME,

F.EXPIRY_AGING_DESC,

F.EXPIRY_AGING_NUM

ORDER BY STD_INV_NM desc

大佬们这个圈起来的地方需要根据ty改变，当TY=1时是1200，=2时是720，=3时是120，我用IF条件写预览的时候报错，大佬还有没有其他办法能改一下

FineReport 嘘.. 发布于 2024-9-12 14:59

1min目标场景问卷

立即参与

回答问题关注问题

悬赏：3 F币 + 添加悬赏

提示：增加悬赏、完善问题、追问等操作，可使您的问题被置顶，并向所有关注者发送通知

共1回答

最佳回答

Z4u3z1Lv6专家互助
发布于2024-9-12 15:00（编辑于 2024-9-12 15:01）

SELECT

F.MATERIAL_CODE 产品编码,

F.MATERIAL_NAME 产品名称,

F.OM_CLASS_L3_NAME 类别,

F.ORGANIZATION_NAME 库存组织,

F.SUBINVENTORY_NAME 子库存,

SUM(F.STD_INV_AM)/10000 AS STD_INV_AM,

SUM(F.STD_INV_NM) AS STD_INV_NM,

SUM(F.CVT_INV_NM) AS CVT_INV_NM,

F.EXPIRY_AGING_DESC INV_AGE,

F.EXPIRY_AGING_NUM INV_AGE_ORD

FROM

ADS_EBS_INV_HQTY_DETAIL F

WHERE

1 = 1

AND F.MATERIAL_CLASS_L1_NAME = '${ty}'

AND ( F.OU_ID = 102 OR F.OU_ID = 127 )

AND F.YM_ID = SUBSTR( '${P_S_DATE}', 1, 7 )

AND F.INV_CLASS_01_NAME = '成品库'

AND (TO_DATE('${P_S_DATE}'||'-01', 'YYYY-MM-DD') - NVL(F.PRODUCE_DATE, TO_DATE('${P_S_DATE}'||'-01', 'YYYY-MM-DD'))) >='${SWITCH("true",ty=1,1200,ty=2,720,ty=3,120)}'

GROUP BY

F.MATERIAL_CODE,

F.MATERIAL_NAME,

F.OM_CLASS_L3_NAME,

F.ORGANIZATION_NAME,

F.SUBINVENTORY_NAME,

F.EXPIRY_AGING_DESC,

F.EXPIRY_AGING_NUM

ORDER BY STD_INV_NM desc

----------

或者

SELECT

F.MATERIAL_CODE 产品编码,

F.MATERIAL_NAME 产品名称,

F.OM_CLASS_L3_NAME 类别,

F.ORGANIZATION_NAME 库存组织,

F.SUBINVENTORY_NAME 子库存,

SUM(F.STD_INV_AM)/10000 AS STD_INV_AM,

SUM(F.STD_INV_NM) AS STD_INV_NM,

SUM(F.CVT_INV_NM) AS CVT_INV_NM,

F.EXPIRY_AGING_DESC INV_AGE,

F.EXPIRY_AGING_NUM INV_AGE_ORD

FROM

ADS_EBS_INV_HQTY_DETAIL F

WHERE

1 = 1

AND F.MATERIAL_CLASS_L1_NAME = '${ty}'

AND ( F.OU_ID = 102 OR F.OU_ID = 127 )

AND F.YM_ID = SUBSTR( '${P_S_DATE}', 1, 7 )

AND F.INV_CLASS_01_NAME = '成品库'

AND (TO_DATE('${P_S_DATE}'||'-01', 'YYYY-MM-DD') - NVL(F.PRODUCE_DATE, TO_DATE('${P_S_DATE}'||'-01', 'YYYY-MM-DD'))) >='${SWITCH(ty,1,1200,2,720,3,120)}'

GROUP BY

F.MATERIAL_CODE,

F.MATERIAL_NAME,

F.OM_CLASS_L3_NAME,

F.ORGANIZATION_NAME,

F.SUBINVENTORY_NAME,

F.EXPIRY_AGING_DESC,

F.EXPIRY_AGING_NUM

ORDER BY STD_INV_NM desc