Skip to Main Content
  • Questions
  • MERGE statement including CASE statement does not update row as expected

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, Simon.

Asked: November 10, 2021 - 2:45 pm UTC

Last updated: November 12, 2021 - 5:13 pm UTC

Version: 11.2.0.4.0

Viewed 100+ times

You Asked

Good afternoon community,

I'm struggling with a MERGE statement that includes a CASE, I cannot make it work and I'm wondering if what I'm trying to do is possible.
Please note that my unit-test is available here https://livesql.oracle.com/apex/livesql/s/mmi0chf961e1htuoanc2718or

My original merge has more predicates and has ore data but for the sake of the understanding I created a small use-case

G TABLE
=======

|REF|GLABEL|INST|DEV|
---------------------
|10 |      |    |USD|


I TABLE
=======
|REF|GKEY|
----------
|123|10  |


I and G are joined on I.GKEY = G.REF

M_TABLE
=======
|REF|DEV|GEN|
-------------
|-1 |USD|1  |



Through my merge statement I want M.REF to be set to 123 and it works find with the sql below

merge into M
using (
    select I.ref, 
           G.dev, 
           G.glabel
    from I join G on I.gkey = G.ref
) S
on (M.dev <> ' '
    and M.dev = S.dev
    --and case when M.gen > 0 then ' ' else '-1' end = S.glabel
    )
when matched then update set M.ref = S.ref;


Now you can see the CASE predicate is commented because if I uncomment it then the merge does not update anything and I don't know why

From my unique row in M, gen =1 so I guess my case is changed as "' ' = S.glabel".
From my unit data, the predicate seems valid and verified so I don't see why this predicate is failing

Any feedback is appreciated

Simon

with LiveSQL Test Case:

and Chris said...

The problem is G.glabel is a CHAR(10). This means the database will blank pad any values you insert in it so it's 10 characters long.

So when you have:

and case when M.gen > 0 then ' ' else '-1' end = S.glabel


You need to compare by padding up to 10 spaces or removing the extra spaces.

One way to check the merge matches what you want is to turn it into a query:

select * from M 
left join  ( 
    select I.ref,  
           G.dev,  
           G.glabel 
    from I join G on I.gkey = G.ref 
) s
on (M.dev <> ' ' 
  and M.dev = S.dev 
  and case when M.gen > 0 then ' ' else '-1' end = S.glabel 
);

REF    DEV       GEN       REF DEV       GLABEL   
    -1 USD         1    <null> <null>    <null>  

select * from M 
left join  ( 
    select I.ref,  
           G.dev,  
           G.glabel 
    from I join G on I.gkey = G.ref 
) s
on (M.dev <> ' ' 
  and M.dev = S.dev 
  and case when M.gen > 0 then '          ' else '-1' end = S.glabel 
);


REF    DEV       GEN    REF DEV   GLABEL       
    -1 USD         1    123 USD 

Rating

  (3 ratings)

Comments

Thanks for the obvious, one more question if I may

Simon, November 12, 2021 - 7:27 am UTC

Thank you Chris for your answer that seems obvious.

One point is puzzling me.

If I do :
- select * from G where glabel = ' '; -- 1 row is returned
- select * from G where glabel = ' '; --1 row is returned

Because both sqls returned the same row, I though I could use ' ' in my case statement but as per your answer I need to add the extra blanks.

Therefore could you explain me why it's working for the SELECT statement but not for the CASE.

I thank you in advance

sorry for the typo in my last comment

Simon, November 12, 2021 - 7:40 am UTC

Hello,

It looks in my comment that the 2nd sql (or maybe I mistype)
select * from G where glabel = '          ';


was replaced with
select * from G where glabel = ' ';


So what I was saying is both sql below returned the same row so this is why I put a 'single' blank in my CASE but it looks it does not have the same behaviour.

Is there an explanation to this?

Thanks and sorry for the misleading comment
Chris Saxon
November 12, 2021 - 5:13 pm UTC

From the docs:

Oracle uses blank-padded comparison semantics only when both values in the comparison are either expressions of data type CHAR, NCHAR, text literals, or values returned by the USER function.

So in this case you're comparing a CHAR column to a text literal (some number of spaces). So the database uses blank-padding to see if they're the same.

In the original example, the literal is "buried" in a CASE expression, so blank-padding doesn't apply.

thank you for the clarification

A reader, November 15, 2021 - 7:09 am UTC

Thanks Chris for the clarification.

It makes sense

More to Explore

SQL

The Oracle documentation contains a complete SQL reference.