0
select  col_name1, count(1)
from    tab_name
where   col2 = nvl('', col2)
group by col1;

This returns rows in my local DB. But in another server it returns no rows.

But if I replace nvl('', col2) with nvl(null, col2) it returns the rows.

Disillusioned
  • 14,635
  • 3
  • 43
  • 77
Prabhu
  • 1
  • 1
    Please provide a [MCVE], including your Oracle version, DDL for your table and DML for you sample data, that demonstrates the problem as [`''` (empty string) is `NULL`](https://stackoverflow.com/q/203493/1509264) in Oracle and there is no difference between your two queries. I cannot [replicate your issue](http://sqlfiddle.com/#!4/e76e8b/1). – MT0 Sep 23 '17 at 21:57
  • @MT0 I stand corrected. Prabhu, are you certain you're not actually changing `nvl(' ', col2)` to `nvl(null, col2)`? (Note the space in the string of the first expression). – Disillusioned Sep 24 '17 at 06:39
  • '' is not equal to null, instead it is equal to blank. – Ferdinand Gaspar Sep 24 '17 at 23:36
  • @FerdinandGaspar It seems [Oracle does things a little differently](http://sqlfiddle.com/#!4/f8ece/1/0). (I made the same mistake as you.) – Disillusioned Sep 29 '17 at 15:43

0 Answers0