LIKE Operator in MERGE [message #667327] |
Thu, 21 December 2017 01:10 |
pstanand
Messages: 133 Registered: February 2005 Location: Chennai,India
|
Senior Member |
|
|
Hi All,
I'm trying to update a set of rows using merge statement with LIKE operator in the where clause.
But I'm not getting the correct value updated. Here the lvcparm value will have the value 7805.
But sometimes this will come as 78052. So I should consider both 7805 and 78052.
The 78052 value comes occasionally. So I thouhgt of using the LIKE operator. But it is not updating properly.
Kindly let me advise whether I'm correct.
SELECT PARM_VAL
INTO lvcparm
WHERE PROCESS_NAME='BHP';
lvcparm VARCHAR2(7);
MERGE INTO TSG_2500 A
USING (SELECT CLCL_ID,
IPCD_ID
FROM PDPD_PRODUCT
WHERE IPCD_ID = ''''||lvcparm||'%''')B
ON(A.CLCL_ID = B.CLCL_ID)
WHEN MATCHED THEN
UPDATE SET BHP_IND = CASE WHEN LOBD_ID IN ('1001','1011','1012') THEN 'Y' ELSE 'N' END;
|
|
|
|
Re: LIKE Operator in MERGE [message #667329 is a reply to message #667328] |
Thu, 21 December 2017 01:46 |
pstanand
Messages: 133 Registered: February 2005 Location: Chennai,India
|
Senior Member |
|
|
WHERE IPCD_ID LIKE lvcParm||'%'
is not updating while running it in a procedure. Whereas it is updating when run a separate statement.
Also
WHERE IPCD_ID LIKE CHR(39)||lvcParm||'%'||CHR(39)
is also not updating when run in the procedure.
|
|
|
|
Re: LIKE Operator in MERGE [message #667334 is a reply to message #667329] |
Thu, 21 December 2017 05:50 |
cookiemonster
Messages: 13925 Registered: September 2008 Location: Rainy Manchester
|
Senior Member |
|
|
pstanand wrote on Thu, 21 December 2017 07:46
Also
WHERE IPCD_ID LIKE CHR(39)||lvcParm||'%'||CHR(39)
is also not updating when run in the procedure.
I'd be worried if it did as that would mean that you have quotes stored in the ipcd_id column.
|
|
|
Re: LIKE Operator in MERGE [message #667336 is a reply to message #667334] |
Thu, 21 December 2017 07:00 |
pstanand
Messages: 133 Registered: February 2005 Location: Chennai,India
|
Senior Member |
|
|
The parm_val is VARCHAR2(7). I have resolved it by storing the value in a table column and pass the value to a variable and used LIKE.
Thank you all.
|
|
|
|