Check incorrect balance

[hatakastation]

Hi guys,

Though sometimes I work with Oracle's DB , I m a newbie still and got some questions.

Here I have a table where I have to figre out if there's any incorrect balance.

table trn_master
TRN_ID DR_CR TRN_VAL BAL_VAL
1 D 0 100
2 D 20 120
3 C 10 110
4 C 10 90

In my table, I need to check if each BAL_VAL = Previous BAL_VAL add/minus (determined by D or C) TRN_VAL. As in my example, the 4th record is incorrect as 110 -10 should be 100 but the BAL_VAL shows 90.

Is there anyway I can check this? Stupid me know how to do add/minus in single record but not comparig with previous record.

[hatakastation]

Also do you have any tutorials recommended so i can learn PL/SQL stored proc?

Sometimes I got so confused as I had to work with different languages and DB within a short period of time.....more I work sometimes more I get confused and easily got screwed up as all the schedules re tough.........

[Hack]

Welcome to DevX

Also do you have any tutorials recommended so i can learn PL/SQL stored proc?

Here are some links that should help.

[seeburnvik]

please try the following sql code to solve your problem. I am not sure wether the CASE expression works in your oracle database but it works in the most recent versions.

SELECT t1.trn_id
, t1.dr_cr
, t1.trn_val
, t1.bal_val b1
, t2.bal_val b2
, CASE WHEN t1.dr_cr = 'D' AND (t1.bal_val - t2.bal_val) = t1.TRN_val THEN 'valid'
WHEN t1.dr_cr = 'C' AND (t2.bal_val - t1.bal_val) = t1.TRN_val THEN 'valid'
WHEN t2.bal_val IS NULL THEN 'dont know'
ELSE 'invalid' END validity
FROM trn_master t2
, trn_master t1
WHERE t2.trn_id(+) = t1.trn_id - 1

thanks and do reply if u found this useful.