Select Case Statement - SQL Server Forums

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

All Forums

General SQL Server Forums

New to SQL Server Programming

Select Case Statement

Author

Topic

LoriM
Starting Member

29 Posts

Posted - 2005-08-12 : 13:09:31

Hi,

I'm wondering what syntax to use for a select case statement.

I have a list of questions. If a particular question is on the list, then it shouldn't show some other related questions.

For example, "have you been in the military?". If you haven't , then you wouldn't be asked all the military related questions.

So- I'm saying:
select case when question_id = 488 then don't return question_id 220, 962, 963

I'm trying something like this:

select q.client_id,
case when q.question_id = 488 then end q.question_id = 220 else q.question_id = 220 end q.question_id
from questions_yes_no q

How would I say 'dont show' question 220? End is not the way to go, obviously.

If you can see what I'm trying to do, can you point me to an article or bol search?

Thank you for any help.

ajthepoolman
Constraint Violating Yak Guru

384 Posts

Posted - 2005-08-12 : 14:40:38

So, the questions are in a table somewhere? Or is the interface trying to hide questions based on the answers to previous questions?

Aj

Hey, it compiles.

LoriM
Starting Member

29 Posts

Posted - 2005-08-12 : 15:25:07

Hi Aj

First part of your question:
The questions are in a table, but I am working from a query that shows which questions have not been answered. We make a list of questions that have NOT been answered so people can go back and answer the questions, then hopefully disappear from the list.

Second part: I guess a report would be my interface and yes, I'm trying to hide questions based on if a certain question is in this list. For instance if question 488 appears in this list, then hide question 200, 963 and 964.

So hmmm... my question would be is a select case a good way to go about 'hiding' questions?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-08-12 : 15:27:23

For instance if question 488 appears in this list, then hide question 200, 963 and 964.

How do you know which questions to hide? Is that information stored somewhere?

Perhaps, you should provide DDL, DML for sample data, and expected result set in order to get a T-SQL answer rather than us trying to figure out what your environment looks like. Here's a link for details on this (make sure to check out the link in the first post):
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=29090

Tara

LoriM
Starting Member

29 Posts

Posted - 2005-08-12 : 16:10:17

Hi Tara,

I'm working from a couple of different queries.

columns:
question_id
descrip
[wasAnswered?] --- they all are 'NO'
client_id
client
social sec no
social sec no quality

[wasAnswered?] column has been derived from this query:

SELECT c.question_id, c.client_id,
CASE WHEN d.client_id IS NOT NULL THEN 'yes' ELSE 'no' END AS [wasanswered?], Qs_for_HUD.descrip
FROM
(SELECT a.question_id, b.client_id
FROM qs_for_hud a CROSS JOIN

(SELECT DISTINCT client_id
FROM answers_clients) b) c

INNER JOIN
Qs_for_HUD ON c.question_id = Qs_for_HUD.question_id
LEFT OUTER JOIN
Answers_Clients d ON c.question_id = d.question_id AND c.client_id = d.client_ID

WHERE
(CASE WHEN d.client_id IS NOT NULL
THEN 'yes' ELSE 'no' END = 'no')

ORDER BY c.client_id, c.question_id

Then I added other tables to it to get the clients and providers.
Maybe I should take out the END='no' and deal with Yesses and No's?

Results I'd like to see:

question_id----client_id---wasAnswered-------descrip------Client

-----23----------------5--------------no-----------children in household------mary
-----142----------------5--------------no-----------Last 30 day income--------mary
-----1075---------------5--------------no-----------Source of Income------------mary
-----488----------------5--------------no-----------Military Information------------mary

-----23----------------27--------------no-----------children in household------Bob
-----142----------------27--------------no-----------Last 30 day income------Bob
-----1075--------------27--------------no-----------Source of Income--------Bob
-----973----------------27--------------no-----------Military Branches-----------Bob
-----963----------------27--------------no-----------Did You Serve in a War zone?------Bob

So Mary answered NO to Military Information, so we wouldn't care if she forgot/didn't answer the rest of the military questions.

Bob answered YES to Military Information, so it doesn't show in the list, but he forgot to answer one of the military related questions.

This is even confusing to explain. I have to go soon, I'll think more on how I can explain it over the weekend.

Thanks for asking questions to clarify!

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-08-15 : 12:45:12

Please check out the link again that I posted which shows how to provide the DDL, DML for sample data, and expected result set using that sample data.

Tara

LoriM
Starting Member

29 Posts

Posted - 2005-08-16 : 10:09:12

OK. I'm back and can work on this today.

Tara, If the DDL's and DML's show the CREATE and INSERTs, what can I show you if I'm using views from mulitple tables? Would you like to see each table's DDL? Also, I'm not doing any inserting, just selecting.

Before I paste in a bunch of code, shall I just show you the columns pertaining to my queries? Or do you want to see every column for each table in case there's something I'm missing?

Thank you for your help! I apologize none of this is obvious to me.

LoriM
Starting Member

29 Posts

Posted - 2005-08-16 : 12:36:37

Boy do I feel like an idiot. Sorry, didn't realize you were trying to reCREATE what I'm seeing.

Here are my tables and some data:
CREATE TABLE [dbo].[clients] ([uid] [int] NOT NULL )

INSERT INTO [SC].[dbo].[clients]([uid]) VALUES(5)
INSERT INTO [SC].[dbo].[clients]([uid]) VALUES(27)

use sc
CREATE TABLE [dbo].[da_answer] ([uid] [int] NOT NULL ,
[client_id] [int] NOT NULL ,
[question_id] [int] NOT NULL )

INSERT INTO [SC].[dbo].[da_answer]([uid], [client_id], [question_id]) VALUES(1,5, 23)
INSERT INTO [SC].[dbo].[da_answer]([uid], [client_id], [question_id]) VALUES(4, 27,23)
INSERT INTO [SC].[dbo].[da_answer]([uid], [client_id], [question_id]) VALUES(7,27,488)
INSERT INTO [SC].[dbo].[da_answer]([uid], [client_id], [question_id]) VALUES(8,27,973)
INSERT INTO [SC].[dbo].[da_answer]([uid], [client_id], [question_id]) VALUES(4, 27,1075)

use sc
CREATE TABLE [dbo].[da_question] ([uid] [int] NOT NULL ,
[descrip] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL )

INSERT INTO [SC].[dbo].[da_question]([uid], [descrip]) VALUES(23,'children in household')
INSERT INTO [SC].[dbo].[da_question]([uid], [descrip]) VALUES(142,'Last 30 day income')
INSERT INTO [SC].[dbo].[da_question]([uid], [descrip]) VALUES(488,'Military Information')
INSERT INTO [SC].[dbo].[da_question]([uid], [descrip]) VALUES(1075,'Source of Income')
INSERT INTO [SC].[dbo].[da_question]([uid], [descrip]) VALUES(973,'Military Branches')
INSERT INTO [SC].[dbo].[da_question]([uid], [descrip]) VALUES(963,'Did You Serve in a War zone?')

-----142----------------5--------------no-----------Last 30 day income--------mary

-----142----------------27--------------no-----------Last 30 day income------Bob
------963----------------27--------------no-----------Did You Serve in a War zone?------Bob

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-08-16 : 12:48:07

Given this sample data, how do we know which questions to ask the user? Is there another table that stores this information?

Tara

LoriM
Starting Member

29 Posts

Posted - 2005-08-16 : 13:00:12

Thanks, Tara.

I've created a view that shows the questions pertaining to HUD req's.
CREATE VIEW dbo.Qs_for_HUD
AS
SELECT uid AS question_id, descrip
FROM dbo.da_question
WHERE (uid = 23) OR

(uid = 142) OR
(uid = 488) OR
(uid = 1075) OR
(uid = 973) OR
(uid = 963)
GO

Here is the other view that I use for the query referenced in my 3rd post:
CREATE VIEW dbo.Answers_Clients
AS
SELECT TOP 100 PERCENT dbo.da_answer.question_id, dbo.da_answer.client_id
FROM dbo.da_answer INNER JOIN
dbo.clients ON dbo.da_answer.client_id = dbo.clients.uid
ORDER BY dbo.da_answer.question_id, dbo.clients.uid

GO

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-08-16 : 13:08:00

I'm just not seeing how this works. I would think that you would have a table that stores which questions to ask when certain conditions are met, but it doesn't seem that you have this.

Tara

LoriM
Starting Member

29 Posts

Posted - 2005-08-16 : 14:12:06

Sorry for the delay, I lost everything and had to retype.

Hmmmm... I'll try to explain more...

Lets say there are 100 questions a user must ask a client. 10 of those are required for HUD. And the user must also ask the other 90 questions.

Of those 10 questions some people will really only need to answer 7 of them. I would like to filter out the other 3 questions based on a NO answer. For instance Military types of questions. If they haven't served in the military, then the other military questions are irrelevant.

This is the result from the query I've mentioned in another post (not what I want to see). It shows me the questions that haven't been answered:

QuestionID--ClientID--[wasAnswered?]--Descrip
142----5----no----Last 30 day income
488----5----no----Military Information
963----5----no----Did You Serve in a War zone?
973----5----no----Military Branches
1075----5----no----Source of Income
142----27----no----Last 30 day income
963----27----no----Did You Serve in a War zone?

So- for client 5, they answered 'NO' to question 488. How can I filter out questions 963 and 973 based on the 'NO' for 488? (BTW, if they answered YES, it wouldn't appear in this queries results because I'm only looking for NO's.)

Are you thinking I should be putting this list of special questions into their own table? The thing is- I have a few other questions that will need to be filtered out based on a NO answer. That's why I was hoping I could use a Select Case.

For instance the question 'is your child enrolled in school?' If that's a yes, then they should answer the question 'What type of school?' If the answer is NO, then I don't want to see either question in my result set.

Feel free to let me know I'm going about the whole thing the wrong way!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources