topic Query Using Results of Another Query in Discussions

Query Using Results of Another Query

Sully — Tue, 30 Oct 2018 15:13:23 GMT

Good Morning,

I am wondering if it is possible to use the results of a database query and input them into another query to pull from another database within a JSL?

For example, one of my scripts will pull the usernames from a db based on predetermined criteria. I then take those user names and run another script to pull additional data on the usernames account. I was hoping there is a way to make the inital pull of usernames and have the JSL input those usernames into the second query.

I have tried looking all over and haven't been able to find the answer.

I appreciate the help!

Thanks,

Sully

Re: Query Using Results of Another Query

txnelson — Tue, 30 Oct 2018 16:01:31 GMT

The answer is yes. You can build any subsequent query you need, based upon a previous query(s)

Here is a simple, and silly example

Names Default To Here( 1 );

// Run a Query to get one set of information...in this case what ages are
// found for the females
dt = New SQL Query(
	Version( 130 ),
	Connection( "JMP" ),
	JMP Tables(
		["Big Class" => "\C:\Program Files\SAS\JMPPRO\14\Samples\Data\Big Class.jmp"]
	),
	QueryName( "SQLQuery1" ),
	Select(
		Column(
			"age",
			"t1",
			Analysis Type( "Ordinal" ),
			Numeric Format( "Fixed Dec", "0", "NO", "" )
		)
	),
	From( Table( "Big Class", Alias( "t1" ) ) ),
	Where( Custom( "sex = \!"F\!"", UI( Custom( Base( "Continuous" ) ) ) ) )
) << Run;

// Find the distinct ages
Summarize( dt, ages = by( :age ) );

// Build the required Where Clause for the next query
queryCode = "age in(" || ages[1];
For( i = 2, i <= N Items( ages ), i++,
	queryCode = queryCode || ", " || ages[i]
);
// Close out the Where Clause
queryCode = queryCode || ")";

New SQL Query(
	Version( 130 ),
	Connection( "JMP" ),
	JMP Tables(
		["Big Class Families" =>
		"\C:\Program Files\SAS\JMPPRO\14\Samples\Data\Big Class Families.jmp"]
	),
	QueryName( "SQLQuery3" ),
	Select(
		Column( "picture", "t1", Analysis Type( "None" ) ),
		Column( "name", "t1" ),
		Column(
			"age",
			"t1",
			Analysis Type( "Ordinal" ),
			Numeric Format( "Fixed Dec", "0", "NO", "" )
		),
		Column( "sex", "t1" ),
		Column( "height", "t1", Numeric Format( "Fixed Dec", "0", "NO", "" ) ),
		Column( "weight", "t1", Numeric Format( "Fixed Dec", "0", "NO", "" ) ),
		Column( "sibling ages", "t1", Analysis Type( "Multiple Response" ) ),
		Column( "sports", "t1", Analysis Type( "Multiple Response" ) ),
		Column( "countries visited", "t1", Analysis Type( "Multiple Response" ) ),
		Column( "family cars", "t1", Analysis Type( "Multiple Response" ) ),
		Column( "reported illnesses", "t1", Analysis Type( "Unstructured Text" ) ),
		Column( "age vector", "t1", Analysis Type( "Vector" ) )
	),
	From( Table( "Big Class Families", Alias( "t1" ) ) ), 
// add the queryCode to the Where() function
	Where( Custom( queryCode, UI( Custom( Base( "Continuous" ) ) ) ) )
) << Run;

Re: Query Using Results of Another Query

pmroz — Tue, 30 Oct 2018 16:41:40 GMT

This might be a simpler example for you to work with. SQL syntax is Oracle.

dbc = create database connection(
"Driver={Oracle in OraClient11g64_home1};UID=bigdatauser;PWD=bigdatapassword;");
user_sql = 
"SELECT DISTINCT u.username 
   FROM bigdata.user_info u
  WHERE u.access_date > to_date('01-Jan-2018','DD-Mon-YYYY')";
user_dt = execute sql(dbc, user_sql);
// Convert list of usernames to IN list
user_list = user_dt:username << get values;
close(user_dt, nosave);
user_in_list = "IN ('", concat items(user_list, "', '") || "')";
data_sql = evalinsert(
"SELECT m.*
   FROM bigdata.machinery m
  WHERE m.username ^user_in_list^");
data_dt = execute sql(dbc, data_sql);
close database connection(dbc);

Re: Query Using Results of Another Query

Sully — Tue, 16 Jul 2019 20:54:02 GMT

Apologizes for the late reply. I kind of dissapeared from the JMP world for a little while.

I am back attempthig at it attempting this script. When getting to the portion for creating the user_in_list i continue to receive the error message below:

Unexpected ",". Perhaps there is a missing ";" or ",".
Line 54 Column 23: user_in_list = "IN ('"►, concat items(user_list "',
The remaining text that was ignored was
,concat items("user_list"', ')"||"');

user_in_list = "IN ('", concat items(user_list "', '") || "')";

The data that I am using is a list of case numbers instead of usernames. Could it be that even though the column is Character the numbers are affecting this line?

Thanks again for the help!

Re: Query Using Results of Another Query

pmroz — Tue, 16 Jul 2019 19:10:27 GMT

You have a comma where you should have the string concatenation operator ||, and you are missing a comma in concat items.

user_list = {"a", "b", "c"};
user_in_list = "IN ('" || concat items(user_list, "', '") || "')";

"IN ('a', 'b', 'c')"

Re: Query Using Results of Another Query

Sully — Tue, 16 Jul 2019 19:35:09 GMT

JMP Community does it again.

You all are the best. Thank you!