We help IT Professionals succeed at work.

Formatting SQL report

mskitten
mskitten asked
on
Medium Priority
286 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
Comment
Watch Question

Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.