Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> SQL: GROUP BY .. on calculated field

SQL: GROUP BY .. on calculated field

From: Vadim Grepan <_RMV_IT_kezal_at_mail.ru>
Date: Wed, 24 Dec 2003 13:09:46 +0300
Message-ID: <bsbokn$mb2$1@n6.co.ru>


Hello All!

Unfortunatly, Oracle cannot GROUP BY .. by alias, so there are two ways to group calculated field:

--

(1) using subselect:

	SELECT v.aa, v.bb, v.calc_fld
	FROM (SELECT aa, bb,
			(CASE WHEN substr(cc,1,2)='10' THEN 'XXX'
			 	WHEN substr(cc,1,2)='20' THEN 'YYY'
				WHEN substr(cc,1,2)='30' THEN 'ZZZ'
		  END) calc_fld
		) v
	GROUP BY v.aa, v.bb, v.calc_fld;


--

(2) double calculate fieled value:

	SELECT aa, bb,
		(CASE WHEN substr(cc,1,2)='10' THEN 'XXX'
		 	WHEN substr(cc,1,2)='20' THEN 'YYY'
			WHEN substr(cc,1,2)='30' THEN 'ZZZ'
		  END) calc_fld
		)
	GROUP BY aa, bb,
		CASE WHEN substr(cc,1,2)='10' THEN 'XXX'
		 	WHEN substr(cc,1,2)='20' THEN 'YYY'
			WHEN substr(cc,1,2)='30' THEN 'ZZZ'
		

Myself I prefer way (1). Is there any benefit for way (2) ? I've tried similar queries and cound't find any difference in execution plan

Rgds, Vadim
--

Moscow, Russia Received on Wed Dec 24 2003 - 04:09:46 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US