Choose Language Hide Translation Bar
Highlighted
beastwood
Level II

Re: Basic Questions using oracle SQL and JSL

Hmm, interesting.  I can actually create the tables by logging into oracle, but I'd love to get this working and the DBA hasn't been extremely helpful so far.  If I just try inserting the records how do I set it up to iterate through the JMP table records to insert them?  For example, let's say that I have a table in JMP with 100 records and 3 columns (ID, zip code, order amount).  How do I take what you just wrote and insert those records back into the table in oracle?

Highlighted
pmroz
Super User

Re: Basic Questions using oracle SQL and JSL

OK here's some code I cobbled together from a production system.  Names have been changed to protect the innocent and all that.

dt = New Table( "Example", Add Rows( 3 ), 
		New Column( "ID", Numeric, "Continuous", Format( "Best", 12 ),
		Set Values( [1, 2, 3] )	),
	New Column( "Zip Code", Numeric, "Continuous", Format( "Best", 12 ),
		Set Values( [12345, 19117, 10111] )	),
	New Column( "Order Amount", Numeric, "Continuous", Format( "Currency", "USD", 17, 2 ),
		Set Values( [56.25, 34.11, 14.72] )	)
);


// Come up with a unique temporary table name
unique_table_name = "TMP_" || right(char(today()), 5) || "_" || 
			substitute(left(char(tick seconds()), 9), ".", "_");

dbc = create database connection(<insert your parameters here>);
max_inserts = 200;
nr = nrows(dt);

// Use a series of INSERT ALL INTO statements, which have good performance
sql_statement = evalinsert(
"CREATE TABLE ^unique_table_name^ (
	id           NUMBER,
	zipcode      NUMBER,
	ORDER_AMOUNT NUMBER)");

//show(sql_statement);
	execute sql(dbc, sql_statement);
		
sql_statement = "INSERT ALL ";

for (i = 1, i <= nr, i++,
	one_id = dt:id[i];
	one_zip = dt:zip code[i];
	one_amt = dt:Order Amount[i];

	sql_statement = sql_statement || evalinsert(
" INTO ^unique_table_name^ (ID, ZIPCODE, ORDER_AMOUNT)
  VALUES(^one_id^, ^one_zip^, ^one_amt^) ");

	if (mod(i, max_inserts) == 0,
// Weird - INSERT ALL INTO requires a SELECT statement at the end - use a dummy one
		sql_statement = sql_statement || " SELECT 1 FROM DUAL";
//		show(sql_statement);
		execute sql(dbc, sql_statement);
		sql_statement = "INSERT ALL ";
	);
);

// See if there's anything left to do
if (sql_statement != "INSERT ALL ",
	sql_statement = sql_statement || " SELECT 1 FROM DUAL";
//	show(sql_statement);
	execute sql(dbc, sql_statement);
);

close database connection(dbc);
Article Labels

    There are no labels assigned to this post.