nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

Query Using Results of Another Query

Sully

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

Accepted answers

pmroz

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

All comments

txnelson

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;

pmroz

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

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

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')"