1 Reply Latest reply: Oct 1, 2012 3:46 PM by Fernando Obara Suzuki RSS

    consolidate fields and use correct name

    lin ye

      Hi, stores are being re-organized if status code is 1, new_acct represents the new acct # they are going to take on (ignore all other status code, it's not very important here)

       

      acct    Status   New_acct    Name

      123          1          111           AAA

      456          0          333           BBB

      789          1          111          CCC

      111          4          111           XXX

       

      So acct 123 and 789 are going to be combined into 111 acct.

       

      This is what i did in script

       

      AcctTbl:

      SQL  SELECT   DISTINCT "acct" AS original_acct,

      Status,

      Name,

      New_acct,

      case status

      when '1' then New_acct

      else acct

      END as acct

      FROM "ACCOUNT_MASTER";

       

      I want to get this:

       

      acct    Status   New_acct    Name

      456          0          333           BBB

      111          4          111           XXX

       

      but i got this

       

      acct    Status   New_acct    Name

      111          1          111           AAA

      456          0          333           BBB

      111          1          111          CCC

      111          4          111           XXX

       

      Problem with what i got is acct 111 will have 3 names when it really should be 1 name. please help.