Re: Order by in Multiple reports

["Logu Chakravarthi (IT Dept. - Langley)" <logu_chakravarthi@OWFG.COM>]

        I want to make sure that there is no way of doing two reports with
different "order by" condition by using a single select statement, calling
only once. Right now I am using an array instead of calling the database
twice and I hope it takes less time to query from array rather than from
database. Thanks Tony, Have a nice weekend guys.

C.Logu

> -----Original Message-----
> From: Tony DeLia [SMTP:tdelia@EROLS.COM]
> Sent: 1999/06/11 1:03 PM
> To:   Multiple recipients of list SQR-USERS
> Subject:      Re: Order by in Multiple reports
>
> Logu,
>
> You can use a "single-select'" if you call it multiple times... you can
> only order a result set once per execution...
>
> EXAMPLE:
>
> ...
> let $report_no = '1'
> let $order_by  = '1, 2'     ! B.BENEFIT_PROGRAM, A.NAME
> do Process-Select
>
> let $report_no = '2'
> let $order_by  = '2, 1'     ! A.NAME, B.BENEFIT_PROGRAM
> do Process-Select
> ...
>
> begin-procedure Process-Select
>
> begin-select
>
> B.BENEFIT_PROGRAM
> A.NAME
> ...
>
>   if $report_no = '1'
>      do Print-Report1
>   else
>      do Print-Report2
>    end-if
>
>  FROM ...
> WHERE ...
>  ORDER BY [$order_by]
>
> end-select
>
> end-procedure
>
>
> The result set would be created twice (unfortunately)... the advantage
> would be maintaining one set of criteria instead of two identical
> sets...
>
>             Tony DeLia
>
> PS - It's too nice out to post SQR material... Time to start the
> weekend! :)
>
> Logu Chakravarthi (IT Dept. - Langley) wrote:
> >
> > Hi,
> >
> >         I am writing a SQR that produces two reports from a single
> select.
> > But the client wants the columns to be ordered in different way for each
> > report. Is it possible to do that within a single select. I've furnished
> the
> > example. Any suggestions. Thanks.
> >
> > begin select
> > B.BENEFIT_PROGRAM
> > A.NAME
> > A.EMPLID
> >
> >  DO PRINT-REPORT1
> >  DO PRINT-REPORT2
> >
> > from PS_PERSONAL_DATA A, PS_BEN_PROG_PARTIC B
> > where A.EMPLID = B.EMPLID
> >   and B.EFFDT = (select MAX(.......)
> > order by A.NAME, ???
> >
> > I SHOULD GET THE REPORTS LIKE THIS
> >
> > Report 1
> > HO      Albert
> >         Churchill
> > UU      Andrew
> >         Baden
> >
> > Report 2
> > HO      Albert
> > UU      Andrew
> > UU      Baden
> > HO      Churchill
> >
> > Any suggestions.
> >
> > C.Logu
>
> --
> Tony DeLia
> AnswerThink Consulting Group
> PeopleSoft Solutions Practice - Delphi Partners
> tdelia@erols.com
> http://www.sqrtools.com