Home -> Usenet -> comp.databases.oracle -> SQL*Reportwriter/SQLPLUS problem..

SQL*Reportwriter/SQLPLUS problem..

From: Trinanjan Debroy <debroy_at_a.cs.wvu.wvnet.edu>
Date: 18 Apr 92 17:33:13 GMT
Message-ID: <2021_at_h.cs.wvu.wvnet.edu> 






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
 ~~              ~~~~~~~~~~~~        ~~~~~~~~~~~~~~~~     ~~~~~~~~~~~~~~~~





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 - 19:33:13 CEST












Original text of this message