早上好，
我想知道是否可以使用数据库查询的结果并将它们输入到另一个查询中以从 JSL 中的另一个数据库中提取？
例如，我的一个脚本将根据预定标准从数据库中提取用户名。 然后我使用这些用户名并运行另一个脚本来提取用户名帐户上的其他数据。 我希望有一种方法可以对用户名进行初始拉取，并让 JSL 将这些用户名输入到第二个查询中。
我已经尝试过四处寻找，但无法找到答案。
感谢您的帮助！
谢谢，
玷污
您有一个逗号，应该有字符串连接运算符 ||，并且您在连接项中缺少逗号。
user_list = {"a", "b", "c"};
user_in_list = "IN ('" || concat items(user_list, "', '") || "')";
"IN ('a', 'b', 'c')"为迟到的回复道歉。 我有一段时间从 JMP 世界中消失了。
我回来尝试这个脚本。 当进入创建 user_in_list 的部分时，我继续收到以下错误消息：
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 "', '") || "')";
我使用的数据是案例编号列表而不是用户名。 是不是即使列是字符，数字也会影响这一行？
再次感谢您的帮助！
这可能是一个更简单的示例供您使用。SQL 语法是 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);答案是肯定的。您可以根据先前的查询构建您需要的任何后续查询
这是一个简单而愚蠢的例子
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;
