Solved

What is the equivalaent syntax for KEEP DENSE_RANK for Oracle 8174?

Posted on 2009-06-30
13
1,024 Views
Last Modified: 2013-12-19
Hello, I am able to execute the following query in Oracle 9i, but am unable to do so in Oracle 8174. I assume that Oracle 8 doesn't support the syntax I used. That said, what would be the Oracle 8 equivalent for the syntax below?

MAX(c.contractor ||' '||c.rigno ) KEEP (DENSE_RANK LAST ORDER BY c.syscreatedate)

Many thanks!
SELECT w.idwell, SUBSTR(b.afenumber, INSTR(b.afenumber,'10'),8) network,
       a.responsiblegrp2 z_eng,
       a.responsiblegrp1 z_sup,    
       MAX(c.contractor ||' '||c.rigno ) KEEP (DENSE_RANK LAST ORDER BY c.syscreatedate) as RIG ,             
       a.syscreatedate,
       a.wvtyp JOB\                         
FROM  wvwellheader w,
      wvjob a, 
      wvjobafe b, 
      wvjobrig c
                           
WHERE w.idwell = a.idwell
AND a.idrec = b.idrecparent
AND a.idrec = c.idrecparent
AND b.afenumber IS NOT NULL
AND c.dttmstart IS NOT NULL
AND a.DTTMSTART > to_date( '01/MAY/2008', 'DD/MON/YYYY')
AND w.idwell = 'B126D65D02754B0ABAA0394328315D64'
 
GROUP BY 
a.wvtyp, 
w.idwell, 
b.afenumber, 
a.responsiblegrp2, 
a.responsiblegrp1,
a.syscreatedate

Open in new window

0
Comment
Question by:mskitten
[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
  • 6
  • 5
  • 2
13 Comments
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 24749642
in oracle 8, there is no real alternative syntax for this.
you might try to use a subquery, but it will be 1) ugly and 2) likely inefficient.
0
 
LVL 11

Expert Comment

by:Andytw
ID: 24749741
Oracle 8i supports the analytic DENSE_RANK function.  However, it doesn't support the aggregate version (which uses the KEEP modifier).
Try the following, equivalent query, which should work in Oracle 8i:
SELECT sub.network,
       sub.z_eng,
       sub.z_sup,
       sub.syscreatedate,
       sub.JOB
       ( SELECT MAX(contractor ||' '||rigno )
         FROM wvjobrig
         WHERE syscreatedate = max_syscreatedate ) RIG
( SELECT w.idwell, SUBSTR(b.afenumber, INSTR(b.afenumber,'10'),8) network,
           a.responsiblegrp2 z_eng,
           a.responsiblegrp1 z_sup,    
           MAX(c.syscreatedate) as max_syscreatedate,             
           a.syscreatedate,
           a.wvtyp JOB       
    FROM  wvwellheader w,
          wvjob a, 
          wvjobafe b, 
          wvjobrig c
    WHERE w.idwell = a.idwell
    AND a.idrec = b.idrecparent
    AND a.idrec = c.idrecparent
    AND b.afenumber IS NOT NULL
    AND c.dttmstart IS NOT NULL
    AND a.DTTMSTART > to_date( '01/MAY/2008', 'DD/MON/YYYY')
    AND w.idwell = 'B126D65D02754B0ABAA0394328315D64'
    GROUP BY 
    a.wvtyp, 
    w.idwell, 
    b.afenumber, 
    a.responsiblegrp2, 
    a.responsiblegrp1,
    a.syscreatedate ) sub

Open in new window

0
 

Author Comment

by:mskitten
ID: 24749939
Actually, this is the query I want to use. I tried to apply what you posted to this one, but I get the following error:
ORA-00936: missing expression

SELECT sub.well,
       sub.alloc_centre, 
       sub.code, 
       sub.vendor,       
        (SELECT MAX(source_owner_percentage) 
         FROM gpas_source_owner
         WHERE source_owner_effectivedate = max_source_owner_effectivedate) WI,
       sub.volume
              
    (SELECT b.source_name well,
            a.alloc_centre_name alloc_centre,
            c.vendor_code code,
            e.vendor_name vendor,
            MAX(c.source_owner_effectivedate) as max_source_owner_effectivedate
            SUM(d.source_volume_gas) volume
            
     FROM gpas_alloc_centre a,
          gpas_source b,
          gpas_source_owner c,
          gpas_source_volume d,
          gpas_vendor e
     
 
     WHERE a.alloc_centre_id = b.alloc_centre_id
     AND   b.source_id = c.source_id
     AND   c.source_id = d.source_id
     AND   c.source_vendor_code = e.vendor_code
     AND   a.alloc_centre_name LIKE '%WEM%'
     AND   d.source_volume_date between trunc(sysdate)-30 and trunc(sysdate)
 
 
     GROUP BY source_name, alloc_centre_name, vendor_code, vendor_name) sub

Open in new window

0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 11

Expert Comment

by:Andytw
ID: 24753357
Sorry, that was my mistake, the outer query was missing a FROM clause.  Try this:
SELECT sub.well,
       sub.alloc_centre, 
       sub.code, 
       sub.vendor,       
       ( SELECT MAX(source_owner_percentage) 
         FROM gpas_source_owner
         WHERE source_owner_effectivedate = max_source_owner_effectivedate ) WI,
       sub.volume
FROM (SELECT b.source_name well,
             a.alloc_centre_name alloc_centre,
             c.vendor_code code,
             e.vendor_name vendor,
             MAX(c.source_owner_effectivedate) as max_source_owner_effectivedate
             SUM(d.source_volume_gas) volume
      FROM gpas_alloc_centre a,
          gpas_source b,
          gpas_source_owner c,
          gpas_source_volume d,
          gpas_vendor e
      WHERE a.alloc_centre_id = b.alloc_centre_id
       AND   b.source_id = c.source_id
       AND   c.source_id = d.source_id
       AND   c.source_vendor_code = e.vendor_code
       AND   a.alloc_centre_name LIKE '%WEM%'
       AND   d.source_volume_date between trunc(sysdate)-30 and trunc(sysdate)
     GROUP BY source_name, alloc_centre_name, vendor_code, vendor_name) sub

Open in new window

0
 
LVL 11

Expert Comment

by:Andytw
ID: 24753723
and I've just also realised that you are missing a ',' in the extra column you added.  Should be:
...
e.vendor_name vendor,
             MAX(c.source_owner_effectivedate) as max_source_owner_effectivedate,
             SUM(d.source_volume_gas) volume
FROM
...

Open in new window

0
 

Author Comment

by:mskitten
ID: 24764245
Hi Andytw. I was able to execute the code, but it gave me incorrect results. The attached screenshot shows the results.
The WI results should read (in the order of the Vendor in the screenshot):
0.283518000
0.032069000
0.026147000
0.084805000
0.013828000
The Volumes for each Vendor should be different as well, but they're all reading the same, probably because they all have the same effective date. I want to be able to pull the WI and Volumes for each Vendor associated with the Well. The Vendor's WI can have multiple effective dates, since the WI can change.
So I would like to see the most recent WI associated with the most recent effective date, for each vendor associated for each well. Perhaps my logic is incorrect in the script?



 

results.jpg
0
 
LVL 11

Expert Comment

by:Andytw
ID: 24774111
mskitten:  Firstly, apologies for not being able to respond back to your post sooner.  

There was a problem with the logic in the query which I first posted.  The max aggregate in the sub-select, wasn't using the same filtered result-set, and as you pointed out returned the effective date for all rows.

I've rewritten your query, using the analytics permitted in Oracle 8 (no KEEP modifier).  I've tested the approach on a different data-set, and it produces the correct results.  

Can you try it and see if it produces the results that you expect - I don't have access to an oracle 8 database.  Let me know how you get on.  
SELECT well,
       alloc_centre,
       code,
       vendor,
       MAX( DECODE(max_effective_date,source_owner_effectivedate,source_owner_percentage, NULL) ) WI,
       SUM( volume ) volume
FROM     ( SELECT b.source_name well,
             a.alloc_centre_name alloc_centre,
             c.vendor_code code,
             e.vendor_name vendor,
             MAX( c.source_owner_effectivedate ) OVER (PARTITION BY source_name, alloc_centre_name, vendor_code, vendor_name) max_effective_date,
             c.source_owner_percentage,
             d.source_volume_gas volume
            FROM gpas_alloc_centre a,
                 gpas_source b,
                 gpas_source_owner c,
                 gpas_source_volume d,
                 gpas_vendor e
            WHERE a.alloc_centre_id = b.alloc_centre_id
              AND   b.source_id = c.source_id
              AND   c.source_id = d.source_id
              AND   c.source_vendor_code = e.vendor_code
              AND   a.alloc_centre_name LIKE '%WEM%'
              AND   d.source_volume_date between trunc(sysdate)-30 and trunc(sysdate)  )    
GROUP BY well, alloc_centre, code, vendor;

Open in new window

employees-test.txt
0
 

Author Comment

by:mskitten
ID: 24786642
Hi Andytw, hope you had a great weekend.

I tried running the script, but source_owner_effectivedate in the DECODE line comes back as an invalid column name :(
0
 
LVL 11

Accepted Solution

by:
Andytw earned 500 total points
ID: 24787844
mskitten: Yes I had a great weekend thanks : - )
I forgot to include that column, c.source_owner_effectivedate, in the inline view (inner query).  Try ...
SELECT well,
       alloc_centre,
       code,
       vendor,
       MAX( DECODE(max_effective_date,source_owner_effectivedate,source_owner_percentage, NULL) ) WI,
       SUM( volume ) volume
FROM     ( SELECT b.source_name well,
             a.alloc_centre_name alloc_centre,
             c.vendor_code code,
             e.vendor_name vendor,
             c.source_owner_effectivedate,
             MAX( c.source_owner_effectivedate ) OVER (PARTITION BY source_name, alloc_centre_name, vendor_code, vendor_name) max_effective_date,
             c.source_owner_percentage,
             d.source_volume_gas volume
            FROM gpas_alloc_centre a,
                 gpas_source b,
                 gpas_source_owner c,
                 gpas_source_volume d,
                 gpas_vendor e
            WHERE a.alloc_centre_id = b.alloc_centre_id
              AND   b.source_id = c.source_id
              AND   c.source_id = d.source_id
              AND   c.source_vendor_code = e.vendor_code
              AND   a.alloc_centre_name LIKE '%WEM%'
              AND   d.source_volume_date between trunc(sysdate)-30 and trunc(sysdate)  )    
GROUP BY well, alloc_centre, code, vendor;

Open in new window

0
 

Author Comment

by:mskitten
ID: 24804032
Hi Andytw, I ran the script and so far so good. Once I've validated the results, I'll let you know.
Thanks for all your help this far!

Quick question, would you be able to give me a simplified explanation of what this line does?

MAX( DECODE(max_effective_date,source_owner_effectivedate,source_owner_percentage, NULL) )

Thanks :)
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 24804107
no points:
MAX( DECODE(max_effective_date,source_owner_effectivedate,source_owner_percentage, NULL) )

means:
return the max value of source_owner_percentage,
  considering only the rows where max_effective_date = source_owner_effectivedate

of course, per GROUP BY.
0
 
LVL 11

Expert Comment

by:Andytw
ID: 24805126
Thanks angelIII for providing the explanation.
mskitten:  in case you are not familiar with the Oracle DECODE function, it is equivalent to the following (arguably, more readable) expresssion:

Good luck, and let me know once you've had time to check the results.
MAX( CASE WHEN max_effective_date = source_owner_effectivedate THEN source_owner_percentage,
                    ELSE NULL END)

Open in new window

0
 

Author Closing Comment

by:mskitten
ID: 31598539
Works great. Thanks!
0

Featured Post

Space-Age Communications Transitions to DevOps

ViaSat, a global provider of satellite and wireless communications, securely connects businesses, governments, and organizations to the Internet. Learn how ViaSat’s Network Solutions Engineer, drove the transition from a traditional network support to a DevOps-centric model.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Password_rules_securitty.. 12 46
how to tune the query 17 80
calculate days away 11 59
Need multiple Group By's 8 51
PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.

738 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