SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

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

acdacd
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)
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
22403 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)

Kristen
Go to Top of Page

rockmoose
SQL Natt Alfen

Sweden
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

rockmoose
Go to Top of Page

jsmith8858
Dr. Cross Join

USA
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  
 New 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.09 seconds. Powered By: Snitz Forums 2000