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)

asked 24 Nov '20, 10:22

ontsnapt's gravatar image

ontsnapt
1107716
accept rate: 0%

edited 24 Nov '20, 10:27

Reg%20Domaratzki's gravatar image

Reg Domaratzki
7.0k340109


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

permanent link

answered 24 Nov '20, 10:54

Reg%20Domaratzki's gravatar image

Reg Domaratzki
7.0k340109
accept rate: 38%

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
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×361

question asked: 24 Nov '20, 10:22

question was seen: 126 times

last updated: 25 Nov '20, 06:48