BookmarkSubscribeRSS Feed
Sully

Community Trekker

Joined:

Aug 24, 2017

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

 

2 REPLIES
txnelson

Super User

Joined:

Jun 22, 2012

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
Highlighted
pmroz

Super User

Joined:

Jun 23, 2011

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