Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: SQL Plus PL/SQL Syntax Question

Re: SQL Plus PL/SQL Syntax Question

From: Sig Dock <sigdock_at_hotmail.com>
Date: 28 Aug 2001 01:35:15 -0700
Message-ID: <17dc745b.0108280035.5600ffe7@posting.google.com>


Jon,

you will have to use columndefinitions and put a break on them. Try the following:

COLUMN C1 FORMAT 999
COLUMN C2 FORMAT 999
COLUMN C3 FORMAT A40
etc

BREAK ON C1 ON C2 SELECT PA_JOB.PROJECT_NO

, PA_JOB.JOB_NO   C1
, PA_JOB.DES1  C2
, PA_JOB.JOB_MANAGER_REF C3
, PA_JOB.EST_COMPLETION_DATE C4
, PA_JOB.DEADLINE_DATE C5
, PA_JOB.CUSTOMER_REF C6
, PA_JOB.UD_NOTE C7

FROM K.PA_JOB PA_JOB
WHERE ( PA_JOB.STATUS IN ('A','I') )
ORDER BY PA_JOB.CUSTOMER_REF ASC, PA_JOB.JOB_MANAGER_REF ASC,PA_JOB.PROJECT_NO ASC, PA_JOB.JOB_NO ASC CLEAR BREAK
CLEAR COLUMN For more information, read the SQL*Plus user manual.

h.t.h.

BertJan Meinders
Oracle DBA

"Jon Schlatter" <*jon-schlatter_at_stamats.com> wrote in message news:<toluem49c0ln7a_at_corp.supernews.com>...
> I need to perform a select statement that does a group by and the supresses
> duplicate entries. I am writing a job status report and have the following
> select statement written.
>
> SELECT PA_JOB.PROJECT_NO, PA_JOB.JOB_NO, PA_JOB.DES1,
> PA_JOB.JOB_MANAGER_REF, PA_JOB.EST_COMPLETION_DATE,PA_JOB.DEADLINE_DATE,
> PA_JOB.CUSTOMER_REF, PA_JOB.UD_NOTE
> FROM K.PA_JOB PA_JOB
> WHERE ( PA_JOB.STATUS IN ('A','I') )
> ORDER BY PA_JOB.CUSTOMER_REF ASC, PA_JOB.JOB_MANAGER_REF
> ASC,PA_JOB.PROJECT_NO ASC, PA_JOB.JOB_NO ASC
>
> All the data shows up properly, but now I need to supress duplicate entries.
> Currently my report looks like this.
>
> Client1 Project1 Description
> Client1 Project2 Description
> Client1 Project3 Description
> Client2 Project1 Description
> Client2 Project2 Description
> Client2 Project3 Description
>
> What I want to see is like this.
>
> Client1 Project1 Description
> Project2 Description
> Project3 Description
> Client2 Project1 Description
> Project2 Description
> Project3 Description
>
> Can I do this in SQL Plus PL/SQL?
Received on Tue Aug 28 2001 - 03:35:15 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US