You're welcome. It's called subquery factoring, and it's hidden somewhere in the depths of the 100's of pages the SELECT reference for Oracle spans :-) Good luck Stefan ========================= Stefan P Knecht CEO & Founder s@xxxxxxxx 10046 Consulting GmbH Schwarzackerstrasse 29 CH-8304 Wallisellen Switzerland Phone +41-(0)8400-10046 Cell +41 (0) 79 571 36 27 info@xxxxxxxx http://www.10046.ch ========================= On Wed, Jun 23, 2010 at 6:27 PM, Dave Pacia <davepacia@xxxxxxxxx> wrote: > Thanks Stefan. That is syntax I had not seen before--didn't know you could > do that. I've given it to the developer - let's see what he says. > > Dave Pacia > > ------------------------------ > *From:* Stefan Knecht <knecht.stefan@xxxxxxxxx> > *To:* davepacia@xxxxxxxxx > *Sent:* Wed, June 23, 2010 12:05:50 PM > *Subject:* Re: Top N without inline > > This might do the trick: > > WITH myview as > (SELECT pal_attained_mtd, DENSE_RANK () OVER (ORDER BY > pal_attained_mtd DESC) AS RANKINGS, bdm_name > FROM bdm_stats_ts > GROUP BY bdm_name, pal_attained_mtd) > SELECT pal_attained_mtd, RANKINGS , bdm_name from myview > WHERE RANKINGS < 6; > > Not sure if this fits your requirement, but it doesn't use an "inline-view" > as per its definition :) > > Stefan > > > ========================= > > Stefan P Knecht > CEO & Founder > s@xxxxxxxx > > 10046 Consulting GmbH > Schwarzackerstrasse 29 > CH-8304 Wallisellen > Switzerland > > Phone +41-(0)8400-10046 > Cell +41 (0) 79 571 36 27 > info@xxxxxxxx > http://www.10046.ch > > ========================= > > > On Wed, Jun 23, 2010 at 4:57 PM, Dave Pacia <davepacia@xxxxxxxxx> wrote: > >> Hello List, >> A friend (report developer) has asked for help, and I have not been able >> to give him a satisfactory answer. I'm hoping someone on Oracle-L can help >> out. Basically, he wants to run a Top N query that does not use an inline >> (nested SQL statement). It's easier in Hyperion 11.3 to select the columns >> and where clause, and allow the tool to populate the FROM clause. It isn't >> an insoluble problem--we could either build a view or he can paste a SQL >> statement into Hyperion, but then any changes to the report spec or table >> design require that additional step. >> >> He's using a dense rank function. We can generate Top N queries of the >> form: >> >> SELECT * FROM >> (SELECT pal_attained_mtd, DENSE_RANK () OVER (ORDER BY >> pal_attained_mtd DESC) AS RANKINGS, bdm_name >> FROM bdm_stats_ts >> GROUP BY bdm_name, pal_attained_mtd) >> WHERE RANKINGS < 6; >> >> But it would be helpful if we could generate the SQL so that the >> limitation to the Top 5 was done in a unnested SQL statement. >> >> Here's the table structure. This is a simplified. The production table >> has columns for different kinds of targets and actual results: >> BDM_STATS_TS >> Name >> Null? Type >> ----------------------------------------------------------------------- >> -------- ------------------------------------------------- >> PAL_ATTAINED_MTD >> NOT NULL NUMBER(6,2) >> BDM_NAME >> NOT NULL VARCHAR2(150) >> >> The database is Oracle 10.2.0 running on Solaris 8. >> >> Thanks in advance, >> Dave >> >> > >