Home » SQL & PL/SQL » SQL & PL/SQL » DECODE or CASE help
DECODE or CASE help [message #226786] Mon, 26 March 2007 09:22 Go to next message
staann56
Messages: 136
Registered: May 2006
Location: atlanta
Senior Member
I have a field that gets populated based on what the end user checks (up to six check boxes). These check boxes are labeled as follows (what the end user sees):


What the end user sees
1)Stage II
2)Partial-thickness
3)Like an Abrasion
4)Blister
5)Superficial
6)Shallow Crater

What is stored in the table
1)Stgii
2)Pthick
3)Likeab
4)Blistr
5)Spfcl
6)Shcrtr

Any combination of these six options can be checked. I'd like to, either through a DECODE, CASE or whatever will work, translate what is in the result set to what the user is used to seeing....like so

If the field contains:
StgiiPthickShcrtr
I'd like to report:
Stage II, Partial-Thickness, Shallow Crater


Thanks for looking

Stan
Re: DECODE or CASE help [message #226788 is a reply to message #226786] Mon, 26 March 2007 09:25 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
>I'd like to, either through a DECODE, CASE or whatever will work, translate what is in the result set to what the user is used to seeing
So proceed to do so.
What is stopping you?
Do you expect us (TINU) to do your homework for you?
Re: DECODE or CASE help [message #226789 is a reply to message #226786] Mon, 26 March 2007 09:25 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

If there is nothing to separate each value in your field, I think you have to do it programatically.

Regards
Michel
Re: DECODE or CASE help [message #226804 is a reply to message #226786] Mon, 26 March 2007 10:32 Go to previous messageGo to next message
staann56
Messages: 136
Registered: May 2006
Location: atlanta
Senior Member
anacedent...you really shouldn't assume I'm asking you to do my homework...you're making an ass out of yourself! With six different options that can be checked, there could be 720 permutations/combinations to cover in a DECODE or CASE. I'm merely wanting clarification on the possibility of this working with a DECODE or CASE (without having to type in 720 combinations) or any method that would accomplish the desired result. If you're not going to contribute anything meaningful or productive, then keep your remarks to yourself and don't respond to my posts!

[Updated on: Mon, 26 March 2007 10:44]

Report message to a moderator

Re: DECODE or CASE help [message #226835 is a reply to message #226786] Mon, 26 March 2007 14:02 Go to previous messageGo to next message
Ericle
Messages: 44
Registered: April 2006
Location: United States of America ...
Member

Are the results of whether or not they checked the boxes stored in the table? In other words, is there a checkbox_checked column in the table? If so, you could create a cursor with a loop and only bring in the records where checkbox_checked = 'Y'. You could then populate a variable with the result that the user sees as each record is pulled in. So, if the first record that comes in is Stgii then you'd have variable = variable||'Stage II'. As each successive record comes in you'd concatenate the value to the variable that you would display for the end user.
Re: DECODE or CASE help [message #226851 is a reply to message #226786] Mon, 26 March 2007 15:05 Go to previous messageGo to next message
Ericle
Messages: 44
Registered: April 2006
Location: United States of America ...
Member

If you only have the option of translating the result set of 'StgiiPthickShcrtr' directly to 'Stage II, Partial-Thickness, Shallow Crater' for the end user, then you could have code that looks like this:

select case when instr(:field,'Stgii') <> 0 then 'Stage II' end||
       case when instr(:field,'Pthick') <> 0 and instr(:field,'Stgii') <> 0 then ', Partial-thickness' 
	        when instr(:field,'Pthick') <> 0 then 'Partial-thickness' end||
	   case when instr(:field,'Likeab') <> 0 and (instr(:field,'Stgii') <> 0 or instr(:field,'Pthick') <> 0) then ', Like an Abrasion'
	        when instr(:field,'Likeab') <> 0 then 'Like an Abrasion' end||
	   case when instr(:field,'Blistr') <> 0 and (instr(:field,'Stgii') <> 0 or instr(:field,'Pthick') <> 0 or 
	             instr(:field,'Likeab') <> 0) then ', Blister'
			when instr(:field,'Blistr') <> 0 then 'Blister' end||
	   case when instr(:field,'Spfcl') <> 0 and (instr(:field,'Stgii') <> 0 or instr(:field,'Pthick') <> 0 or 
	             instr(:field,'Likeab') <> 0 or instr(:field,'Blistr') <> 0) then ', Superficial'
			when instr(:field,'Spfcl') <> 0 then 'Superficial' end||
	   case when instr(:field,'Shcrtr') <> 0 and (instr(:field,'Stgii') <> 0 or instr(:field,'Pthick') <> 0 or 
	             instr(:field,'Likeab') <> 0 or instr(:field,'Blistr') <> 0 or instr(:field,'Spfcl') <> 0) then ', Shallow Crater'
        	when instr(:field,'Shcrtr') <> 0 then 'Shallow Crater' end end_user_display
  from dual


I entered this code in Toad and put in 'StgiiPthickShcrtr' for the :field parameter and got the desired results.
Re: DECODE or CASE help [message #226864 is a reply to message #226851] Mon, 26 March 2007 15:58 Go to previous messageGo to next message
staann56
Messages: 136
Registered: May 2006
Location: atlanta
Senior Member
Thanks guys for your input. I was making the CASE statement more difficult than necessary, believing I would have to create 720 permutations/combinations for it to work. I was able to get the desired result set with code similar to Ericle's suggestion.

Much appreciated,
Stan
Re: DECODE or CASE help [message #226959 is a reply to message #226864] Tue, 27 March 2007 00:32 Go to previous message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Trust me, you do NOT want to store it that way.
Store the separate values chosen. This will make your life and the life of programmers following you SOO much easier!
Previous Topic: oracle date formatting problemo
Next Topic: Unable to compile the whole user
Goto Forum:
  


Current Time: Sat Dec 10 11:14:23 CST 2016

Total time taken to generate the page: 0.05367 seconds