Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
Author |
Topic |
acdacd
Yak Posting Veteran
63 Posts |
Posted - 2006-01-07 : 14:54:18
|
Dear all,I have a query like this"select TableA.IDfrom TableA Right Join TableB on TableA.ID = TableB.MID"(The ColA is type of int)Result Null 5 3 NullHowever, 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.MIDThxbe a hardworking people!! |
|
Kristen
Test
22859 Posts |
Posted - 2006-01-07 : 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
3279 Posts |
Posted - 2006-01-07 : 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 TableA2. There is no match in TableBWould the 2 different kinds of null's be logically different?__________________________I have nil problemsrockmoose |
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
Posted - 2006-01-07 : 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. |
|
|
|
|
|
|
|