Solved

Does the analytical function "RANK" work in Oracle Forms

Posted on 2008-06-16
5
1,527 Views
Last Modified: 2013-12-19
I have the following sql declared in a cursor.  The SQL runs fine in toad if I populate the variables but it won't compile in forms...

  CURSOR cAssetData (pRank IN NUMBER) IS
  SELECT asset_class_code,
         product_id,
         payment_method_code,
         model_code,
         manufacturer_code,
         year_of_manufacture,
         serial_number,
         location_code,
         asset_location_notes,
         asset_status_code
  FROM   (
         SELECT nvl(a.asset_class_code,'METER') asset_class_code,
            a.product_id,
            a.payment_method_code,
            a.model_code,
            a.manufacturer_code,
            a.year_of_manufacture,
            a.serial_number,
            lpad(a.location_code,2,0) location_code,
            a.asset_location_notes,
            nvl(a.asset_status_code,'LI') asset_status_code,
            RANK() OVER (ORDER BY a.effective_from_date DESC) ASSET_RANK
         FROM vw_meter_point mp, asset a
         WHERE mp.meter_point_reference = vMeterPointReference
         AND   mp.supply_start_date = (SELECT MAX(supply_start_date)
                                       FROM   VW_METER_POINT mp2
                                       WHERE  mp2.meter_point_reference = vMeterPointReference
                                       )
         AND a.meter_point_id = mp.meter_point_id
         )
  WHERE ASSET_RANK = pRank;


The compile error I get is...

Encountered the symbol "(" when expecting one of the following:
, from

Any help would be much appreciated.
0
Comment
Question by:bryanhale
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
5 Comments
 
LVL 74

Expert Comment

by:sdstuber
ID: 21795197
the forms and reports pl/sql engines are less robust than those in the database.

so, as a workaround...
wrap your query in a function that returns a pipelined collection or
a procedure or function that returns a reference cursor and compile your code on the server.
0
 
LVL 7

Expert Comment

by:Dr_Billy
ID: 21795530
Try this and see how it works
cAssetData (pRank IN NUMBER) IS
SELECT asset_class_code, 
       product_id, 
	   payment_method_code, 
	   model_code,
       manufacturer_code, 
	   year_of_manufacture, 
	   serial_number, 
	   location_code,
       asset_location_notes, 
	   asset_status_code
  FROM 
  (SELECT NVL (a.asset_class_code, 'METER') asset_class_code,
         a.product_id,
		 a.payment_method_code, 
		 a.model_code,
         a.manufacturer_code, 
		 a.year_of_manufacture, 
		 a.serial_number,
         LPAD (a.location_code, 2, 0) location_code,
         a.asset_location_notes,
         NVL (a.asset_status_code, 'LI') asset_status_code,
         RANK () OVER (ORDER BY a.effective_from_date DESC) asset_rank
    FROM asset a ) aa  , 
   (Select *
      from vw_meter_point	
    WHERE mp.meter_point_reference = vmeterpointreference
      ) bb
    WHERE aa.aset_rank = pRank
      AND aa.meter_point_id = bb.meter_point_id 
      AND bb.supply_start_date =
                     (SELECT MAX (supply_start_date)
                        FROM vw_meter_point mp2
                       WHERE mp2.meter_point_reference = vmeterpointreference);
	  
  	  

Open in new window

0
 

Author Comment

by:bryanhale
ID: 21796719
Unfortunately I do not really want to compile code on the server if I can avoid it.  I have many forms in this app and none of them do that so it will be inconsistent with the code.  I am sure there is a way of doing this.

I have tried out the code in the second response, I have fixed a couple of typos but am still getting an error as aa.meter_point_id doesn't exist, i.e. its not part of the aa select.  I wasn't sure exactly what you were getting at with this SQL so I will leave you to explain if thats okay?

Cheers for your responses anyway.

Bryan
0
 
LVL 7

Expert Comment

by:Dr_Billy
ID: 21799348
What i wanted you to do is to make the joins out of the From part instead of doing it inside the main From part , so you would have an a virtual cursor aa and another bb and you join them out of the FROM part of the main query ..... hope that explains what your question !!
0
 

Accepted Solution

by:
bryanhale earned 0 total points
ID: 21803156
Unfortunately this doesn't work as it is ranking every single asset, not just the assets for that meter point.  Also the SQL takes quite a while to run, probably because there are over 3 million asset records.

In the end I have just selected all the assets for the meter point and ordered by date desc.  I have then used a variable to rank them in the cursor.  i.e. I know the first loop of the cursor I am looking at the first record (i.e. rank 1) and the second loop the second record etc.  I just then add one to the variable each time.  If its rank 1 I am after I just exit the cursor after the first loop.

Thanks for all your help, very annoying that it doesn't look like you can use rank in forms.

0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article started out as an Experts-Exchange question, which then grew into a quick tip to go along with an IOUG presentation for the Collaborate confernce and then later grew again into a full blown article with expanded functionality and legacy…
Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Suggested Courses

622 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question