沒用sum時,
SELECT
to_char(MMT.TRANSACTION_DATE,'YYYYMMDD') ,
BIC.COMPONENT_ITEM_ID "itemid",
MSIB.SEGMENT1 "item",
MSIB.DESCRIPTION "name",
0-MMT.TRANSACTION_QUANTITY "QTY"
--sum(0-MMT.TRANSACTION_QUANTITY) "QTY"
FROM
bom_departments bd,
BOM_INVENTORY_COMPONENTS BIC,
MTL_MATERIAL_TRANSACTIONS MMT,
MTL_SYSTEM_ITEMS_FVL MSIB,
MTL_SYSTEM_ITEMS_TL MSIT,
MTL_TRANSACTION_TYPES MTTWHERE bd.department_id = MMT.DEPARTMENT_ID and
BIC.COMPONENT_ITEM_ID=MSIB.INVENTORY_ITEM_ID AND
MMT.TRANSACTION_TYPE_ID IN (35) AND (TO_CHAR(MMT.TRANSACTION_DATE, 'YYYYMMDD')='&s_date')
AND MMT.INVENTORY_ITEM_ID = MSIB.INVENTORY_ITEM_ID
AND MSIT.INVENTORY_ITEM_ID = MSIB.INVENTORY_ITEM_ID
AND MSIT.ORGANIZATION_ID = MSIB.ORGANIZATION_ID
AND MMT.ORGANIZATION_ID = MSIB.ORGANIZATION_ID
AND MSIT.LANGUAGE = 'ZHT'
AND MTT.TRANSACTION_TYPE_ID = MMT.TRANSACTION_TYPE_ID
and bd.department_code in('EP4400','EP4410','EP4420','EP4430')
group by BIC.COMPONENT_ITEM_ID, MSIB.SEGMENT1,MSIB.DESCRIPTION,to_char(MMT.TRANSACTION_DATE,'YYYYMMDD')
select結果如下
QTY
1 42258 T407943510 Label Phantom COO Label-79435101(標簽) 25
2 42258 T407943510 Label Phantom COO Label-79435101(標簽) 30
3 42258 T407943510 Label Phantom COO Label-79435101(標簽) 50
4 42258 T407943510 Label Phantom COO Label-79435101(標簽) 100
5 42258 T407943510 Label Phantom COO Label-79435101(標簽) 175
6 42258 T407943510 Label Phantom COO Label-79435101(標簽) 225
7 42258 T407943510 Label Phantom COO Label-79435101(標簽) 250
8 42258 T407943510 Label Phantom COO Label-79435101(標簽) 275
9 42258 T407943510 Label Phantom COO Label-79435101(標簽) 700
10 42258 T407943510 Label Phantom COO Label-79435101(標簽) 1675
QTY列的總數為3505而用了sum後,
SELECT
to_char(MMT.TRANSACTION_DATE,'YYYYMMDD') ,
BIC.COMPONENT_ITEM_ID "itemid",
MSIB.SEGMENT1 "item",
MSIB.DESCRIPTION "name",
sum(0-MMT.TRANSACTION_QUANTITY) "QTY"
FROM
bom_departments bd,
BOM_INVENTORY_COMPONENTS BIC,
MTL_MATERIAL_TRANSACTIONS MMT,
MTL_SYSTEM_ITEMS_FVL MSIB,
MTL_SYSTEM_ITEMS_TL MSIT,
MTL_TRANSACTION_TYPES MTTWHERE bd.department_id = MMT.DEPARTMENT_ID and
BIC.COMPONENT_ITEM_ID=MSIB.INVENTORY_ITEM_ID AND
MMT.TRANSACTION_TYPE_ID IN (35) AND (TO_CHAR(MMT.TRANSACTION_DATE, 'YYYYMMDD')='&s_date')
AND MMT.INVENTORY_ITEM_ID = MSIB.INVENTORY_ITEM_ID
AND MSIT.INVENTORY_ITEM_ID = MSIB.INVENTORY_ITEM_ID
AND MSIT.ORGANIZATION_ID = MSIB.ORGANIZATION_ID
AND MMT.ORGANIZATION_ID = MSIB.ORGANIZATION_ID
AND MSIT.LANGUAGE = 'ZHT'
AND MTT.TRANSACTION_TYPE_ID = MMT.TRANSACTION_TYPE_ID
and bd.department_code in('EP4400','EP4410','EP4420','EP4430')
group by BIC.COMPONENT_ITEM_ID, MSIB.SEGMENT1,MSIB.DESCRIPTION,to_char(MMT.TRANSACTION_DATE,'YYYYMMDD')
結果卻變為
QTY
1 42258 T407943510 Label Phantom COO Label-79435101(標簽) 1806060
為什麼和沒用sum的結果不一樣????????????????
SELECT
to_char(MMT.TRANSACTION_DATE,'YYYYMMDD') ,
BIC.COMPONENT_ITEM_ID "itemid",
MSIB.SEGMENT1 "item",
MSIB.DESCRIPTION "name",
0-MMT.TRANSACTION_QUANTITY "QTY"
--sum(0-MMT.TRANSACTION_QUANTITY) "QTY"
FROM
bom_departments bd,
BOM_INVENTORY_COMPONENTS BIC,
MTL_MATERIAL_TRANSACTIONS MMT,
MTL_SYSTEM_ITEMS_FVL MSIB,
MTL_SYSTEM_ITEMS_TL MSIT,
MTL_TRANSACTION_TYPES MTTWHERE bd.department_id = MMT.DEPARTMENT_ID and
BIC.COMPONENT_ITEM_ID=MSIB.INVENTORY_ITEM_ID AND
MMT.TRANSACTION_TYPE_ID IN (35) AND (TO_CHAR(MMT.TRANSACTION_DATE, 'YYYYMMDD')='&s_date')
AND MMT.INVENTORY_ITEM_ID = MSIB.INVENTORY_ITEM_ID
AND MSIT.INVENTORY_ITEM_ID = MSIB.INVENTORY_ITEM_ID
AND MSIT.ORGANIZATION_ID = MSIB.ORGANIZATION_ID
AND MMT.ORGANIZATION_ID = MSIB.ORGANIZATION_ID
AND MSIT.LANGUAGE = 'ZHT'
AND MTT.TRANSACTION_TYPE_ID = MMT.TRANSACTION_TYPE_ID
and bd.department_code in('EP4400','EP4410','EP4420','EP4430')
group by BIC.COMPONENT_ITEM_ID, MSIB.SEGMENT1,MSIB.DESCRIPTION,to_char(MMT.TRANSACTION_DATE,'YYYYMMDD')
select結果如下
QTY
1 42258 T407943510 Label Phantom COO Label-79435101(標簽) 25
2 42258 T407943510 Label Phantom COO Label-79435101(標簽) 30
3 42258 T407943510 Label Phantom COO Label-79435101(標簽) 50
4 42258 T407943510 Label Phantom COO Label-79435101(標簽) 100
5 42258 T407943510 Label Phantom COO Label-79435101(標簽) 175
6 42258 T407943510 Label Phantom COO Label-79435101(標簽) 225
7 42258 T407943510 Label Phantom COO Label-79435101(標簽) 250
8 42258 T407943510 Label Phantom COO Label-79435101(標簽) 275
9 42258 T407943510 Label Phantom COO Label-79435101(標簽) 700
10 42258 T407943510 Label Phantom COO Label-79435101(標簽) 1675
QTY列的總數為3505而用了sum後,
SELECT
to_char(MMT.TRANSACTION_DATE,'YYYYMMDD') ,
BIC.COMPONENT_ITEM_ID "itemid",
MSIB.SEGMENT1 "item",
MSIB.DESCRIPTION "name",
sum(0-MMT.TRANSACTION_QUANTITY) "QTY"
FROM
bom_departments bd,
BOM_INVENTORY_COMPONENTS BIC,
MTL_MATERIAL_TRANSACTIONS MMT,
MTL_SYSTEM_ITEMS_FVL MSIB,
MTL_SYSTEM_ITEMS_TL MSIT,
MTL_TRANSACTION_TYPES MTTWHERE bd.department_id = MMT.DEPARTMENT_ID and
BIC.COMPONENT_ITEM_ID=MSIB.INVENTORY_ITEM_ID AND
MMT.TRANSACTION_TYPE_ID IN (35) AND (TO_CHAR(MMT.TRANSACTION_DATE, 'YYYYMMDD')='&s_date')
AND MMT.INVENTORY_ITEM_ID = MSIB.INVENTORY_ITEM_ID
AND MSIT.INVENTORY_ITEM_ID = MSIB.INVENTORY_ITEM_ID
AND MSIT.ORGANIZATION_ID = MSIB.ORGANIZATION_ID
AND MMT.ORGANIZATION_ID = MSIB.ORGANIZATION_ID
AND MSIT.LANGUAGE = 'ZHT'
AND MTT.TRANSACTION_TYPE_ID = MMT.TRANSACTION_TYPE_ID
and bd.department_code in('EP4400','EP4410','EP4420','EP4430')
group by BIC.COMPONENT_ITEM_ID, MSIB.SEGMENT1,MSIB.DESCRIPTION,to_char(MMT.TRANSACTION_DATE,'YYYYMMDD')
結果卻變為
QTY
1 42258 T407943510 Label Phantom COO Label-79435101(標簽) 1806060
為什麼和沒用sum的結果不一樣????????????????
SELECT
to_char(MMT.TRANSACTION_DATE,'YYYYMMDD') ,
BIC.COMPONENT_ITEM_ID "itemid",
MSIB.SEGMENT1 "item",
MSIB.DESCRIPTION "name",
0-MMT.TRANSACTION_QUANTITY "QTY"
--sum(0-MMT.TRANSACTION_QUANTITY) "QTY"
FROM
bom_departments bd,
BOM_INVENTORY_COMPONENTS BIC,
MTL_MATERIAL_TRANSACTIONS MMT,
MTL_SYSTEM_ITEMS_FVL MSIB,
MTL_SYSTEM_ITEMS_TL MSIT,
MTL_TRANSACTION_TYPES MTTWHERE bd.department_id = MMT.DEPARTMENT_ID and
BIC.COMPONENT_ITEM_ID=MSIB.INVENTORY_ITEM_ID AND
MMT.TRANSACTION_TYPE_ID IN (35) AND (TO_CHAR(MMT.TRANSACTION_DATE, 'YYYYMMDD')='&s_date')
AND MMT.INVENTORY_ITEM_ID = MSIB.INVENTORY_ITEM_ID
AND MSIT.INVENTORY_ITEM_ID = MSIB.INVENTORY_ITEM_ID
AND MSIT.ORGANIZATION_ID = MSIB.ORGANIZATION_ID
AND MMT.ORGANIZATION_ID = MSIB.ORGANIZATION_ID
AND MSIT.LANGUAGE = 'ZHT'
AND MTT.TRANSACTION_TYPE_ID = MMT.TRANSACTION_TYPE_ID
and bd.department_code in('EP4400','EP4410','EP4420','EP4430')
group by BIC.COMPONENT_ITEM_ID, MSIB.SEGMENT1,MSIB.DESCRIPTION,to_char(MMT.TRANSACTION_DATE,'YYYYMMDD')
那么,还是请楼主确认一个问题,第1个语句都全了吗? 不妨在第2个语句上加个 max(rownum),确定第1个语句的结果记录数 = 第2个语句。
然后,看能不能简化一下逻辑,缩小一下范围,再让大家帮分析分析。当然,最好是要有测试数据。
你不用sum的时候他显示的每一行的qty
但是你用了sum之后,是将to_char(MMT.TRANSACTION_DATE,'YYYYMMDD') ,
BIC.COMPONENT_ITEM_ID ,
MSIB.SEGMENT1 ,
MSIB.DESCRIPTION相同的值分为了一组,把这组的所有的qty相加之后的结果。
你把这个执行的结果贴出来看下SELECT
to_char(MMT.TRANSACTION_DATE,'YYYYMMDD') ,
BIC.COMPONENT_ITEM_ID "itemid",
MSIB.SEGMENT1 "item",
MSIB.DESCRIPTION "name",
count(MMT.TRANSACTION_QUANTITY)
FROM
bom_departments bd,
BOM_INVENTORY_COMPONENTS BIC,
MTL_MATERIAL_TRANSACTIONS MMT,
MTL_SYSTEM_ITEMS_FVL MSIB,
MTL_SYSTEM_ITEMS_TL MSIT,
MTL_TRANSACTION_TYPES MTTWHERE bd.department_id = MMT.DEPARTMENT_ID and
BIC.COMPONENT_ITEM_ID=MSIB.INVENTORY_ITEM_ID AND
MMT.TRANSACTION_TYPE_ID IN (35) AND (TO_CHAR(MMT.TRANSACTION_DATE, 'YYYYMMDD')='&s_date')
AND MMT.INVENTORY_ITEM_ID = MSIB.INVENTORY_ITEM_ID
AND MSIT.INVENTORY_ITEM_ID = MSIB.INVENTORY_ITEM_ID
AND MSIT.ORGANIZATION_ID = MSIB.ORGANIZATION_ID
AND MMT.ORGANIZATION_ID = MSIB.ORGANIZATION_ID
AND MSIT.LANGUAGE = 'ZHT'
AND MTT.TRANSACTION_TYPE_ID = MMT.TRANSACTION_TYPE_ID
and bd.department_code in('EP4400','EP4410','EP4420','EP4430')
group by BIC.COMPONENT_ITEM_ID, MSIB.SEGMENT1,MSIB.DESCRIPTION,to_char(MMT.TRANSACTION_DATE,'YYYYMMDD')
你把这个执行的结果贴出来看下SELECT
to_char(MMT.TRANSACTION_DATE,'YYYYMMDD') ,
BIC.COMPONENT_ITEM_ID "itemid",
MSIB.SEGMENT1 "item",
MSIB.DESCRIPTION "name",
count(MMT.TRANSACTION_QUANTITY)
FROM
bom_departments bd,
BOM_INVENTORY_COMPONENTS BIC,
MTL_MATERIAL_TRANSACTIONS MMT,
MTL_SYSTEM_ITEMS_FVL MSIB,
MTL_SYSTEM_ITEMS_TL MSIT,
MTL_TRANSACTION_TYPES MTTWHERE bd.department_id = MMT.DEPARTMENT_ID and
BIC.COMPONENT_ITEM_ID=MSIB.INVENTORY_ITEM_ID AND
MMT.TRANSACTION_TYPE_ID IN (35) AND (TO_CHAR(MMT.TRANSACTION_DATE, 'YYYYMMDD')='&s_date')
AND MMT.INVENTORY_ITEM_ID = MSIB.INVENTORY_ITEM_ID
AND MSIT.INVENTORY_ITEM_ID = MSIB.INVENTORY_ITEM_ID
AND MSIT.ORGANIZATION_ID = MSIB.ORGANIZATION_ID
AND MMT.ORGANIZATION_ID = MSIB.ORGANIZATION_ID
AND MSIT.LANGUAGE = 'ZHT'
AND MTT.TRANSACTION_TYPE_ID = MMT.TRANSACTION_TYPE_ID
and bd.department_code in('EP4400','EP4410','EP4420','EP4430')
group by BIC.COMPONENT_ITEM_ID, MSIB.SEGMENT1,MSIB.DESCRIPTION,to_char(MMT.TRANSACTION_DATE,'YYYYMMDD')