SELECT MSIF.SEGMENT1 "料號",
MOQD.SUBINVENTORY_CODE "倉庫",
--decode(sum(MOQD.PRIMARY_TRANSACTION_QUANTITY),sum(MOQD.PRIMARY_TRANSACTION_QUANTITY),0) "庫存數量",
sum(MOQD.PRIMARY_TRANSACTION_QUANTITY) "庫存數量",
MOQD.ORGANIZATION_ID "Org",
MSIF.DESCRIPTION "品名"
FROM MTL_ONHAND_QUANTITIES_DETAIL MOQD,
MTL_SYSTEM_ITEMS_FVL MSIF
WHERE MSIF.INVENTORY_ITEM_ID = MOQD.INVENTORY_ITEM_ID AND
MSIF.ORGANIZATION_ID = 83
and MOQD.ORGANIZATION_ID in (88)
--AND to_char(MOQD.Date_Received,'yyyymmdd')<='&P1'
and MSIF.SEGMENT1 in
('L0500CRL03','L05000YL01','L0400CRL02','L030083003','L030143001','L070200M01','L060060S01'
,'L060035S01','L060022S01','L390000006','T050000004','J010017401','J010030401','J010043101'
,'J010070102','J010862001','J010K45001'
)
GROUP BY
msif.INVENTORY_ITEM_ID,
--MOQD.Date_Received,
MOQD.ORGANIZATION_ID,MOQD.SUBINVENTORY_CODE,MSIF.SEGMENT1,MSIF.DESCRIPTION
結果如下:
料號 倉庫 庫存數量 Org 品名
1 J010017401 WDS100 14469 88 不銹鋼174
2 J010030401 WDS100 29454.5 88 不銹鋼304
3 J010043101 WDS100 7952 88 不銹鋼431
4 J010K45001 WDS100 7800 88 不銹鋼k-450
5 L030083003 WES200 500 88 矽酸膠830
6 L030143001 WES200 9000 88 矽酸膠1430#
7 L0400CRL02 WES200 2860 88 鋯砂立維 80-120CRL
8 L0500CRL03 WES200 1860 88 鋯粉 立維300 CRL(鉅東)
9 L060022S01 WES200 11080 88 粒狀熱料22S (莫來砂)
10 L060035S01 WES200 7180 88 粒狀熱料35S (莫來砂)
11 L060060S01 WES200 4805 88 粒狀熱料 60S (莫來砂)
12 L070200M01 WES200 8640 88 粉狀熱料200M(莫來粉)
13 L390000006 WES200 20000 88 二層錐度砂芯 8.7*52+5.6*6.2*14度
14 T050000004 WES200 442 88 測溫棒 S型事實上我是要找出in子名中總共17個料號的,不符合條件的另外三個料號庫存數量就為0,我要的是:
料號 倉庫 庫存數量Org 品名
1 J010017401 WDS100 14469 88 不銹鋼174
2 J010030401 WDS100 29454.5 88 不銹鋼304
3 J010043101 WDS100 7952 88 不銹鋼431
4 J010K45001 WDS100 7800 88 不銹鋼k-450
5 L030083003 WES200 500 88 矽酸膠830
6 L030143001 WES200 9000 88 矽酸膠1430#
7 L0400CRL02 WES200 2860 88 鋯砂立維 80-120CRL
8 L0500CRL03 WES200 1860 88 鋯粉 立維300 CRL(鉅東)
9 L060022S01 WES200 11080 88 粒狀熱料22S (莫來砂)
10 L060035S01 WES200 7180 88 粒狀熱料35S (莫來砂)
11 L060060S01 WES200 4805 88 粒狀熱料 60S (莫來砂)
12 L070200M01 WES200 8640 88 粉狀熱料200M(莫來粉)
13 L390000006 WES200 20000 88 二層錐度砂芯 8.7*52+5.6*6.2*14度
14 T050000004 WES200 442 88 測溫棒 S型
15 L05000YL01 0
16 J010070102 0
17 J010862001 0
MOQD.SUBINVENTORY_CODE "倉庫",
--decode(sum(MOQD.PRIMARY_TRANSACTION_QUANTITY),sum(MOQD.PRIMARY_TRANSACTION_QUANTITY),0) "庫存數量",
sum(MOQD.PRIMARY_TRANSACTION_QUANTITY) "庫存數量",
MOQD.ORGANIZATION_ID "Org",
MSIF.DESCRIPTION "品名"
FROM MTL_ONHAND_QUANTITIES_DETAIL MOQD,
MTL_SYSTEM_ITEMS_FVL MSIF
WHERE MSIF.INVENTORY_ITEM_ID = MOQD.INVENTORY_ITEM_ID AND
MSIF.ORGANIZATION_ID = 83
and MOQD.ORGANIZATION_ID in (88)
--AND to_char(MOQD.Date_Received,'yyyymmdd')<='&P1'
and MSIF.SEGMENT1 in
('L0500CRL03','L05000YL01','L0400CRL02','L030083003','L030143001','L070200M01','L060060S01'
,'L060035S01','L060022S01','L390000006','T050000004','J010017401','J010030401','J010043101'
,'J010070102','J010862001','J010K45001'
)
GROUP BY
msif.INVENTORY_ITEM_ID,
--MOQD.Date_Received,
MOQD.ORGANIZATION_ID,MOQD.SUBINVENTORY_CODE,MSIF.SEGMENT1,MSIF.DESCRIPTION
結果如下:
料號 倉庫 庫存數量 Org 品名
1 J010017401 WDS100 14469 88 不銹鋼174
2 J010030401 WDS100 29454.5 88 不銹鋼304
3 J010043101 WDS100 7952 88 不銹鋼431
4 J010K45001 WDS100 7800 88 不銹鋼k-450
5 L030083003 WES200 500 88 矽酸膠830
6 L030143001 WES200 9000 88 矽酸膠1430#
7 L0400CRL02 WES200 2860 88 鋯砂立維 80-120CRL
8 L0500CRL03 WES200 1860 88 鋯粉 立維300 CRL(鉅東)
9 L060022S01 WES200 11080 88 粒狀熱料22S (莫來砂)
10 L060035S01 WES200 7180 88 粒狀熱料35S (莫來砂)
11 L060060S01 WES200 4805 88 粒狀熱料 60S (莫來砂)
12 L070200M01 WES200 8640 88 粉狀熱料200M(莫來粉)
13 L390000006 WES200 20000 88 二層錐度砂芯 8.7*52+5.6*6.2*14度
14 T050000004 WES200 442 88 測溫棒 S型事實上我是要找出in子名中總共17個料號的,不符合條件的另外三個料號庫存數量就為0,我要的是:
料號 倉庫 庫存數量Org 品名
1 J010017401 WDS100 14469 88 不銹鋼174
2 J010030401 WDS100 29454.5 88 不銹鋼304
3 J010043101 WDS100 7952 88 不銹鋼431
4 J010K45001 WDS100 7800 88 不銹鋼k-450
5 L030083003 WES200 500 88 矽酸膠830
6 L030143001 WES200 9000 88 矽酸膠1430#
7 L0400CRL02 WES200 2860 88 鋯砂立維 80-120CRL
8 L0500CRL03 WES200 1860 88 鋯粉 立維300 CRL(鉅東)
9 L060022S01 WES200 11080 88 粒狀熱料22S (莫來砂)
10 L060035S01 WES200 7180 88 粒狀熱料35S (莫來砂)
11 L060060S01 WES200 4805 88 粒狀熱料 60S (莫來砂)
12 L070200M01 WES200 8640 88 粉狀熱料200M(莫來粉)
13 L390000006 WES200 20000 88 二層錐度砂芯 8.7*52+5.6*6.2*14度
14 T050000004 WES200 442 88 測溫棒 S型
15 L05000YL01 0
16 J010070102 0
17 J010862001 0
解决方案 »
- oracle如何查询大于当前日期的数据(当前日期取0点)
- 怎么我安装的Oracle 11g 的 sqlplus 是win32的啊 应该怎么配置啊?
- oracle 索引问题
- Oracle与插件DevExpress XtraTreeList兼容性问题
- 一次update多个有一定规律的数据
- 关于oracle数据库的连接问题。
- linux oracle数据库 导入数据库数据中文乱码(急急急)
- 高分请各位大虾请指点:Oracle 频繁产生归档日志文件!
- oracle里面怎么建数据库?
- 连接SQL——PLUS问题,用connect / as sysdba,抱错:服务句柄未初始化
- 关于oracle分区表交换分区主键失效的问题
- oracle 9i问题
/* Formatted on 2011-5-17 10:49:03 (QP5 v5.114.809.3010) */
SELECT MSIF.SEGMENT1 "料號",
MOQD.SUBINVENTORY_CODE "倉庫",
--decode(sum(MOQD.PRIMARY_TRANSACTION_QUANTITY),sum(MOQD.PRIMARY_TRANSACTION_QUANTITY),0) "庫存數量",
SUM (NVL(MOQD.PRIMARY_TRANSACTION_QUANTITY,0)) "庫存數量",
MOQD.ORGANIZATION_ID "Org",
MSIF.DESCRIPTION "品名"
FROM MTL_ONHAND_QUANTITIES_DETAIL MOQD, MTL_SYSTEM_ITEMS_FVL MSIF
WHERE MSIF.INVENTORY_ITEM_ID = MOQD.INVENTORY_ITEM_ID
AND MSIF.ORGANIZATION_ID = 83
AND MOQD.ORGANIZATION_ID IN (88)
--AND to_char(MOQD.Date_Received,'yyyymmdd')<='&P1'
AND MSIF.SEGMENT1 IN
('L0500CRL03',
'L05000YL01',
'L0400CRL02',
'L030083003',
'L030143001',
'L070200M01',
'L060060S01',
'L060035S01',
'L060022S01',
'L390000006',
'T050000004',
'J010017401',
'J010030401',
'J010043101',
'J010070102',
'J010862001',
'J010K45001')
GROUP BY msif.INVENTORY_ITEM_ID,
--MOQD.Date_Received,
MOQD.ORGANIZATION_ID,
MOQD.SUBINVENTORY_CODE,
MSIF.SEGMENT1,
MSIF.DESCRIPTION