May 7, 2003 at 12:27 am
Hi,
I have a select statment with a group by clause.
eg : select gender, count(*) from ulist where status=1 group by gender
There are lots of enteries in the database which dont have a gender associated with them. So as the result of the query it shows a empty field for all the records with no gender specified and then shows count for male and female respectively.
64
Male 75
Female 63
I want to replace the null or empty field with a value like 'none' so that it shows
None 64
Male 75
Female 63
I tried using replace command in the select query for the gender field but no use.
Can you please suggest how can i change the empty fields returned by the select query?
Thanks and regards
Hitendra
May 7, 2003 at 12:29 am
Try :
Select IsNull(gender,'None'), count(*) from ulist where status=1 group by gender
May 7, 2003 at 12:33 am
No I tried that earlier but it doesn't work. It still gives a empty field instead of showing 'None'
May 7, 2003 at 1:14 am
Is your gender field empty or null?
By the sounds of other posts, it empty rather than null and so you should use a case statement:
select case
when gender <> '' then gender
else 'None'
end as gender
from ulist .....
IsNull only works on null fields.
Jeremy
May 7, 2003 at 1:31 am
Thanks, this was simply a perfect solution. I never knew we can use a case statement within select.
Hey i would like to know more about such functions that can be induced in the select query this will decrease a lot of programming.
Where can i get a resource for such functions usable with select query?
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply