Click here to Skip to main content
11,709,844 members (49,324 online)
Rate this: bad
Please Sign up or sign in to vote.
What i'm trying to accomplish here is if response = 'N/A' then set response 1-9 to 'N/A' basically to hide those rows and display 'N/A'. The other scenario is if response = 'RevRank' then set response 1-9 to diplay response. If neither of these conditions are met then simply show responses. I'm not having any luck, anyone have any ideas on how to accomplish this?

SELECT Question, ResponseID,
    WHEN response IN ('N/A','1','2','3','4','5','6','7','8','9') THEN 'N/A'
    WHEN response IN ('RevRank','1','2','3','4','5','6','7','8','9') THEN response
    ELSE response
END AS response,
FROM view_PromonetProfileQuestionResponses 
WHERE promotionid = @promotionid

result set partial:
025 - Miscellaneous - 3666
13 State
Direct TV
Uverse Green
Zodiac 3G
Non-Phone Plaque Color Dot Program
ISIS Trial
Wireless Home Phone
Digital Life
Dummy Phone
Priority 1
Posted 4-Apr-13 8:17am
Edited 4-Apr-13 10:25am
Maciej Los at 4-Apr-13 15:04pm
If i understand you well, you want to replace data stored in column response: values (1 to 9) to 'N/A' and 'RevRank' to (1 to 9), but on what condition???
Please, show me an example data, because till now, i'm a bit confused ;(
JasonMacD at 4-Apr-13 16:27pm
So I added a result showing where N/A is selected and 1-9 is also, this would be where I want 1-9 to to be cloaked with N/A
JasonMacD at 4-Apr-13 15:40pm
Yes basically it is dropdown lists and Im capturing the data. If RevRanks is selected I won't to display the 1-9 if 'N/A' is selected I want everything to show as 'N/A'. Keep in mind the response column is varchar not int because other reponses are words.
Maciej Los at 4-Apr-13 16:15pm
Example data, please. Use "Improve question" widget to update your question (right-bottom corner of question).
To reply my comment, use "Reply" widget.
Rate this: bad
Please Sign up or sign in to vote.

Solution 1

Try something like this:
SELECT  Question,
        Response AS 'RawResponse'
           WHEN response = 'N/A' THEN 'N/A'
           WHEN response < 10 AND response > 0 THEN 'N/A'
           ELSE response
        END AS Response
  FROM  view_PromonetProfileQuestionResponses
 WHERE  promotionid = @promotionid

You had two columns returning as "Response", so I updated the first to "RawResponse".
As well, I just redid the case a little differently, but I would expect that yours should work as is as well.
JasonMacD at 4-Apr-13 15:38pm
Thanks for the fast response (no pun intended), I didn't mean to put the response in there twice that was a typo. The reponse column is a varchar so the greater than less than is out of the question in this case.
Marcus Kramer at 4-Apr-13 15:49pm
If you just keep your WHEN clause you're probably going to be OK with a single instance of the response column name. Cheers.
Rate this: bad
Please Sign up or sign in to vote.

Solution 2

Try this:
SELECT Question, ResponseID, NewResponce =
CASE response
    WHEN '1' THEN 'N/A'
    WHEN '2' THEN 'N/A'
    WHEN '3' THEN 'N/A'
    WHEN '4' THEN 'N/A'
    WHEN '5' THEN 'N/A'
    WHEN '6' THEN 'N/A'
    WHEN '7' THEN 'N/A'
    WHEN '8' THEN 'N/A'
    WHEN '9' THEN 'N/A'
    ELSE response
FROM view_PromonetProfileQuestionResponses 
WHERE promotionid = @promotionid

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
0 Sergey Alexandrovich Kryukov 514
1 OriginalGriff 250
2 Peter Leow 229
3 Maciej Los 185
4 Mika Wendelius 175
0 OriginalGriff 9,428
1 Sergey Alexandrovich Kryukov 9,037
2 CPallini 5,189
3 Maciej Los 4,991
4 Mika Wendelius 3,856

Advertise | Privacy | Mobile
Web02 | 2.8.150819.1 | Last Updated 4 Apr 2013
Copyright © CodeProject, 1999-2015
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100