Re: SQL*Reportwriter/SQLPLUS problem..

In article <2021_at_h.cs.wvu.wvnet.edu> debroy_at_a.cs.wvu.wvnet.edu (Trinanjan Debroy) writes:
>I am trying to do the following using SQL*Reportwriter:
>I have a table with columns -
>
> ID YEAR FACTOR
> ~~ ~~~~ ~~~~~~
>where the key is (ID + YEAR ).
>
>The report I want should look something like this :
>
> FACTOR VALUES FOR
> ~~~~~~~~~~~~~~~~~~
> ID CURRENT-YEAR CURRENT-YEAR - 1 CURRENT-YEAR - 2
> ~~ ~~~~~~~~~~~~ ~~~~~~~~~~~~~~~~ ~~~~~~~~~~~~~~~~

Would this work for you? I'm assuming that your YEAR column is defined as NUMBER. I don't know SQL*ReportWriter so the following is a SQL*Plus script.

select tab1.id, nvl(tab1.factor, 'N/A'), nvl(tab2.factor, 'N/A'),

    nvl(tab3.factor, 'N/A')
from table tab3, table tab2, table tab1
where tab3.id(+) = tab1.id

and tab2.id(+) = tab1.id
and tab1.year = &current_year
and tab2.year(+) = tab1.year - 1
and tab3.year(+) = tab1.year - 2

-Rich

>
>For a given ID I want to print factor values for the current and last two
>years. In case there is no factor value for a certain year but there are
>values for any of the other years, I would like to be able to print N/A
>in the columns for years which do not have a factor value. Hence, suppose
>ID #2 has a factor value of 1.6 for 1992 and 1.5 for 1991 but no value for
>1990, then the report should print :
>
> ID CURRENT-YEAR CURRENT-YEAR - 1 CURRENT-YEAR - 2
> ~~ ~~~~~~~~~~~~ ~~~~~~~~~~~~~~~~ ~~~~~~~~~~~~~~~~
> 2 1.6 1.5 N/A
>
>Can this be done in SQL*Reportwriter or what should the query/queries be like
>in SQLPLUS?
>Using seperate queries in separate groups in SQL*Reportwriter does not allow
>factor values to be matched with results from previous queries in another
>group. So, since there is only a single column (YEAR) for all year entries,
>how do I print factor values for a certain ID for a certain year and then in
>the next column print the value for the previous year (N/A if there is no
>entry) and so on for the other year previous year.
>
>Any help/suggestions/pointers will be highly appreciated.
>
>
>Trinanjan DebRoy
>
>INTERNET : debroy_at_cs.wvu.wvnet.edu
>
Received on Sat Apr 18 1992 - 20:44:36 CEST