| Author |
 Topic  |
|
|
acdacd
Yak Posting Veteran
63 Posts |
 Posted - 01/07/2006 : 14:54:18
|
Dear all,
I have a query like this
"select TableA.ID
from TableA Right Join TableB on TableA.ID = TableB.MID"
(The ColA is type of int)
Result
Null
5
3
Null
However, I would the the presentation is
Wanted Result
'Nil'
5
3
'Nil'
How should i write this query?
I have tried this but fail(i guess is datatype problem)
select isnull(TableA.ColA, 'Nil')
from Table! Right Join TableB on TableA.ID = TableB.MID
Thx
be a hardworking people!! |
|
|
Kristen
Test
United Kingdom
22191 Posts |
Posted - 01/07/2006 : 15:01:28
|
Your problem is that you are trying to output a Text String when an Integer column IS NULL - so there is a datatype mismatch between the Text String and the Integer. What you will need to do is to convert the INT to a Text String, and then that will be compatible with the "Nil" string.
However, you would be much better off doing this in your application layer. If you convert the INT to a Text String then that's the datatype that the application will think it has received - so we are no long Numeric, and you lose all the benefits of the application thinking that that column is numeric.
So personally I would have the application layer check for the value being "NULL" and change it to "Nil" at that point.
That said, to do it in SQL you would use:
SELECT isnullCOALESCE(CONVERT(varchar(20), TableA.ColA), 'Nil')
(Note that I have substituted COALESCE for ISNULL as that is Standards-Compliant, and unfortunately ISNULL isn't!, but either will work for your purposes, although COALESCE has the benefit of returning the FIRST non-null parameter from a variable number of parameters, whereas ISNULL takes precisely two parameters only)
Kristen |
 |
|
|
rockmoose
SQL Natt Alfen
Sweden
3279 Posts |
Posted - 01/07/2006 : 15:24:31
|
In your case, what is the logical difference between NULL 'Nil' and 0 ?
Since you are doing a right join to TableB in the example,
TableA.ID can be null out of 2 reasons:
1. The column is null in TableA
2. There is no match in TableB
Would the 2 different kinds of null's be logically different?
__________________________
I have nil problems
rockmoose |
|
|
|
jsmith8858
Dr. Cross Join
USA
7423 Posts |
Posted - 01/07/2006 : 16:58:48
|
| Advice: Use a LEFT JOIN. Always select FROM your primary table, and join to your auxiliary tables. Much, much clearer and easier to expand upon (i.e., adding other joins) as needed and it is a great habit to get into. |
|
|
| |
Topic |
|
Isnull problem
