早上好，
我想知道是否可以使用數據庫查詢的結果並將它們輸入到另一個查詢中以從 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;
