cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
JMP is taking Discovery online, April 16 and 18. Register today and join us for interactive sessions featuring popular presentation topics, networking, and discussions with the experts.
Choose Language Hide Translation Bar
Sully
Level III

Query Using Results of Another Query

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

 

1 ACCEPTED SOLUTION

Accepted Solutions
pmroz
Super User

Re: Query Using Results of Another Query

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

View solution in original post

5 REPLIES 5
txnelson
Super User

Re: Query Using Results of Another Query

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;
Jim
pmroz
Super User

Re: Query Using Results of Another Query

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);
Sully
Level III

Re: Query Using Results of Another Query

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!

pmroz
Super User

Re: Query Using Results of Another Query

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')"
Sully
Level III

Re: Query Using Results of Another Query

JMP Community does it again.

 

You all are the best. Thank you!