Top N without inline

From: Dave Pacia <davepacia_at_yahoo.com>
Date: Wed, 23 Jun 2010 07:57:46 -0700 (PDT)
Message-ID: <318514.90388.qm_at_web30606.mail.mud.yahoo.com>

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       

--
http://www.freelists.org/webpage/oracle-l

Received on Wed Jun 23 2010 - 09:57:46 CDT