Soo, I need to import a text file which contains a date column that does not always contain a value. Obviously, just doing to_date() will fail when said column is empty. So I tried a case statement to limit to_date() calls to those cases when that column was filled.
Just, I could not get it to work. So I tried to simplify that CASE statement and boiled it down to:
<FONT size=1>
select CASE WHEN </FONT><FONT color=#0000f0 size=1>''</FONT><FONT size=1> = </FONT><FONT color=#0000f0 size=1>''</FONT><FONT size=1> THEN </FONT><FONT color=#0000f0 size=1>'equal'</FONT><FONT size=1> ELSE </FONT><FONT color=#0000f0 size=1>'not equal'</FONT><FONT size=1> END FROM dual</FONT>
<FONT size=1>The result? Not equal. So I tried</FONT>
<FONT size=1>
select CASE WHEN </FONT><FONT color=#0000f0 size=1>''</FONT><FONT size=1> is null THEN </FONT><FONT color=#0000f0 size=1>'equal'</FONT><FONT size=1> ELSE </FONT><FONT color=#0000f0 size=1>'not equal'</FONT><FONT size=1> END FROM dual
</FONT><FONT size=1>This results in 'equal'.</FONT>
<FONT size=1>WTF? I know that the empty string is null, but why doesn't Oracle convert both empty strings to null, so they are equal? Grmpf.</FONT>