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.
