My problem is that an error occurred when I update with where conditions.
The error message is 00936. 00000 - "missing expression"
If I remove this line where q1.SALE_QTY <> -9
, then that sql works.
Database version : oracle 9i SQL:
merge into TBL_QTY q1
using (
select 'A111' as prod_no
,'BAT0000171624' as part_batch_no
,'172' as cond1_type
,'20201225' as beg_dt
,'20201225' as end_dt
, 17 as obtain_qty
, 17 as sale_qty ) q2
on
( q1.PROD_NO = q2.PROD_NO
and q1.PART_BATCH_NO = q2.PART_BATCH_NO
and q1.COND1_TYPE = q2.COND1_TYPE
and q1.BEG_DT = q2.BEG_DT
and q1.END_DT= q2.END_DT)
WHEN MATCHED THEN
update SET q1.OBTAIN_QTY = 15
,q1.SALE_QTY = 15
where q1.SALE_QTY <> -9
WHEN NOT MATCHED THEN
INSERT (PROD_NO, PART_BATCH_NO, COND1_TYPE, BEG_DT
,END_DT , OBTAIN_QTY , SALE_QTY )
VALUES (q2.PROD_NO, q2.PART_BATCH_NO, q2.COND1_TYPE, q2.BEG_DT
,q2.END_DT , q2.OBTAIN_QTY , q2.SALE_QTY );
One way to achieve the same behavior is to use CASE..WHEN
as follows in UPDATE
clause of the MERGE
statement.
...
...
WHEN MATCHED THEN
update SET q1.OBTAIN_QTY = CASE WHEN q1.SALE_QTY <> -9 THEN 15 ELSE q1.OBTAIN_QTY END
,q1.SALE_QTY = CASE WHEN q1.SALE_QTY <> -9 THEN 15 ELSE q1.SALE_QTY END
-- where q1.SALE_QTY <> -9
WHEN NOT MATCHED THEN
...
...