Re: Questions

[Pavan Krishnamurthy <Pavan_Krishnamurthy@PUTNAM.COM>]

Hi Russ,

Thanks for that explanation. That would really help me...I am attaching the
code that I have written, please let me know how I can implement what you
have suggested here :

begin-heading 6
print 'Excess Balance Report' (+1,50) BOLD box SHADE
print 'Fund'                  (+2,1)
print 'Fund Name'             (,9)
print 'Currency'              (,40)
print 'Tolerance USD'         (,50)
print 'Excess Bank'           (,65)
print 'Bank Balance'          (,80)
print 'Bank Balance'          (,95)
print 'GPS Balance'           (,110)
print 'Number '               (+1,1)
print 'equivalent'            (,50)
print 'Balance Today'         (,65)
print 'T-1 day'               (,80)
print 'T-2 day'               (,95)
print 'Today'                 (,110)
print '='                     (+1,1,120) fill
print ''                      (+1,1,120) fill
end-heading

Begin-Program
Begin-select
a.fund_nbr (+1,1)
a.fund_name (,9)
b.curr_code (,40)
'$' || a.excess_amt_usd (,50)
b.curr_code || ' ' ||b.bal_amt   (,65)
b.curr_code || ' ' ||b.bal_amt   (,80)   -> This should be the previous
day's value
b.curr_code || ' ' ||b.bal_amt   (,95)   -> This should be the value 2 days
prior to the current date
b.curr_code || ' ' ||b.bal_amt   (,110)
    next-listing
from ucm_fund a,
ucm_bal b,
pp_fx_rate c
where b.fund_nbr=a.fund_nbr
and b.curr_code <> 'USD'
and b.bal_type_id=500
and b.source_ind='B'
and a.fund_sub_type_id=5
and c.crrncy_cd=b.curr_code
and a.excess_amt_usd < b.bal_amt*c.fx_mult_rate
End-select
End-Program

Could you please let me know ?

Thanks,
Pavan



The beauty of SQR is that you don't have to directly handle a cursor.  The
Begin-Select takes care of the cursor.  You nest Begin-Selects by inserting
a Do <procedure name>.  In the next procedure you have another
Begin-Select. Assuming that the previous day's value and the previous two
day's value are selectable from the database, you just pass those values to
another variable and print them. Here's a brief example of how to get the
data.

Begin-Procedure Get-First-Value
Begin-Select
Value
     let $First_value = &Value
     Do Get-Second-Value
     Do Get-Third-Value
     Do Print-Values
from table
where date = today
End-Select
End-Procedure Get-First-Value

Begin-Procedure Get-Second-Value
Begin-Select
Value
     let $Second_value = &Value
from table
where date = yesterday
End-Select
End-Procedure Get-Second-Value

Begin-Procedure Get-Third-Value
Begin-Select
Value
     let $Third_value = &Value

from table
where date = day before yesterday
End-Select
End-Procedure Get-Third-Value

Hope this helps