Home » SQL & PL/SQL » SQL & PL/SQL » LIKE Operator in MERGE (Oracle,12c,Unix)
LIKE Operator in MERGE [message #667327] Thu, 21 December 2017 01:10 Go to next message
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 #667328 is a reply to message #667327] Thu, 21 December 2017 01:40 Go to previous messageGo to next message
quirks
Messages: 82
Registered: October 2014
Member
Just out of curiosity, what happens when you try:
where lvcparm like IPCD_ID||'%'
Re: LIKE Operator in MERGE [message #667329 is a reply to message #667328] Thu, 21 December 2017 01:46 Go to previous messageGo to next message
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 #667333 is a reply to message #667329] Thu, 21 December 2017 05:44 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
What datatype is parm_val?
Re: LIKE Operator in MERGE [message #667334 is a reply to message #667329] Thu, 21 December 2017 05:50 Go to previous messageGo to next message
cookiemonster
Messages: 13917
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 Go to previous messageGo to next message
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.
Re: LIKE Operator in MERGE [message #667337 is a reply to message #667336] Thu, 21 December 2017 07:10 Go to previous message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
?
Your original post implied you were doing that already.
Previous Topic: How to execute a result of another table.
Next Topic: CLOB to file
Goto Forum:
  


Current Time: Thu Mar 28 12:33:56 CDT 2024