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.

 All Forums
 SQL Server 2000 Forums
 Transact-SQL (2000)
 Isnull problem

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.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

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
Go to Top of Page

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 TableA
2. There is no match in TableB

Would the 2 different kinds of null's be logically different?

__________________________
I have nil problems

rockmoose
Go to Top of Page

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.
Go to Top of Page
   

- Advertisement -