Please start any new threads on our new site at We've got lots of great SQL Server experts to answer whatever question you can come up with.

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | Active Topics | Members | Search | Forum FAQ
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 Transact-SQL (2000)
 Isnull problem
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Yak Posting Veteran

63 Posts

Posted - 01/07/2006 :  14:54:18  Show Profile  Reply with Quote

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)

However, I would the the presentation is
Wanted Result

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


be a hardworking people!!


United Kingdom
22859 Posts

Posted - 01/07/2006 :  15:01:28  Show Profile  Reply with Quote
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)

Go to Top of Page

SQL Natt Alfen

3279 Posts

Posted - 01/07/2006 :  15:24:31  Show Profile  Reply with Quote
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

Go to Top of Page

Dr. Cross Join

7423 Posts

Posted - 01/07/2006 :  16:58:48  Show Profile  Visit jsmith8858's Homepage  Reply with Quote
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
  Previous Topic Topic Next Topic  
 Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.03 seconds. Powered By: Snitz Forums 2000