Oracle SELECT-Query does not return the expected row-CodePudding

can somebody explain why this statement

select au.new_text, au.old_text
from bsaudt_cd_table au,
     bscore_sample_cont sc,
     bscore_sample sa 
where REGEXP_SUBSTR(au.visible_identification, '[^;] ') = TO_CHAR(sc.id) 
  and SUBSTR(au.visible_identification, INSTR(au.visible_identification, ';') 1) = TO_CHAR(sc.rno) 
  and sc.sample_id = sa.id 
  and au.record_id = sc.record_id 
  and au.table_name = 'BSCORE_SAMPLE_CONT'
  and au.column_name = 'DEPARTMENT_ID'
  and sa.id = 386608 and sc.id = 2207021 
  and au.old_text is null

is not returning the marked row in the screenshot below?

When I change the last line of the query in

and au.old_text is not null

lines #4 and #6 are returned...

Thank you very much in advance!

CodePudding user response：

Based on your comment:

This:

1 Typ=1 Len=6: 40,110,117,108,108,41
               ---------------------
               this

means that column contains

SQL> select chr(40), chr(110), chr(117), chr(108), chr(108), chr(41) from dual;

C C C C C C
- - - - - -
( n u l l )       --> this

SQL>

i.e. literally column contents is a string '(null)' (open bracket, letters "n", "u", "l", "l", closing bracket).

Run update and set it to null:

update that_table set
  old_text = null
  where <conditions that uniquely identify that row>

That should fix it.