在 SQL 中使用主要表示式
如果您想要跟隨範例，請參閱執行查詢的範例資料以檢視範例資料，並瞭解如何使用 OCI 主控台建立範例表格，以及使用 JSON 檔案載入資料。
括號化表示式
括號化表示式主要用來改變運算子之間的預設優先順序。它們也被用來作為語法輔助，以其他方式混合運算式會造成語法不明確。
範例：擷取搭乘 JFK 的乘客全名、標記編號及遞送明細，而其目的地為 MAD 或 VIE。
SELECT fullName, bag.bagInfo.tagNum,
bag.bagInfo.routing,
bag.bagInfo[].flightLegs[].fltRouteDest
FROM BaggageInfo bag
WHERE bag.bagInfo.flightLegs[].fltRouteSrc=any "JFK" AND
(bag.bagInfo[].flightLegs[].fltRouteDest=any "MAD" OR
bag.bagInfo[].flightLegs[].fltRouteDest=any "VIE" )說明：您要提取乘客的全名、標籤號碼和路線詳細資料。第一個篩選條件是登機點 / 運輸為 JFK。滿足此條件之後，第二個篩選條件即為 MAD 或 VIE。您可以使用 OR 條件來篩選目的地值。
輸出：
{"fullName":"Dierdre Amador","tagNum":"17657806240229","routing":"JFK/MAD","fltRouteDest":"MAD"}
{"fullName":"Rosalia Triplett","tagNum":"17657806215913","routing":"JFK/IST/VIE","fltRouteDest":["IST","VIE"]}
{"fullName":"Kendal Biddle","tagNum":"17657806296887","routing":"JFK/IST/VIE","fltRouteDest":["IST","VIE"]}Case 表示式
搜尋的 CASE 表示式類似於傳統程式設計語言的 if-then-else 陳述式。它由多個 WHEN-THEN 配對組成，後面接著一個選用的 ELSE 子句。每個 WHEN 表示式都是一個條件，也就是說它必須傳回 BOOLEAN。THEN 表示式和 ELSE 表示式可以傳回任何項目順序。CASE 表示式的評估方式是先從上到下評估 WHEN 表示式，直到第一個傳回 true 的表示式為止。如果是傳回 true 的第 i-th WHEN 表示式，則會評估 i-th THEN 表示式，其結果就是整個 CASE 表示式的結果。如果沒有 WHEN 表示式傳回 true，則如果有 ELSE，則會評估其表示式，而其結果是整個 CASE 表示式的結果；否則，CASE 表示式的結果就是空序列。
範例：
SELECT
fullName,
CASE
WHEN NOT exists bag.bagInfo.flightLegs[0]
THEN "you have no bag info"
WHEN NOT exists bag.bagInfo.flightLegs[1]
THEN "you have one hop"
WHEN NOT exists bag.bagInfo.flightLegs[2]
THEN "you have two hops."
ELSE "you have three hops."
END AS NUMBER_HOPS
FROM BaggageInfo bag WHERE ticketNo=1762340683564說明：您要使用 CASE 陳述式來決定乘客 bagInfo 有多少次的轉換。如果 flightLegs 陣列沒有元素，則乘客沒有袋資料。當 flightLegs 陣列只有一個元素時，只有一個傳輸點。同樣地，如果 flightLegs 陣列有兩個元素，則會有兩個躍點。否則有三個在途點。在這裡，您假設一個袋子最多可以有三個在途點 / 躍點。
輸出：
{"fullName":"Mary Watson","NUMBER_HOPS":"you have two hops."}範例 2：撰寫查詢，在現有值不是字串時，提醒系統更新 tagNum 的乘客。
SELECT bag.bagInfo[].tagNum,
CASE
WHEN bag.bagInfo[0].tagNum is of type (NUMBER)
THEN "Tagnumber is not a STRING. Update the data"
ELSE "Tagnumber has correct datatype"
END AS tag_NUM_TYPE
FROM BaggageInfo bag說明：bagInfo 綱要中乘客的 tagNum 是 STRING 資料類型。但是應用程式可能會誤以 NUMBER 值作為 tagNum 的值。查詢使用 "is of type" 運算子來擷取此項目，如果現有值不是字串，則提示系統更新 tagNum。
輸出 (繁殖只會顯示少數資料列)。
{"tagNum":"17657806240001","tag_NUM_TYPE":"Tagnumber has correct datatype"}
{"tagNum":"17657806224224","tag_NUM_TYPE":"Tagnumber has correct datatype"}
{"tagNum":17657806243578,"tag_NUM_TYPE":"Tagnumber is not a STRING. Update the data"}
{"tagNum":"1765780623244","tag_NUM_TYPE":"Tagnumber has correct datatype"}轉換表示式
如果可能的話，轉換表示式會從其輸入序列的項目建立指定之目標類型的新項目。例如，您可以使用 CAST 表示式將 STRING 轉換成 TIMESTAMP (0)。
CAST 表示式中所遵循的規則：
-
如果輸入項目的類型等於目標項目類型，則轉換會是 no-op：會傳回輸入項目本身。
-
如果目標類型是 JSON 以外的萬用字元類型，且輸入項目的類型是萬用字元類型的子類型，則轉換會是 no-op。
-
目標類型若為 JSON，則輸入項目若為非 json 單元類型，便會發生錯誤。
-
如果目標類型是陣列類型，如果輸入項目不是陣列，就會發生錯誤。
-
如果目標類型是字串，則輸入項目可以是任何類型。這表示每個項目都可以轉換成字串。對於時間戳記，其字串值為 UTC，格式為
uuuu-MM-dd['T'HH:mm:ss]。 -
如果目標類型是字串以外的單元類型，則輸入項目也必須是單元。
-
-
整數和長整數可以轉換為時間戳記。輸入值會解譯為自 1970 年 1 月 1 日 00:00:00 GMT 之後的毫秒數。
-
字串項目可以轉換成所有其他單元類型。轉換是否成功取決於是否可以將實際的字串值剖析為屬於目標類型之網域的值。
-
時間戳記項目可轉換成所有時間戳記類型。如果目標類型的精確度較小，則產生的時戳會是最接近目標精確度中之輸入時戳的數字。
-
-
-
若要將 STRING 轉換成 TIMESTAMP，如果輸入的 STRING 值為 ISO-8601 格式，則 SQL 程式實際執行會將它自動轉換成 TIMESTAMP 資料類型。
注意：
ISO8601 說明代表日期、時間和持續時間的國際公認方式。
語法：含時間的日期：YYYY-MM-DDThh:mm:ss[.s[s[s[s[s[s]]]][Z| (+|-) hh:mm]
其中
- YYYY 指定年份，小數點四位數
- MM 指定月份為兩位小數，00 到 12
- DD 將天指定為兩位小數，00 到 31
- hh 指定小時，為兩位小數，00 到 23
- mm 指定分鐘為兩位小數，從 00 到 59
- ss[.s[s[s[s]]]] 將秒數指定為 00 到 59 的兩個小數位數，選擇性地接著一個小數點和 1 到 6 個小數位數 (代表第二個的小數位數部分)。
- Z 指定 UTC 時間 (時區 0)。(它也可以由 +00:00 指定，但不能由 -00:00 指定。)
- (+|-) hh:mm 指定與 UTC 不同的時區。(需要 + 或 - 其中之一。)
範例 1：以 TIMESTAMP (3) 格式擷取預訂代碼為 DN3I4Q 之乘客的行李抵達日期。
SELECT CAST (bag.bagInfo.bagArrivalDate AS Timestamp(3))
AS BAG_ARRIVING_DATE
FROM BaggageInfo bag WHERE bag.confNo=DN3I4Q說明：bagArrivalDate 為 STRING。使用 CAST 將此欄位轉換為 TIMESTAMP 格式。
輸出：
{"BAG_ARRIVING_DATE":"2019-02-15T21:21:00.000Z"}範例 2：擷取 2019 年之後出貨之所有客戶行李的全名與標籤編號。
SELECT fullName, bag.ticketNo,
bag.bagInfo[].bagArrivalDate
FROM BaggageInfo bag WHERE
exists bag.bagInfo[$element.bagArrivalDate >="2019-01-01T00:00:00"]說明：您想要篩選並顯示 2019 年之後出貨的行李明細。flightLegs 陣列中每個元素的行李到達日期會與指定的時間戳記 (2019-01-01T00:00:00) 進行比較。此處的轉換是隱含的，因為 bagArrivalDate 是 STRING，而且會直接與靜態時戳值進行比較。當可以執行隱含轉換時，不需要明確的 CAST 函數。不過，資料格式應為 YYYY-MM-DDTHH:MI:SS。然後使用 EXISTS 條件來檢查此時間戳記條件的 bagInfo 是否存在。
輸出：
{"fullName":"Kendal Biddle","ticketNo":1762377974281,"bagArrivalDate":"2019-03-05T12:00:00Z"}
{"fullName":"Lucinda Beckman","ticketNo":1762320569757,"bagArrivalDate":"2019-03-12T15:05:00Z"}
{"fullName":"Adelaide Willard","ticketNo":1762392135540,"bagArrivalDate":"2019-02-15T21:21:00Z"}
{"fullName":"Raymond Griffin","ticketNo":1762399766476,"bagArrivalDate":"2019-02-03T08:09:00Z"}
{"fullName":"Elane Lemons","ticketNo":1762324912391,"bagArrivalDate":"2019-03-15T10:13:00Z"}
{"fullName":"Zina Christenson","ticketNo":1762390789239,"bagArrivalDate":"2019-02-04T10:08:00Z"}
{"fullName":"Zulema Martindale","ticketNo":1762340579411,"bagArrivalDate":"2019-02-25T20:15:00Z"}
{"fullName":"Dierdre Amador","ticketNo":1762376407826,"bagArrivalDate":"2019-03-07T13:51:00Z"}
{"fullName":"Henry Jenkins","ticketNo":176234463813,"bagArrivalDate":"2019-03-02T13:18:00Z"}
{"fullName":"Rosalia Triplett","ticketNo":1762311547917,"bagArrivalDate":"2019-02-12T07:04:00Z"}
{"fullName":"Lorenzo Phil","ticketNo":1762320369957,"bagArrivalDate":["2019-03-12T15:05:00Z","2019-03-12T16:25:00Z"]}
{"fullName":"Gerard Greene","ticketNo":1762341772625,"bagArrivalDate":"2019-03-07T16:01:00Z"}
{"fullName":"Adam Phillips","ticketNo":1762344493810,"bagArrivalDate":"2019-02-01T16:13:00Z"}
{"fullName":"Doris Martin","ticketNo":1762355527825,"bagArrivalDate":"2019-03-22T10:17:00Z"}
{"fullName":"Joanne Diaz","ticketNo":1762383911861,"bagArrivalDate":"2019-02-16T16:13:00Z"}
{"fullName":"Teena Colley","ticketNo":1762357254392,"bagArrivalDate":"2019-02-13T11:15:00Z"}
{"fullName":"Michelle Payne","ticketNo":1762330498104,"bagArrivalDate":"2019-02-02T23:59:00Z"}
{"fullName":"Mary Watson","ticketNo":1762340683564,"bagArrivalDate":"2019-03-14T06:22:00Z"}
{"fullName":"Omar Harvey","ticketNo":1762348904343,"bagArrivalDate":"2019-03-02T16:09:00Z"}
{"fullName":"Fallon Clements","ticketNo":1762350390409,"bagArrivalDate":"2019-02-21T14:08:00Z"}
{"fullName":"Lisbeth Wampler","ticketNo":1762355854464,"bagArrivalDate":"2019-02-10T10:01:00Z"}序列轉換表示式
序列轉換表示式會將序列轉換成另一個序列。語法上看起來像是名稱為 seq_transform 的函數。第一個引數是產生要轉換之順序 (輸入順序) 的表示式，第二個引數則是針對輸入順序的每個項目計算的 "mapper" 表示式。seq_transform 表示式的結果是由對應程式表示式的每個評估所產生的序列串連。對應程式表示式可以透過 $ 變數存取目前的輸入項目。
範例：針對每個 ticketNo，擷取包含對該 ticketNo 行李執行之所有動作的平面陣列。
SELECT seq_transform(l.bagInfo[],
seq_transform(
$sq1.flightLegs[],
seq_transform(
$sq2.actions[],
{
"at" : $sq3.actionAt,
"action" : $sq3.actionCode,
"flightNo" : $sq2.flightNo,
"tagNum" : $sq1.tagNum
}
)
)
) AS actions
FROM baggageInfo l WHERE ticketNo=1762340683564說明：您可以使用序列轉換表示式來轉換儲存在表格資料列中的 JSON 文件。在這種情況下，您經常使用多個相互巢狀的序列轉換表示式。在這裡，內部序列轉換的對應程式表示式可能需要存取外部序列轉換的目前項目。若要允許此情況，每個序列轉換表示式 'S' 都會宣告名稱為 $sqN 的變數，其中 N 是外部序列轉換表示式中表示式 S 的巢狀層次。$sqN 基本上是 $ 的同義字，亦即，它繫結至輸入表示式 S 傳回的項目。不過，$sqN 可由其他可以在表示式 S 內嵌的序列轉換表示式存取。
輸出：
{
"actions":[
{"action":"ONLOAD to HKG","at":"YYZ","flightNo":"BM267","tagNum":"17657806299833"},
{"action":"BagTag Scan at YYZ","at":"YYZ","flightNo":"BM267","tagNum":"17657806299833"},
{"action":"Checkin at YYZ","at":"YYZ","flightNo":"BM267","tagNum":"17657806299833"},
{"action":"Offload to Carousel at BLR","at":"BLR","flightNo":"BM115","tagNum":"17657806299833"},
{"action":"ONLOAD to BLR","at":"HKG","flightNo":"BM115","tagNum":"17657806299833"},
{"action":"OFFLOAD from HKG","at":"HKG","flightNo":"BM115","tagNum":"17657806299833"}
]
}使用 QueryRequest API 的 SQL 表示式範例
您可以使用 QueryRequest API，並使用「SQL 表示式」篩選 NoSQL 表格的資料。
若要執行查詢，請使用 NoSQLHandle.query() API。
Download the full code SQLExpressions.java from the examples here.
//Fetch rows from the table
private static void fetchRows(NoSQLHandle handle,String sqlstmt) throws Exception {
try (
QueryRequest queryRequest = new QueryRequest().setStatement(sqlstmt);
QueryIterableResult results = handle.queryIterable(queryRequest)){
for (MapValue res : results) {
System.out.println("\t" + res);
}
}
}String paran_expr="SELECT fullName, bag.bagInfo.tagNum, bag.bagInfo.routing, "+
"bag.bagInfo[].flightLegs[].fltRouteDest FROM BaggageInfo bag WHERE "+
"bag.bagInfo.flightLegs[].fltRouteSrc=any \"SFO\" AND "+
"(bag.bagInfo[].flightLegs[].fltRouteDest=any \"ATH\" OR "+
"bag.bagInfo[].flightLegs[].fltRouteDest=any \"JTR\" )";
System.out.println("Using Paranthesized expression ");
fetchRows(handle,paran_expr);
String case_expr="SELECT fullName,"+
"CASE WHEN NOT exists bag.bagInfo.flightLegs[0] "+
"THEN \"you have no bag info\" "+
"WHEN NOT exists bag.bagInfo.flightLegs[1] "+
"THEN \"you have one hop\" "+
"WHEN NOT exists bag.bagInfo.flightLegs[2] "+
"THEN \"you have two hops.\" "+
"ELSE \"you have three hops.\" "+
"END AS NUMBER_HOPS "+
"FROM BaggageInfo bag WHERE ticketNo=1762341772625";
System.out.println("Using Case Expression ");
fetchRows(handle,case_expr);
String seq_trn_expr="SELECT seq_transform(l.bagInfo[],"+
"seq_transform("+
"$sq1.flightLegs[],"+
"seq_transform("+
"$sq2.actions[],"+
"{"+
"\"at\" : $sq3.actionAt,"+
"\"action\" : $sq3.actionCode,"+
"\"flightNo\" : $sq2.flightNo,"+
"\"tagNum\" : $sq1.tagNum"+
"}"+
")"+
")"+
") AS actions FROM baggageInfo l WHERE ticketNo=1762376407826";
System.out.println("Using Sequence Transform Expressions ");
fetchRows(handle,seq_trn_expr);若要執行您的查詢，請使用 borneo.NoSQLHandle.query() 方法。
Download the full code SQLExpressions.py from the examples here.
# Fetch data from the table
def fetch_data(handle,sqlstmt):
request = QueryRequest().set_statement(sqlstmt)
print('Query results for: ' + sqlstmt)
result = handle.query(request)
for r in result.get_results():
print('\t' + str(r))paran_expr = '''SELECT fullName, bag.bagInfo.tagNum, bag.bagInfo.routing,
bag.bagInfo[].flightLegs[].fltRouteDest FROM BaggageInfo bag
WHERE bag.bagInfo.flightLegs[].fltRouteSrc=any "SFO" AND
(bag.bagInfo[].flightLegs[].fltRouteDest=any "ATH" OR
bag.bagInfo[].flightLegs[].fltRouteDest=any "JTR" )'''
print('Using Paranthesized expression:')
fetch_data(handle,paran_expr)
case_expr = '''SELECT fullName,
CASE
WHEN NOT exists bag.bagInfo.flightLegs[0]
THEN "you have no bag info"
WHEN NOT exists bag.bagInfo.flightLegs[1]
THEN "you have one hop"
WHEN NOT exists bag.bagInfo.flightLegs[2]
THEN "you have two hops."
ELSE "you have three hops."
END AS NUMBER_HOPS
FROM BaggageInfo bag WHERE ticketNo=1762341772625'''
print('Using Case Expression:')
fetch_data(handle,case_expr)
seq_trn_expr = '''SELECT seq_transform(l.bagInfo[],
seq_transform(
$sq1.flightLegs[],
seq_transform(
$sq2.actions[],
{
"at" : $sq3.actionAt,
"action" : $sq3.actionCode,
"flightNo" : $sq2.flightNo,
"tagNum" : $sq1.tagNum
}
)
)
) AS actions FROM baggageInfo l WHERE ticketNo=1762376407826'''
print('Using Sequence Transform Expressions:')
fetch_data(handle,seq_trn_expr)若要執行查詢，請使用 Client.Query 函數。
Download the full code SQLExpressions.go from the examples here.
//fetch data from the table
func fetchData(client *nosqldb.Client, err error, tableName string, querystmt string)(){
prepReq := &nosqldb.PrepareRequest{
Statement: querystmt,
}
prepRes, err := client.Prepare(prepReq)
if err != nil {
fmt.Printf("Prepare failed: %v\n", err)
return
}
queryReq := &nosqldb.QueryRequest{
PreparedStatement: &prepRes.PreparedStatement, }
var results []*types.MapValue
for {
queryRes, err := client.Query(queryReq)
if err != nil {
fmt.Printf("Query failed: %v\n", err)
return
}
res, err := queryRes.GetResults()
if err != nil {
fmt.Printf("GetResults() failed: %v\n", err)
return
}
results = append(results, res...)
if queryReq.IsDone() {
break
}
}
for i, r := range results {
fmt.Printf("\t%d: %s\n", i+1, jsonutil.AsJSON(r.Map()))
}
}paran_expr := `SELECT fullName, bag.bagInfo.tagNum, bag.bagInfo.routing,
bag.bagInfo[].flightLegs[].fltRouteDest FROM BaggageInfo bag
WHERE bag.bagInfo.flightLegs[].fltRouteSrc=any "SFO" AND
(bag.bagInfo[].flightLegs[].fltRouteDest=any "ATH" OR
bag.bagInfo[].flightLegs[].fltRouteDest=any "JTR" )`
fmt.Printf("Using Paranthesized expression:\n")
fetchData(client, err,tableName,paran_expr)
case_expr := `SELECT fullName,
CASE
WHEN NOT exists bag.bagInfo.flightLegs[0]
THEN "you have no bag info"
WHEN NOT exists bag.bagInfo.flightLegs[1]
THEN "you have one hop"
WHEN NOT exists bag.bagInfo.flightLegs[2]
THEN "you have two hops."
ELSE "you have three hops."
END AS NUMBER_HOPS
FROM BaggageInfo bag WHERE ticketNo=1762341772625`
fmt.Printf("Using Case Expression:\n")
fetchData(client, err,tableName,case_expr)
seq_trn_expr := `SELECT seq_transform(l.bagInfo[],
seq_transform(
$sq1.flightLegs[],
seq_transform(
$sq2.actions[],
{
"at" : $sq3.actionAt,
"action" : $sq3.actionCode,
"flightNo" : $sq2.flightNo,
"tagNum" : $sq1.tagNum
}
)
)
) AS actions FROM baggageInfo l WHERE ticketNo=1762376407826`
fmt.Printf("Using Sequence Transform Expressions:\n")
fetchData(client, err,tableName,seq_trn_expr)若要執行查詢，請使用 query 方法。
JavaScript：從此處的範例下載完整程式碼 SQLExpressions.js 。
//fetches data from the table
async function fetchData(handle,querystmt) {
const opt = {};
try {
do {
const result = await handle.query(querystmt, opt);
for(let row of result.rows) {
console.log(' %O', row);
}
opt.continuationKey = result.continuationKey;
} while(opt.continuationKey);
} catch(error) {
console.error(' Error: ' + error.message);
}
}TypeScript：從此處的範例下載完整的程式碼 SQLExpressions.ts 。
interface StreamInt {
acct_Id: Integer;
profile_name: String;
account_expiry: TIMESTAMP;
acct_data: JSON;
}/* fetches data from the table */
async function fetchData(handle: NoSQLClient,querystmt: any) {
const opt = {};
try {
do {
const result = await handle.query<StreamInt>(querystmt, opt);
for(let row of result.rows) {
console.log(' %O', row);
}
opt.continuationKey = result.continuationKey;
} while(opt.continuationKey);
} catch(error) {
console.error(' Error: ' + error.message);
}
}const paran_expr = `SELECT fullName, bag.bagInfo.tagNum, bag.bagInfo.routing,
bag.bagInfo[].flightLegs[].fltRouteDest FROM BaggageInfo bag
WHERE bag.bagInfo.flightLegs[].fltRouteSrc=any "SFO" AND
(bag.bagInfo[].flightLegs[].fltRouteDest=any "ATH" OR
bag.bagInfo[].flightLegs[].fltRouteDest=any "JTR" )`
console.log("Using Paranthesized expression");
await fetchData(handle,paran_expr);
const case_expr = `SELECT fullName,
CASE
WHEN NOT exists bag.bagInfo.flightLegs[0]
THEN "you have no bag info"
WHEN NOT exists bag.bagInfo.flightLegs[1]
THEN "you have one hop"
WHEN NOT exists bag.bagInfo.flightLegs[2]
THEN "you have two hops."
ELSE "you have three hops."
END AS NUMBER_HOPS
FROM BaggageInfo bag WHERE ticketNo=1762341772625`
console.log("Using Case Expression");
await fetchData(handle,case_expr);
const seq_trn_expr = `SELECT seq_transform(l.bagInfo[],
seq_transform(
$sq1.flightLegs[],
seq_transform(
$sq2.actions[],
{
"at" : $sq3.actionAt,
"action" : $sq3.actionCode,
"flightNo" : $sq2.flightNo,
"tagNum" : $sq1.tagNum
}
)
)
) AS actions FROM baggageInfo l WHERE ticketNo=1762376407826`
console.log("Using Sequence Transform Expressions");
await fetchData(handle,seq_trn_expr);若要執行查詢，您可以呼叫 QueryAsync 方法或呼叫 GetQueryAsyncEnumerable 方法，然後重複產生的非同步列舉項目。
Download the full code SQLExpressions.cs from the examples here.
private static async Task fetchData(NoSQLClient client,String querystmt){
var queryEnumerable = client.GetQueryAsyncEnumerable(querystmt);
await DoQuery(queryEnumerable);
}
private static async Task DoQuery(IAsyncEnumerable<QueryResult<RecordValue>> queryEnumerable){
Console.WriteLine(" Query results:");
await foreach (var result in queryEnumerable) {
foreach (var row in result.Rows)
{
Console.WriteLine();
Console.WriteLine(row.ToJsonString());
}
}
}private const string paran_expr =@"SELECT fullName, bag.bagInfo.tagNum, bag.bagInfo.routing,
bag.bagInfo[].flightLegs[].fltRouteDest FROM BaggageInfo bag
WHERE bag.bagInfo.flightLegs[].fltRouteSrc=any ""SFO"" AND
(bag.bagInfo[].flightLegs[].fltRouteDest=any ""ATH"" OR
bag.bagInfo[].flightLegs[].fltRouteDest=any ""JTR"" )";
Console.WriteLine("\nUsing Paranthesized expression:!");
await fetchData(client,paran_expr);
private const string case_expr =@"SELECT fullName,
CASE
WHEN NOT exists bag.bagInfo.flightLegs[0]
THEN ""you have no bag info""
WHEN NOT exists bag.bagInfo.flightLegs[1]
THEN ""you have one hop""
WHEN NOT exists bag.bagInfo.flightLegs[2]
THEN ""you have two hops.""
ELSE ""you have three hops.""
END AS NUMBER_HOPS
FROM BaggageInfo bag WHERE ticketNo=1762341772625";
Console.WriteLine("\nUsing Case Expression!");
await fetchData(client,case_expr);
private const string seq_trn_expr =@"SELECT seq_transform(l.bagInfo[],
seq_transform(
$sq1.flightLegs[],
seq_transform(
$sq2.actions[],
{
""at"" : $sq3.actionAt,
""action"" : $sq3.actionCode,
""flightNo"" : $sq2.flightNo,
""tagNum"" : $sq1.tagNum
}
)
)
) AS actions FROM baggageInfo l WHERE ticketNo=1762376407826" ;
Console.WriteLine("\nUsing Sequence Transform Expressions!");
await fetchData(client,seq_trn_expr);