Results 1 to 8 of 8
  1. #1
    Join Date
    Mar 2005
    Posts
    71

    Unanswered: how to do update of select columns based on...

    the following criteria.
    i have the selection all done but am trying to figure out how to do the following:
    if column4 < 0 then add column4 to column3, move 0 to column4;
    if column3 < 0 then add column3 to column2, move 0 to column3;
    if column2 < 0 then add column2 to column1, move 0 to column2;
    add column3 to column4;
    move column2 to column3;
    move column1 to column2;
    if column0 > 0 move column0 to column1, move 0 to column0 else move 0 to column1;

    these are all numeric data types.

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Why are you moving columns? Here's a hint on how to do it. Go look up CASE expressions in your manual.
    Code:
    SELECT CASE WHEN column4 < 0 THEN column4 + column3 ELSE 0 END As 'An Example'
    FROM MyTable
    George
    Home | Blog

  3. #3
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    There are many ways to accomplish what you've specified, but the simplest way is for you to code the steps as you've described them... That will be the easiest for you to understand going forward because it is how you think about the operations involved.

    If you are looking for one of the other ways to go about this, you'll have to explain what you want in a bit more detail. If this is what you want, I'd describe the problem in terms of the real world instead of in terms of the columns that exist in your database now... There may be a much better way to get the same answer!

    -PatP

  4. #4
    Join Date
    Mar 2005
    Posts
    71

    i am basically trying to age balances...

    i have 5 columns that represent aged balances.
    0 thru 4
    the first part about with checking the values to 0 is due to some bad data since a - negative balance should not be aged i want to roll it down to column0 in order for the age to be current
    then after that is done i want to really age the balances forward 1 column each. the first column 0 will be the new current amount due so if it is negative i want to move 0 to my next column1. if it is not negative then i want to add column0 to column1 and move 0 to column0 so then when i go thru another process i add the new billing amount to the column0 (current due)

  5. #5
    Join Date
    Mar 2005
    Posts
    71

    OK here is what i have come up with but...

    is there a better way? (i am way to un-educated on sql syntax/options)

    Code:
    update ACCTF_TEST
    set a_curr = case when a_120 < 0 then a_120 + a_curr else a_curr end,
      a_120 = case when a_120 < 0 then 0 else a_120 end
    where a_lastage <> 20060402 
    go
    update ACCTF_TEST
    set a_curr = case when a_90 < 0 then a_90 + a_curr else a_curr end,
      a_90 = case when a_90 < 0 then 0 else a_90 end
    where a_lastage <> 20060402
    go
    update ACCTF_TEST
    set a_curr = case when a_60 < 0 then a_60 + a_curr else a_curr end,
      a_60 = case when a_60 < 0 then 0 else a_60 end
    where a_lastage <> 20060402
    go
    update ACCTF_TEST
    set a_curr = case when a_30 < 0 then a_30 + a_curr else a_curr end,
      a_30 = case when a_30 < 0 then 0 else a_30 end
    where a_lastage <> 20060402
    go
    update ACCTF_TEST
    set a_120 = a_120 + a_90,
      a_90 = 0
    where a_lastage <> 20060402 
    go
    update ACCTF_TEST
    set a_90 = a_90 + a_60,
      a_60 = 0
    where a_lastage <> 20060402 
    go
    update ACCTF_TEST
    set a_60 = a_60 + a_30,
      a_30 = 0
    where a_lastage <> 20060402 
    go
    update ACCTF_TEST
    set a_30 = case when a_curr > 0 then a_curr else 0 end,
      a_curr = case when a_curr > 0 then 0 else a_curr end
    where a_lastage <> 20060402

  6. #6
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Relational databases aren't spreadsheets.

    Could you explain the BUSINESS purpose of what you're doing as opposed to how you think the technical process should occur? It sounds a lot like you're trying to use MSSQL like it was an excel spreadsheet, that's going to bite you if true.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  7. #7
    Join Date
    Mar 2005
    Posts
    71

    Long story short....

    1) original files were non-sql (proprietery format - flat files)
    original applications COBOL code
    2) customers wanted sql file system for our current apps (COBOL-Acucorp)
    3) acucorp announces sql compliance via ntwdblib.dll (little/no change required)
    4) we implimented said compliance
    5) complaints about slowness
    6) optimized code to take advantage of where constraint when possible
    7) still complaints about slowness
    8) create stored proceedures to do some of the actual COBOL programs to bypass the ntwdblib
    9) i get assigned this task - reduce time to AGE accounts and to reset the current balance to 0 for the UPDATE process to load it.
    10) yes we are in process of writing a non-ntwdblib binding app(strickly sql code)
    11) its about 2 yrs out
    12) complaints still coming in...

  8. #8
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Bear in mind that I'm not 100% sure of how you want to present the aging, but what I would use to replace all of your code would look something a lot like this:
    Code:
    UPDATE ACCTF_TEST
       SET a_curr = a_curr
    +     CASE WHEN a_30  < 0 THEN a_30  ELSE 0 END
    +     CASE WHEN a_60  < 0 THEN a_60  ELSE 0 END
    +     CASE WHEN a_90  < 0 THEN a_90  ELSE 0 END
    +     CASE WHEN a_120 < 0 THEN a_120 ELSE 0 END
    ,  a_30  = CASE WHEN a_curr < 0 THEN 0 ELSE a_curr END
    ,  a_60  = CASE WHEN a_30   < 0 THEN 0 ELSE a_30   END
    ,  a_90  = CASE WHEN a_60   < 0 THEN 0 ELSE a_60   END
    ,  a_120 = CASE WHEN a_90   < 0 THEN 0 ELSE a_90   END + a_120		--  To acheive "bucket brigade"
       WHERE  a_lastage <> 20060402
    -PatP

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •