Solved

Formatting SQL report

Posted on 2009-07-09
1
250 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
[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
1 Comment
 

Accepted Solution

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

Featured Post

Industry Leaders: 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!

Question has a verified solution.

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

Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
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.

740 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