Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Order | Account |
---|---|
1 | 1 |
2 | 1 |
3 | 2 |
4 | 2 |
Aaccount | Status | new acct |
---|---|---|
1 | Inactive | 4 |
2 | active | 2 |
3 | inactive | 4 |
4 | active | 4 |
Every time you store a QVD, the entire file is being written on disk. So, from this perspective, you can't only update one column wihtout touching other parts.
You can carefully craft your code in such a way that other columns are not changing any values, but technically they will be re-written one way or another...
when i mentioned not change the other parts, i meant don't change the values. for example if my original main table had another column called value1
order acct value1
1 1 x
2 1 y
3 2 z
4 2 a
the table should look like this after updating it
order acct value1
1 4 x
2 4 y
3 2 z
4 2 a
You can use Mapping Table.
AccountMap:
Mapping Load
OldAccount,
NewAccount
From AccountMaster:
History:
Load
Order,
ApplyMap('AccountMap', acct) as NewAcct,
...
from History;
Or
AccountMap:
Mapping Load
OldAccount,
NewAccount
From AccountMaster:
Map acct Using AccountMap;
History:
Load
Order,
acct,
...
from History;
Hope this helped.
Sean
thanks sean, your method is very nice. This is my fault, I forgot to mention the column of new account does not represent the updated list of new accounts.
so the actual account master is more like this:
account, status, new acct
1, inactive, 4
2, active, 99999200403 (some random stuff I don't care, and i do NOT want to use)
3, inactive, 4
4, active, 093284259 (do not want to use this value at all)
I don't know if I am understanding your question correctly.
If account Status is Inactive, you want to replace it to 'new acct' values and if it is active, just leave it as is?
In order words, you want to change only account# 1 and 3 to 4 and 2 and 4 as is, am I right?
Sean
Yes, and i want to overwrite the qvd with new acct info. so when i load it in next time, i will only see 4, not 1 or 3. Also accounts can change again and again, say, account 4 and 2 becomes inactive, and the new account is 5, it will change all 4 and 2 accounts on main tbl to 5.
Think of this as managing clients' order system. clients' account opening, closing, getting combined into 1 account, etc. So the account num on order table need to change, otherwise i can't link account details on acct master.
Please take a look at the example I have attached.
You will be creating a Mapping table (AccountMap) for only Inactive accounts.
Then when you are loading history table, you will replace inactive accounts to whatever you assigned in the Customer master file.
Then you can store it into qvd.
Sean