topic Updating my JMP table with SQL "AS" statement in Discussions

Updating my JMP table with SQL "AS" statement

claude_bellavan — Sat, 10 Jun 2023 23:21:30 GMT

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.

Re: Updating my JMP table with SQL "AS" statement

ih — Wed, 28 Oct 2020 17:26:34 GMT

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 )
);

Re: Updating my JMP table with SQL "AS" statement

claude_bellavan — Fri, 30 Oct 2020 14:06:40 GMT

Thank you very much ih.

That will be very useful!

Claude B.