|
Is the order the columns are uploaded the same as in this script or is it dependent on the column number? START SYNCHRONIZATION SCHEMA CHANGE FOR TABLES DBA.STOCK SET SCRIPT VERSION = 'vup_1'; ALTER publication uploadtope ALTER TABLE STOCK( "COMP_ID", "FARM_ID", "FIN_EVENTID", "ACCT_ID", "PERC_SOWS", "PERC_FINISHERS", "PERC_REPLGILTS", "PERC_WEANERS", "PRICE100KG_EXVAT", "TOTAL_WEIGHT", "EV", "DM", "PRICE100KG" ) WHERE (select upload_data from company as c2 where c2.comp_id = stock.comp_id) = 1; STOP SYNCHRONIZATION SCHEMA CHANGE; Upload order is now STOCK( "COMP_ID", "FARM_ID", "FIN_EVENTID", "ACCT_ID", "PERC_SOWS", "PERC_FINISHERS", "PERC_REPLGILTS", "PERC_WEANERS", "NUMBER", "TOTAL_WEIGHT", "EV", "DM", "PRICE100KG" ) I want to replace "NUMBER" (colno = 9) with ""PRICE100KG_EXVAT" (colno = 15) |
|
The order that the columns are uploaded is based on the column number, not the order that you specify in the column list subset you specify when you add the table to the publication. This should be irrelevant though. When you write your synchronization scripts at the consolidated database, you reference the named parameters in your scripts, so the column order should not matter. For example : call ml_add_table_script( 'vup_1', 'STOCK', 'upload_insert',
'INSERT INTO STOCK
(
"COMP_ID",
"FARM_ID",
"FIN_EVENTID",
"ACCT_ID",
"PERC_SOWS",
"PERC_FINISHERS",
"PERC_REPLGILTS",
"PERC_WEANERS",
"PRICE100KG_EXVAT",
"TOTAL_WEIGHT",
"EV",
"DM",
"PRICE100KG"
)
VALUES
(
{ml r.COMP_ID},
{ml r.FARM_ID},
{ml r.FIN_EVENTID},
{ml r.ACCT_ID},
{ml r.PERC_SOWS},
{ml r.PERC_FINISHERS},
{ml r.PERC_REPLGILTS},
{ml r.PERC_WEANERS},
{ml r.PRICE100KG_EXVAT},
{ml r.TOTAL_WEIGHT},
{ml r.EV},
{ml r.DM},
{ml r.PRICE100KG}
)'
);
Reg Thanks, I wanted to correct an error and send the price100kg_exvat instead of the number. Number is allways null; At the consolidated it would look like this 'INSERT INTO STOCK
(
"COMP_ID",
"FARM_ID",
"FIN_EVENTID",
"ACCT_ID",
"PERC_SOWS",
"PERC_FINISHERS",
"PERC_REPLGILTS",
"PERC_WEANERS",
"PRICE100KG_EXVAT",
"TOTAL_WEIGHT",
"EV",
"DM",
"PRICE100KG"
)
VALUES
(
{ml r.COMP_ID},
{ml r.FARM_ID},
{ml r.FIN_EVENTID},
{ml r.ACCT_ID},
{ml r.PERC_SOWS},
{ml r.PERC_FINISHERS},
{ml r.PERC_REPLGILTS},
{ml r.PERC_WEANERS},
{ml r.NUMBER},
{ml r.TOTAL_WEIGHT},
{ml r.EV},
{ml r.DM},
{ml r.PRICE100KG}
)'
);
In this case I would not have to replace the upload scripts in all databases at the same time.
(24 Nov '20, 11:49)
ontsnapt
|