DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Results 1 to 4 of 4

Thread: Check incorrect balance

  1. #1
    Join Date
    Jun 2007
    Posts
    7

    Unhappy Check incorrect balance

    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.

  2. #2
    Join Date
    Jun 2007
    Posts
    7
    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.........

  3. #3
    Join Date
    Apr 2007
    Location
    Sterling Heights, Michigan
    Posts
    8,663
    Welcome to DevX

    Quote Originally Posted by hatakastation
    Also do you have any tutorials recommended so i can learn PL/SQL stored proc?
    Here are some links that should help.

  4. #4
    Join Date
    Jun 2007
    Posts
    1

    check balance validity

    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.

Similar Threads

  1. Account
    By Tmcclain in forum Java
    Replies: 3
    Last Post: 11-20-2006, 10:04 AM
  2. conditional check in oracle
    By rahulvasanth in forum Database
    Replies: 1
    Last Post: 01-26-2006, 11:48 PM
  3. Spell check
    By borgfabr in forum .NET
    Replies: 0
    Last Post: 05-20-2005, 04:35 AM
  4. Check box isssue when bound to a database column
    By Narayan in forum VB Classic
    Replies: 0
    Last Post: 04-10-2001, 10:30 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center
 
 
FAQ
Latest Articles
Java
.NET
XML
Database
Enterprise
Questions? Contact us.
C++
Web Development
Wireless
Latest Tips
Open Source


   Development Centers

   -- Android Development Center
   -- Cloud Development Project Center
   -- HTML5 Development Center
   -- Windows Mobile Development Center