Search code examples
stringdb2comparedb2-luw

db2 empty string equality comparison

I want to do equal comparison between empty strings('' = '')

Under query not work. I think ''='' does not work.

SELECT RESOURCE_ID  
FROM DEV_RESOURCE
WHERE PHYSICAL_NAME = 'g'                   
    AND 
    CASE WHEN '' = '' THEN '1' ELSE RESOURCE_DIV END = CASE WHEN '' = '' THEN  '1' ELSE '' END
 -- AND DECODE('', '','1', RESOURCE_DIV) = DECODE('','','1','')
WITH UR;

Like comment, changing query using decode function seems great but I don't want to.

Solution

  • The reason is "Oracle compatibility".

    Before create database I modified registry variable which allow Oracle compatibility. (db2set DB2_COMPATIBILITIY_VECTOR = ORA)

    Unlike DB2 Oracle treat null same as empty string(''). So oracle compatibility makes ''='' brings error.