Compare 3columns from a row with data Null value
-
Dear all, Please kindly advise my case How to Compare 3columns from a row with data are Null value
A1 A2 A3 B1 B2 B3 C1 C2 C3
25 Null Null 25 Null Null 25 Null Null
Null 20 Null Null 20 Null Null 20 NullSelect * from Table1 Where (A1=B1 and B1=C1) And (A2=B2 and B2=C2) And (A3=B3 and B3=C3)
A1 A2 A3 B1 B2 B3 C1 C2 C3
25 0 0 25 0 0 25 0 0
0 20 0 0 20 0 0 20 0When data value are Null I didn't get result from my query, but after change Null value to 0 (zero) then I have got the result. Could you please advise how to compare data with Null values ? Thanks and best regards
-
Dear all, Please kindly advise my case How to Compare 3columns from a row with data are Null value
A1 A2 A3 B1 B2 B3 C1 C2 C3
25 Null Null 25 Null Null 25 Null Null
Null 20 Null Null 20 Null Null 20 NullSelect * from Table1 Where (A1=B1 and B1=C1) And (A2=B2 and B2=C2) And (A3=B3 and B3=C3)
A1 A2 A3 B1 B2 B3 C1 C2 C3
25 0 0 25 0 0 25 0 0
0 20 0 0 20 0 0 20 0When data value are Null I didn't get result from my query, but after change Null value to 0 (zero) then I have got the result. Could you please advise how to compare data with Null values ? Thanks and best regards
Read up about ISNULL
Select * from Table1 Where (ISNULL(A1,'')=ISNULL(B1,'') and ISNULL(B1,'')=ISNULL(C1,'')) And (ISNULL(A2,'')=ISNULL(B2,'') and ISNULL(B2,'')=ISNULL(C2,'')) And (ISNULL(A3,'')=ISNULL(B3,'') and ISNULL(B3,'')=ISNULL(C3,''))
I hope that copy/paste worked
Never underestimate the power of human stupidity RAH
-
Dear all, Please kindly advise my case How to Compare 3columns from a row with data are Null value
A1 A2 A3 B1 B2 B3 C1 C2 C3
25 Null Null 25 Null Null 25 Null Null
Null 20 Null Null 20 Null Null 20 NullSelect * from Table1 Where (A1=B1 and B1=C1) And (A2=B2 and B2=C2) And (A3=B3 and B3=C3)
A1 A2 A3 B1 B2 B3 C1 C2 C3
25 0 0 25 0 0 25 0 0
0 20 0 0 20 0 0 20 0When data value are Null I didn't get result from my query, but after change Null value to 0 (zero) then I have got the result. Could you please advise how to compare data with Null values ? Thanks and best regards
Try the below query Select * from Table1 Where (ISNULL(A1,0)=ISNULL(B1,0) and ISNULL(B1,0)=ISNULL(C1,0)) And (ISNULL(A2,0)=ISNULL(B2,0) and ISNULL(B2,0)=ISNULL(C2,0)) And (ISNULL(A3,0)=ISNULL(B3,0) and ISNULL(B3,0)=ISNULL(C3,0)) Use isnull with all the column fields. If the value is null it will take it as zero else it will take the coressponding values. I hope this will help you out!!!!
-
Try the below query Select * from Table1 Where (ISNULL(A1,0)=ISNULL(B1,0) and ISNULL(B1,0)=ISNULL(C1,0)) And (ISNULL(A2,0)=ISNULL(B2,0) and ISNULL(B2,0)=ISNULL(C2,0)) And (ISNULL(A3,0)=ISNULL(B3,0) and ISNULL(B3,0)=ISNULL(C3,0)) Use isnull with all the column fields. If the value is null it will take it as zero else it will take the coressponding values. I hope this will help you out!!!!