Updating my JMP table with SQL "AS" statement

[#1]

Hello,

I have to regularly update my JMP file by connecting to my database and running a long manually created SQL query. Until now, I used the "AS" command to match the field names in my database with the simplified name of columns in JMP. After importing the data into a temporary JMP table, my script finished the update by making a join with my working JMP file using the same short column names.

Question 1: I tried using JMP's newest SQL query builder (available since version 11, I believe). But I can't find a button to manage the "AS" command other than to manually edit the query which considerably reduces the interest of the new constructor. Is there a more efficient way?

Question 2: Is there a more suitable method for my needs? If I imported the fields as is (without changing the names) into the temporary table, would there be an efficient way to update the data in my JMP table? Using some sort of name mapping table in my script, for example?

NB: I chose not to import the updated data directly into my JMP file because the items are not always in the same order and I regularly create new columns for derived data. Otherwise, it would create offsets when updating.

Thank you for your attention

Claude B.

[#2 · Accepted solution]

One option might be to leave the names unchanged in the query results and then update them using a saved data table having the old and new names:

Names default to here(1);

//Open a saved jmp file with the old and new names (creating the table here instead of opening it...)
dtNames = New Table( "Names",
	Add Rows( 4 ),
	New Column( "old", Character, "Nominal",
		Set Values( {"Sepal length", "Sepal width", "Petal length", "Petal width"} )
	),
	New Column( "new", Character, "Nominal",
		Set Values( {"sl", "sw", "pl", "pw"} )
	)
);

//Run database query using 'default' column names
dtQuery = Open("$Sample_data/iris.jmp");

//Update names in the query results
for each row( dtNames,
	Column( dtQuery, :old ) << Set Name( :new )
);

[#3]

Thank you very much ih.

That will be very useful!

Claude B.