empty fields in a query
Home > IT Answers > Database > empty fields in a query
Bananahead
60 pts.
0
Q:
empty fields in a query
CASE statement, NULL, SQL Query
I have a column with answers that people responded to different questions about themseleves. I have another column with the question id that the answer corresponds to. i want to display the answers in separate columns based on which question it corresponds to. Each column should be filled with the answers to the specific question. I used a case statement to divide the answer column into different columns based on their question id. The problem is that i don't know what to put after the else. if i put Null, then i have a lot of empty fields that say null. I want that the fields shouldn't show up all together.

Here is my query:

SELECT DISTINCT
TOP (100) PERCENT dbo.ProfileAnswers.u_username AS Username, CASE ProfileAnswers.pq_id WHEN 101 THEN ProfileAnswers.pa_value ELSE NULL
END AS HebrewName, CASE ProfileAnswers.pq_id WHEN 42 THEN ProfileAnswers.pa_value ELSE NULL END AS 'Learing/Working',
CASE ProfileAnswers.pq_id WHEN 70 THEN ProfileAnswers.pa_value ELSE NULL END AS 'Previously Married',
CASE ProfileAnswers.pq_id WHEN 35 THEN ProfileAnswers.pa_value ELSE NULL END AS Type, dbo.ProfileAnswers.pa_approved AS Approved,
DATEDIFF(Year, dbo.Users.u_birthdate, GETDATE()) AS Age
FROM dbo.ProfileAnswers INNER JOIN
dbo.Users ON dbo.ProfileAnswers.u_username = dbo.Users.u_username
WHERE (dbo.Users.u_gender = 2)
ASKED: May 5 2009  4:20 PM GMT
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _
RELATED QUESTIONS
0
Juju
335 pts.
0
A:
 RATE THIS ANSWER
-1
Click to Vote:
  •   0
  •  -1
  • AddThis Social Bookmark Button
Bananahead - I would begin each field's select statment with "is not null" then as you have it. Naturally delete the final "else"

Good luck!

JuJu

On second look, I think just deleting "else Null" will give you the results you want. The "else Null" is telling the program to enter the word "Null" when the other conditions are not met. Let me know if this helps.

JuJu
Last Answered: May 8 2009  5:29 PM GMT by Juju   335 pts.
  •   
0
0
RSS - Discussions Help
Discuss This Answer:
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _



_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _

Msi77   800 pts.  |   May 6 2009  5:12PM GMT

What result set do you expect to get?

 

Bananahead   60 pts.  |   May 6 2009  7:13PM GMT

In the column that asks for name, i want name only, age i want age only. Here i get name and whefe there is information filled into the other fields i get NULL. i want to get rid of that all together. yoiu shouldn’t know there was something in the database there.

 

Kccrosser   1850 pts.  |   May 7 2009  4:36PM GMT

I am not sure I understand your objectives, but is it as simple as replacing the “NULL” with an empty string? Like:

CASE ProfileAnswers.pq_id WHEN 70 THEN ProfileAnswers.pa_value ELSE ” END AS ‘Previously Married’,
CASE ProfileAnswers.pq_id WHEN 35 THEN ProfileAnswers.pa_value ELSE ” END AS Type,

Or use a string with a blank, to avoid the empty string being interpreted as null:

CASE ProfileAnswers.pq_id WHEN 70 THEN ProfileAnswers.pa_value ELSE ‘ ‘ END AS ‘Previously Married’,
CASE ProfileAnswers.pq_id WHEN 35 THEN ProfileAnswers.pa_value ELSE ‘ ‘ END AS Type,

 

Bananahead   60 pts.  |   May 7 2009  6:44PM GMT

that wont help. the database is not empty. its showing null for all the answers that i don’t want to see in this colum. for example, lets say i want to show the age for each person in my database. it will show the name, the age and blank spaces for each of the other questions that i don’t want to show.

 

Bananahead   60 pts.  |   May 7 2009  9:07PM GMT

I tried IS NOT NULL but that doesn’t work because the database is not null. I told it to put in Null or an empty string instead of what is in that field in the database. I need that instead of showing these things, the field should not show up at all. Since all the answers are in the same column, i am having a hard time splitting this one column into a couple of different columns.

 

Darryn   370 pts.  |   May 8 2009  3:29PM GMT

This is not ideal, but it should work:

select username,
max(HebrewName) HebrewName,
max(Learning_Working) Learning_Working,
max(Previously_Married) Previously_Married,
max(Type) Type
max(Approved) Approved
from
(
SELECT DISTINCT
TOP (100) PERCENT dbo.ProfileAnswers.u_username AS Username, CASE ProfileAnswers.pq_id WHEN 101 THEN ProfileAnswers.pa_value ELSE NULL END AS HebrewName,
CASE ProfileAnswers.pq_id WHEN 42 THEN ProfileAnswers.pa_value ELSE NULL END AS ‘Learing_Working’,
CASE ProfileAnswers.pq_id WHEN 70 THEN ProfileAnswers.pa_value ELSE NULL END AS ‘Previously_Married’,
CASE ProfileAnswers.pq_id WHEN 35 THEN ProfileAnswers.pa_value ELSE NULL END AS Type, dbo.ProfileAnswers.pa_approved AS Approved,
DATEDIFF(Year, <a href="http://dbo.Us" title="http://dbo. " target="_blank">dbo.Us</a>ers.u_birthdate, GETDATE()) AS Age
FROM dbo.ProfileAnswers INNER JOIN
 <a href="http://dbo.Us" title="http://dbo.
" target="_blank">dbo.Us</a>ers ON dbo.ProfileAnswers.u_username = <a href="http://dbo.Us" title="http://dbo. " target="_blank">dbo.Us</a>ers.u_username
WHERE  <a href="http://dbo.Us" title="http://dbo.(" target="_blank">dbo.Us</a>ers.u_gender = 2) )
group by username

 
0