Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 248
  • Last Modified:

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.
0
bolenka
Asked:
bolenka
  • 4
  • 3
  • 2
  • +2
4 Solutions
 
momi_sabagCommented:
what do you mean if they are empty? the answers?

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
0
 
Anurag ThakurTechnical ManagerCommented:
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
0
 
bolenkaAuthor Commented:
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.
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
momi_sabagCommented:
select id, answer
(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
where answer is not null
0
 
jamesguCommented:
select  ID , Answer1 AS Answer
from yourTable
where  ID=1 AND Answer1  IS NOT NULL
UNION
select  ID , Answer2 AS Answer
from yourTable
where  ID=1 AND Answer2  IS NOT NULL
UNION
select  ID , Answer3 AS Answer
from yourTable
where  ID=1 AND Answer3  IS NOT NULL
0
 
Anurag ThakurTechnical ManagerCommented:
momi_sabag has given you the answer before i can even react ot it :)
0
 
bolenkaAuthor Commented:
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(15390)

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

0
 
bolenkaAuthor Commented:
sorry...copied the alter function part 2 x. just ignore the first alter statement.
0
 
sm394Commented:
try that any issues let me know

ALTER FUNCTION fnTest
(
      -- Add the parameters for the function here
      @ID int
      
)
RETURNS
@Table_Var TABLE
(
      -- Add the column definitions for the TABLE variable here
      ID int IDENTITY,
      Answer nvarchar(255)
)
AS
BEGIN
      -- Fill the table variable with the rows for your result set


INSERT INTO @Table_Var (Answer)
select   Answer1 AS Answer
from yourTable
where  ID=@ID AND Answer1 IS NOT NULL
UNION
select   Answer2 AS Answer
from yourTable
where  ID=@ID AND Answer2 IS NOT NULL
      RETURN
END
0
 
momi_sabagCommented:
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
0
 
bolenkaAuthor Commented:
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!
0

Featured Post

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

  • 4
  • 3
  • 2
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now