Re: Help: SELECT FROM (subquery) ? Is it possible?

On Thu, 20 Jan 2000 12:27:06 +0100, in comp.databases.oracle.tools you wrote:

SORRY, made a mistake in my previous post, concerning the ORDER BY in the subquery. This is indeed not possible, but why would you need it ? You only need to order the data you actually retrieve from the query, and not that that you cannot see ! If you do the rest like I explained below, the subquery will work ...

|| I have a problem concerning subqueries and ORDER BY. I have a query
|| which select a number of records and then does some ordering of them (using
|| CONNECT BY). However, I want to ORDER the table the SELECT operates on, like
|| this:
||
|| SELECT *
|| FROM (SELECT * FROM sometable ORDER BY something)
|| WHERE ...
|| < connect by stuff >
 

I don't think you may use a "select *" in this kind of subquery and you have to give a name to this subquery. The correct way would be :

SELECT *
FROM (

		SELECT s.field_a SFA, s.field_b SFB
		FROM sometable s
		ORDER BY s.field_b    <--- this is not possible, SORRY !!!!
	) TempTable, 
	OtherTable o 	-- let's add another table to make things clear
WHERE
	-- we join both of them :
	TempTable.SFA = o.some_field_c and
	-- add required query criteria ...

ORDER BY TempTable.SFB, o.nr

SFA and SFB here are aliases to which the elements of TempTable will be referred, e.g. like this : and o.some_field_d in (TempTable.SFA, TempTable.SFB)

Kind regards,
Erwin Received on Fri Jan 21 2000 - 17:59:04 CET