Click here to Skip to main content
Rate this: bad
good
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,
CASE
    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
HSI
Direct TV
Uverse Green
iPhone
iPad
LTE
Zodiac 3G
Non-Phone Plaque Color Dot Program
ISIS Trial
Wireless Home Phone
Lifeline
Digital Life
Dummy Phone
Priority 1
N/A
1
2
3
4
5
6
7
9
Posted 4-Apr-13 9:17am
JasonMacD1.6K
Edited 4-Apr-13 11:25am
v2
Comments
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
good
Please Sign up or sign in to vote.

Solution 1

Try something like this:
   SELECT  Question,
           Response AS 'RawResponse'
           ResponseID,
           CASE  
              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.
  Permalink  
v2
Comments
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
good
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
END
FROM view_PromonetProfileQuestionResponses 
WHERE promotionid = @promotionid
  Permalink  
v2

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

  Print Answers RSS
0 OriginalGriff 350
1 Jochen Arndt 190
2 Richard MacCutchan 135
3 Sergey Alexandrovich Kryukov 130
4 DamithSL 95
0 OriginalGriff 6,045
1 DamithSL 4,601
2 Maciej Los 4,087
3 Kornfeld Eliyahu Peter 3,480
4 Sergey Alexandrovich Kryukov 3,310


Advertise | Privacy | Mobile
Web02 | 2.8.141220.1 | Last Updated 4 Apr 2013
Copyright © CodeProject, 1999-2014
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