Choose Language Hide Translation Bar
Highlighted
Sully
Community Trekker

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

 

0 Kudos
1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
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
Highlighted
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
Highlighted
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

Highlighted
Sully
Community Trekker

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!

0 Kudos
Highlighted
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')"
Highlighted
Sully
Community Trekker

Re: Query Using Results of Another Query

JMP Community does it again.

 

You all are the best. Thank you!

0 Kudos