c**t
发帖数: 2744 | 1 in oracle, join table a and b on col, which is a nullable column
a.col = b.col doesn't match all
( a.col is null and b.col is null )
OR
( a.col is not null and b.col is not null and a.col = b.col )
does
nvl(a.col, 'N/A') = nvl( b.col, 'N/A') seems work, but if a.col is null and
b.col is 'N/A', then it will fail.
What's the best way to compare them? | B*****g
发帖数: 34098 | 2 why nvl not work?
and
【在 c**t 的大作中提到】
: in oracle, join table a and b on col, which is a nullable column
: a.col = b.col doesn't match all
: ( a.col is null and b.col is null )
: OR
: ( a.col is not null and b.col is not null and a.col = b.col )
: does
: nvl(a.col, 'N/A') = nvl( b.col, 'N/A') seems work, but if a.col is null and
: b.col is 'N/A', then it will fail.
: What's the best way to compare them?
| c**t
发帖数: 2744 | 3 can't predict the a.col doesn't include 'N/A'
【在 B*****g 的大作中提到】
: why nvl not work?
:
: and
| B*****g
发帖数: 34098 | 4 hehe, I believe N/A is null.
【在 c**t 的大作中提到】
: can't predict the a.col doesn't include 'N/A'
| k*********y
发帖数: 41 | 5 try Coalesce
http://www.techonthenet.com/oracle/functions/coalesce.php
and
【在 c**t 的大作中提到】
: in oracle, join table a and b on col, which is a nullable column
: a.col = b.col doesn't match all
: ( a.col is null and b.col is null )
: OR
: ( a.col is not null and b.col is not null and a.col = b.col )
: does
: nvl(a.col, 'N/A') = nvl( b.col, 'N/A') seems work, but if a.col is null and
: b.col is 'N/A', then it will fail.
: What's the best way to compare them?
| B*****g
发帖数: 34098 | 6 how this works?
【在 k*********y 的大作中提到】
: try Coalesce
: http://www.techonthenet.com/oracle/functions/coalesce.php
:
: and
| c**t
发帖数: 2744 | 7 the main reason is to avoid comparing nulls, apparently coalsece won't work.
【在 B*****g 的大作中提到】
: how this works?
| c*****t
发帖数: 1879 | 8 你是说，你的 string 数值有正常的 value，null, 和 'N/A' ？
and
【在 c**t 的大作中提到】
: in oracle, join table a and b on col, which is a nullable column
: a.col = b.col doesn't match all
: ( a.col is null and b.col is null )
: OR
: ( a.col is not null and b.col is not null and a.col = b.col )
: does
: nvl(a.col, 'N/A') = nvl( b.col, 'N/A') seems work, but if a.col is null and
: b.col is 'N/A', then it will fail.
: What's the best way to compare them?
| c**t
发帖数: 2744 | 9 yes
【在 c*****t 的大作中提到】
: 你是说，你的 string 数值有正常的 value，null, 和 'N/A' ？
:
: and
| s******s
发帖数: 508 | 10 Use another string then.
e.g.
nvl(a.col,'NULL')=nvl(b.col,'NULL')
or even use the following if you like,
nvl(a.col,'Fuck off, it is null')=nvl(b.col,'Fuck off, it is null')
and
【在 c**t 的大作中提到】
: in oracle, join table a and b on col, which is a nullable column
: a.col = b.col doesn't match all
: ( a.col is null and b.col is null )
: OR
: ( a.col is not null and b.col is not null and a.col = b.col )
: does
: nvl(a.col, 'N/A') = nvl( b.col, 'N/A') seems work, but if a.col is null and
: b.col is 'N/A', then it will fail.
: What's the best way to compare them?
|
|
