某一特定物品在倉庫'CK01'和 'CK02'是否為超過60天的呆料
如果兩個倉庫都有的物品,只要判斷在'CK01'這個倉庫是否超過60天就好了(即使在'CK02'是符合條件的呆料也不算)。
總感覺偶下面寫的不妥,請路過的大俠幫忙理一下思路~~
SELECT DISTINCT ITEM_NO INTO V_ITEM
FROM IV_STOC_D
WHERE ORG_ID = P_ORG
AND STOC_NO IN ('CK01', 'CK02')
AND LAST_TRDATE <= (SYSDATE - 60)
AND STOC_QTY > 0
AND ITEM_NO=P_ITEM
AND (ITEM_NO IN (SELECT ITEM_NO FROM IV_STOC_D WHERE STOC_NO='CK01')
OR ITEM_NO IN (SELECT ITEM_NO FROM IV_STOC_D WHERE STOC_NO='CK02'
AND ITEM_NO NOT IN(SELECT ITEM_NO FROM IV_STOC_D WHERE STOC_NO='CK01')))
如果兩個倉庫都有的物品,只要判斷在'CK01'這個倉庫是否超過60天就好了(即使在'CK02'是符合條件的呆料也不算)。
總感覺偶下面寫的不妥,請路過的大俠幫忙理一下思路~~
SELECT DISTINCT ITEM_NO INTO V_ITEM
FROM IV_STOC_D
WHERE ORG_ID = P_ORG
AND STOC_NO IN ('CK01', 'CK02')
AND LAST_TRDATE <= (SYSDATE - 60)
AND STOC_QTY > 0
AND ITEM_NO=P_ITEM
AND (ITEM_NO IN (SELECT ITEM_NO FROM IV_STOC_D WHERE STOC_NO='CK01')
OR ITEM_NO IN (SELECT ITEM_NO FROM IV_STOC_D WHERE STOC_NO='CK02'
AND ITEM_NO NOT IN(SELECT ITEM_NO FROM IV_STOC_D WHERE STOC_NO='CK01')))
select item_no from ck01 where LAST_TRDATE <= (SYSDATE - 60)
union
(select item_no from ck02 where LAST_TRDATE <= (SYSDATE - 60)
minus
select item_no from ck01 where LAST_TRDATE > (SYSDATE - 60))
字段:
ORG_ID --公司代號
ITEM_NO --物品代號
STOC_NO --倉庫代號
LAST_TRDATE --最近使用的日期
STOC_QTY --庫存量
-----------------------
declare v_item varchar2(20);
begin
v_item:=NVL((select item_no from IV_STOC_D where ORG_ID = '100' and
stoc_no='CK01' and LAST_TRDATE<=(SYSDATE - 60)and STOC_QTY > 0 and item_no='CHAUX00011'
union
(select item_no from IV_STOC_D where ORG_ID = '100' and stoc_no ='DW02' and LAST_TRDATE <= (SYSDATE - 60) and STOC_QTY > 0 and item_no='CHAUX00011'
minus
select item_no from IV_STOC_D where ORG_ID = '100' and stoc_no='CK01' and LAST_TRDATE > (SYSDATE - 60) and STOC_QTY > 0 and item_no='CHAUX00011'),'')dbms_output.put_line ('v_item='||v_item);
end;
-----------------------
這一句單獨執行能得出item_no的數據:((select item_no from IV_STOC_D where ORG_ID = '100' and
stoc_no='CK01' and LAST_TRDATE<=(SYSDATE - 60)and STOC_QTY > 0 and item_no='CHAUX00011'
union
(select item_no from IV_STOC_D where ORG_ID = '100' and stoc_no ='DW02' and LAST_TRDATE <= (SYSDATE - 60) and STOC_QTY > 0 and item_no='CHAUX00011'
minus
select item_no from IV_STOC_D where ORG_ID = '100' and stoc_no='CK01' and LAST_TRDATE > (SYSDATE - 60) and STOC_QTY > 0 and item_no='CHAUX00011')但是將這個查詢得出的值賦給變量后就出錯。。難道不能這樣賦值嗎?
如果想得到這個復雜查詢值,要怎么處理啊?
-----------------------
declare v_item varchar2(20);
begin
v_item:=NVL((select item_no from IV_STOC_D where ORG_ID = '100' and
stoc_no='CK01' and LAST_TRDATE<=(SYSDATE - 60)and STOC_QTY > 0 and item_no='CHAUX00011'
union
(select item_no from IV_STOC_D where ORG_ID = '100' and stoc_no ='DW02' and LAST_TRDATE <= (SYSDATE - 60) and STOC_QTY > 0 and item_no='CHAUX00011'
minus
select item_no from IV_STOC_D where ORG_ID = '100' and stoc_no='CK01' and LAST_TRDATE > (SYSDATE - 60) and STOC_QTY > 0 and item_no='CHAUX00011'),'');dbms_output.put_line ('v_item='||v_item);
end;
-----------------------
這一句單獨執行能得出item_no的數據:((select item_no from IV_STOC_D where ORG_ID = '100' and
stoc_no='CK01' and LAST_TRDATE<=(SYSDATE - 60)and STOC_QTY > 0 and item_no='CHAUX00011'
union
(select item_no from IV_STOC_D where ORG_ID = '100' and stoc_no ='DW02' and LAST_TRDATE <= (SYSDATE - 60) and STOC_QTY > 0 and item_no='CHAUX00011'
minus
select item_no from IV_STOC_D where ORG_ID = '100' and stoc_no='CK01' and LAST_TRDATE > (SYSDATE - 60) and STOC_QTY > 0 and item_no='CHAUX00011')但是將這個查詢得出的值賦給變量后就出錯。。難道不能這樣賦值嗎?
如果想得到這個復雜查詢值,要怎么處理啊?
下面语句不是 sql的语法啊..
INTO v_item
FROM iv_stoc_d
WHERE org_id = p_org
AND stoc_no IN ('CK01', 'CK02')
AND last_trdate <= (SYSDATE - 60)
AND stoc_qty > 0
AND item_no = p_item