Solved

Formatting SQL report

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

How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
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.
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

930 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

12 Experts available now in Live!

Get 1:1 Help Now