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

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

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