bolenka
asked on
How do I exclude null values from a user defined functionso they don't show up in radio button list?
Here is the code:
select ID , Answer1 AS Answer
from yourTable
where ID=1
UNION
select ID , Answer2 AS Answer
from yourTable
where ID=1
UNION
select ID , Answer3 AS Answer
from yourTable
where ID=1
Except it returns null values in the list if they are empty in the db. How do I EXCLUDE NULL values???
Thanks.
select ID , Answer1 AS Answer
from yourTable
where ID=1
UNION
select ID , Answer2 AS Answer
from yourTable
where ID=1
UNION
select ID , Answer3 AS Answer
from yourTable
where ID=1
Except it returns null values in the list if they are empty in the db. How do I EXCLUDE NULL values???
Thanks.
what do you want to do when the values are null
want to show them in the radio list or what
the solution that we propose will depend on that
want to show them in the radio list or what
the solution that we propose will depend on that
ASKER
I dont want them to show in the radiobuttonlist at all....yes, if the Answer field is empty...do not show them in the radiobuttonlist. Thanks.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I don't think this is working the way I want it to...I have to spend some time trying each one of these before I assign points, what is the"t" for after the select statement momi sabag?
I would like it if it would NOT select the answer at all into the list if the value is NULL:
also, I have to assign an ID to each answer because each answer does not havean id...for example, this is what I have...
ALTER Function [dbo].[radiobuttonListDD] (@ID int)
Returns Table
As
Return
ALTER Function [dbo].[radiobuttonListDD] (@ID int)
Returns Table
As
Return
(select '1' as IDValue , Answer1 AS Answer
from dbo.userAnswers1
where ID=@ID
UNION
select '2' as IDValue , Answer2 AS Answer
from dbo.userAnswers1
where ID=@ID)
--Select * from dbo.radiobuttonListDD(1539 0)
I think is gives me results even if the Answer column is null since I am assigning and ID to each. How do I make them not show up AT ALL if the Answer column is NULL?
Thanks so much
I would like it if it would NOT select the answer at all into the list if the value is NULL:
also, I have to assign an ID to each answer because each answer does not havean id...for example, this is what I have...
ALTER Function [dbo].[radiobuttonListDD] (@ID int)
Returns Table
As
Return
ALTER Function [dbo].[radiobuttonListDD] (@ID int)
Returns Table
As
Return
(select '1' as IDValue , Answer1 AS Answer
from dbo.userAnswers1
where ID=@ID
UNION
select '2' as IDValue , Answer2 AS Answer
from dbo.userAnswers1
where ID=@ID)
--Select * from dbo.radiobuttonListDD(1539
I think is gives me results even if the Answer column is null since I am assigning and ID to each. How do I make them not show up AT ALL if the Answer column is NULL?
Thanks so much
ASKER
sorry...copied the alter function part 2 x. just ignore the first alter statement.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
the t has to be there because of syntax rules
when you use a derived table (that is, a select statement in a where clause surrounded by parenthesis ) you need to give it an alias
when you use a derived table (that is, a select statement in a where clause surrounded by parenthesis ) you need to give it an alias
ASKER
I actually had to change the IS NOT NULL to where Answer <> '' that is how I got the results that I wanted. I wanted the record to not show up in the results table, but the null wasnt working. I tried to split the points to be fair. thanks so much to everyone...I used momi sabag's answer first so that is who I gave the most points. thank you!
try
select id, isnull(answer, 'UnSpecified') from
(select ID , Answer1 AS Answer
from yourTable
where ID=1
UNION
select ID , Answer2 AS Answer
from yourTable
where ID=1
UNION
select ID , Answer3 AS Answer
from yourTable
where ID=1) t