Solved

Formatting SQL report

Posted on 2009-07-09
1
246 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

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.‚Äč
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
Via a live example, show how to take different types of Oracle backups using RMAN.

708 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now