Home » SQL & PL/SQL » SQL & PL/SQL » Using CASE within a CASE within a CASE - does this make sense? (Oracle 10.1.2g)
Using CASE within a CASE within a CASE - does this make sense? [message #319704] Mon, 12 May 2008 13:58 Go to next message
gnober
Messages: 15
Registered: May 2008
Location: NY
Junior Member
Hello! I have been playing around with DECODE and CASE statements and unfortunately i seem to have lost my mind 'mastering' them both.

Kindly see the ff codes:

DECODE (argument1,
	      ‘value 1’, ‘value 1’,
	      DECODE ( argument2, 
		       ‘value 2’, ‘value 2’,
		       DECODE ( argument3,
			        ‘value3’, ‘value3’,
				‘def_value’)


IF argument1 = value1 THEN
	'VALUE1'
ELSE
	IF argument2 = value2 THEN 
		'VALUE2'
	ELSE
		IF argument3 = argument3 THEN
			'Value3'
		ELSE
			'def_Value'
		END IF;
	END IF;
END IF;


CASE
WHEN argument1 = value1 THEN 'value1'
ELSE
	CASE
	WHEN argument2 = value2 THEN 'value2'
	ELSE
		CASE 
		WHEN argument3 = argument3 THEN 'value3'
		ELSE 'def_value'
		END
	END
END


So far so good. My question is, does it really make sense to use CASE expression with a CASE within a CASE (so on and so forth).

How is the last code different from if indeed it is diff?:

CASE
WHEN argument = value1 THEN 'value1'
WHEN Argument2 = value2  THEN 'value 2'
WHEN argument3 = value3 then 'value 3'
ELSE 'def_value'
END
Re: Using CASE within a CASE within a CASE - does this make sense? [message #319705 is a reply to message #319704] Mon, 12 May 2008 14:01 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
No difference.

Regards
Michel

Re: Using CASE within a CASE within a CASE - does this make sense? [message #319709 is a reply to message #319705] Mon, 12 May 2008 14:28 Go to previous messageGo to next message
gnober
Messages: 15
Registered: May 2008
Location: NY
Junior Member
I can understand the use of DECODE within a DECODE (i.e. suppose there are 50 rows of varchar2 Column and we want to iterate through all the rows (with different criteria) and output specific results based on said rows).

Example:

Bottled_products column is of SodaJuiceEnergy composition

DECODE ( substr(bottled_products, 1, 10,
		‘coke’, ‘coke’,
		‘pepsi’, ‘pepsi’,
		DECODE (  substr(bottled_products, 11, 20),
				‘snapple’, ‘snapple’,
				DECODE ( substr(bottled_products, 21, 30),
					‘gatorade, ‘gatorade’,
					‘powerade’)


In this case we are going progressively deeper in the DECODE node as each DECODES are processed (i.e Give me the result of the result).

However, as CASE operator can be conditional to begin with, couldnt we simply say:

CASE 
WHEN substr(bottled_products, 1, 10) = ‘coke’ THEN ‘coke
WHEN substr(bottled_products, 1, 10) = ‘pepsi’ THEN ‘pepsi’
WHEN substr(bottled_products, 11, 20) = ‘snapple’ THEN ‘snapple’
WHEN substr(bottled_products, 21, 30) = ‘gatorade’ THEN ‘gatorade’
ELSE powerade
END


I'm almost sure this is correct but the more i analyze it the more i get confused. Shocked

@Michel: Thanks for the response!!! I appreciate it.
Re: Using CASE within a CASE within a CASE - does this make sense? [message #319710 is a reply to message #319709] Mon, 12 May 2008 14:36 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Yes, CASE is easier to read and to use.
DECODE is there only because it was the only one that existed till version 8i (in pure SQL)/9i (in SQL embedded in PL/SQL).

Regards
Michel
Re: Using CASE within a CASE within a CASE - does this make sense? [message #319727 is a reply to message #319710] Mon, 12 May 2008 16:21 Go to previous message
gnober
Messages: 15
Registered: May 2008
Location: NY
Junior Member
Hello,

I tried using some test cases and it is indeed the same.

CASE rules, especially when integers are involved. =)

Thanks! Razz
Previous Topic: Joining two Select Statements
Next Topic: Do we need to raise exception while using ref cursor?
Goto Forum:
  


Current Time: Sat Dec 10 22:22:24 CST 2016

Total time taken to generate the page: 0.16712 seconds