SQL: GROUP BY .. on calculated field

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