Solved

Formatting SQL report

Posted on 2009-07-09
1
249 Views
Last Modified: 2013-12-19
I'm trying to spit my results out into a report and was wondering how I would go about eliminating the duplicate values in the report. The values themselves arn't duplicates, but they show up for each row in the report (see attachment). It would be nice to see only one value. Hope that makes sense.

Thanks!


CLEAR COLUMNS
set echo off
spool c:\gpas.doc
BREAK ON WELL SKIP 2 ON GPAS_VOL
 
SET PAGESIZE 9999
SET LINESIZE 115
COLUMN "WELL" format a28
COLUMN "ALLOC_CENTRE" format a27
COLUMN "CODE" format a8
COLUMN "OWNER" format a30
COLUMN "WI%" format a5
COLUMN "WI%" format 0.9999
COLUMN "WI%" justify center
COLUMN "VOLUME" format a10
COLUMN "VOLUME" format 999999.99
 
 
 
SELECT well,
       alloc_centre,
       code,
       owner,
       MAX( DECODE(max_effective_date,source_owner_effectivedate,source_owner_percentage, NULL) ) "WI%",
       volume
        
       
FROM (SELECT 
             b.source_name well,
             a.alloc_centre_name alloc_centre,
             e.vendor_code code,
             e.vendor_name owner,
             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,
             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 to_date( '01/JUN/2009', 'DD/MON/YYYY') AND to_date( '30/JUN/2009', 'DD/MON/YYYY')
              
              
              GROUP BY b.source_name,
                       a.alloc_centre_name,
                       e.vendor_code,
                       e.vendor_name,
                       c.source_owner_effectivedate, 
                       c.source_owner_percentage)
          
             
GROUP BY well, alloc_centre, owner,  code, volume

Open in new window

report.JPG
0
Comment
Question by:mskitten
1 Comment
 

Accepted Solution

by:
mskitten earned 0 total points
ID: 24815112
Please disregard, I have figured it out.
0

Featured Post

DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SQL Help 27 57
Setting Macro in Access to Automate Running an Append at a Certain Time 2 28
Oracle collections 15 27
Requesting help with creating an SQL query with 2 tables 6 27
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…
When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
Via a live example, show how to take different types of Oracle backups using RMAN.
This video shows how to recover a database from a user managed backup

829 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