Re: Top N without inline

  • From: Stefan Knecht <knecht.stefan@xxxxxxxxx>
  • To: Dave Pacia <davepacia@xxxxxxxxx>
  • Date: Wed, 23 Jun 2010 18:46:03 +0200

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
>>
>>
>
>

Other related posts: