Real World Query - FinAid

FIRST:
SELECT WHAT?
SELECT ID, NAME, Fall amount, spring amount.

SECOND:
FROM?
Well, the name is in SPRIDEN, and the awards are in RPRAWRD and RPRATRM.
RPRAWRD is the whole Aid year, RPRATRM is term specific, so we want
RPRATRM.

WHICH records (rows)?
WHERE rpratrm_fund_code = 'FWS'
       and  rpratrm_aidy_code = '9899'
       and  rpratrm_accept_amt > 0

COMPLICATION:
There is no "fall award amount" column or "spring award amount" column.
There is a "term_code" column, and an "amount" column.

APPROACH:
Do an easier version first.
SELECT ID, name, term, amount.

select
    spriden_id                      "ID",
    spriden_last_name          "Last",
    spriden_first_name          "First",
    rpratrm_term_code         "Term",
    rpratrm_accept_amt        "Amount"
from rpratrm, spriden
where rpratrm_pidm = spriden_pidm 
    and spriden_change_ind is null 
    and (rpratrm_term_code = '199809'
            or rpratrm_term_code = '199901')
    and rpratrm_fund_code = 'FWS'
    and rpratrm_accept_amt > 0
order by spriden_last_name, spriden_first_name, spriden_id;

This will produce something like this:
ID        Last
--------- ------------------------------------------------------------
First           Term       Amount
--------------- ------ ----------
123456789 Smith
Tara            199809       1200

123456789 Smith
Tara            199901       1200

223344556  Jones
Jason           199901       1200
...

First thing is the line split makes it pretty ugly, let's fix that.  The problem is the 
SPRIDEN_LAST_NAME column is 60 characters long, and SQLPLUS
will reserve 60 positions for it. If we only want to use up 30 do this:

column last format a30

And rerun the query. You'l get something like this:
ID        Last                           First           Term       Amount
--------- ------------------------------ --------------- ------ ----------
323232321 Anderson                      Erik            199809        615
323232321 Anderson                      Erik            199901        615

Which may give you the info you need.

Now for a more sophisticated version:

Say I want only 1 line per student, listing both the spring award, and the fall
award. We call this "rotating" the data. The DECODE function provides a 
nice way to do this.
 

column last format a20
select
    spriden_id                 "ID",
    spriden_last_name          "Last",
    spriden_first_name         "First",
    sum(decode(rpratrm_term_code,'199809',rpratrm_accept_amt,0))  "Fall_Amount",
    sum(decode(rpratrm_term_code,'199901',rpratrm_accept_amt,0))  "Spring_Amount"
from rpratrm, spriden
where rpratrm_pidm = spriden_pidm 
    and spriden_change_ind is null 
    and (rpratrm_term_code = '199809'
            or rpratrm_term_code = '199901')
    and rpratrm_fund_code = 'FWS'
    and rpratrm_accept_amt > 0
group by spriden_last_name, spriden_first_name, spriden_id 
order by spriden_last_name, spriden_first_name, spriden_id;

Will yield something like:
ID        Last                 First           Fall_Amount  Spring_Amount
--------- -------------------- --------------- -----------_ -------------
123123123 Smith                Enia                    1200          1200
234234234 South                Justine                 1200          1200
576576576 Swanson              Erik                     615           615
555444333 Tunbridge            Diana                   1200             0

The University supports both institutional and personal web pages. The views expressed on personal web pages are strictly those of the author, and are not reviewed or approved by the University of Vermont

Send questions and comments to  Keith.Kennedy@uvm.edu

Read the Webmaster's Policies.
Last page update: November 11, 1998