クエリが分からない
motona以下の2つのテーブルがある。
Empテーブル
| Id | Name | DeptId |
|---|---|---|
| 1 | Hanako | 1,2 |
| 2 | Taro | 3 |
Deptテーブル
| Id | Name |
|---|---|
| 1 | 事務 |
| 2 | 営業 |
| 3 | ITサポート |
このテーブルから、こんな感じでレコードを取りたい
| Id | Name | DeptId | DeptName |
|---|---|---|---|
| 1 | Hanako | 1 | 事務 |
| 1 | Hanako | 2 | 営業 |
| 2 | Taro | 3 | ITサポート |
週明けからいろいろ試してみる
motonaNetSuiteに投げるからSuiteQLじゃないといけないけど、先にSQL Serverの方で試してみる
motona試行1：以下のスクリプトを実行
SELECT *
FROM Emp FULL OUTER JOIN Dept ON Emp.DeptId = Dept.Id
結果
失敗。以下のエラーが表示された
メッセージ 245、レベル 16、状態 1、行 1
Conversion failed when converting the nvarchar value '1,2' to data type int.
試行2：IN句にSTRING_SPLIT関数を使う
ON句でもIN句を使えるとのことなので。
以下のスクリプトを実行
SELECT *
FROM Emp FULL OUTER JOIN Dept ON Dept.Id IN STRING_SPLIT(Emp.DeptId, ',')
結果
実行する前から赤の波線も表示されているが、実行すると以下のエラーが表示された
メッセージ 102、レベル 15、状態 1、行 2
Incorrect syntax near 'STRING_SPLIT'.
STRING_SPLIT関数について調べてみる
使い方が悪そうなので。
公式ドキュメントから
戻り値の型
ordinal 出力列が有効ではない場合、STRING_SPLIT は行が部分文字列である単一列のテーブルを返します。 列の名前は value です。 入力引数のいずれかが nvarchar または nchar の場合は、nvarchar が返されます。 それ以外の場合、varchar が返されます。 戻り値の型の長さは、"文字列" 引数の長さと同じです。
enable_ordinal 引数に値 1 が渡された場合、入力文字列内の各部分文字列の位置の 1 から始まるインデックスの値から成る ordinal という名前の 2 番目の列が返されます。 戻り値の型は bigint です。
「単一列のテーブルを返します。」とあるので、IN句よりExists関数を使った方が良さそう？
motona試行3：ON句でExists関数を使う
以下のスクリプトを実行
SELECT *
FROM Emp FULL OUTER JOIN Dept ON Exists(SELECT 1 FROM STRING_SPLIT(Emp.DeptId, ',') arr WHERE arr.value = Dept.Id)
結果
成功。以下のようなデータが取れた
| Id | Name | DeptId | Id | DeptName |
|---|---|---|---|---|
| 1 | Hanako | 1,2 | 1 | 事務 |
| 1 | Hanako | 1,2 | 2 | 営業 |
| 2 | Taro | 3 | 3 | ITサポート |
motonaEmpに登録されていないDeptを表示しないようにする
Deptテーブルに以下のデータを追加する
| Id | Name |
|---|---|
| 4 | 法務 |
この状態で元のクエリを実行すると以下の結果が出力される
| Id | Name | DeptId | Id | DeptName |
|---|---|---|---|---|
| 1 | Hanako | 1,2 | 1 | 事務 |
| 1 | Hanako | 1,2 | 2 | 営業 |
| 2 | Taro | 3 | 3 | ITサポート |
NULL |
NULL |
NULL |
4 | 法務 |
最後の結果は不要なので、FULL OUTER JOINをLEFT OUTER JOINに変更
SELECT *
FROM Emp LEFT OUTER JOIN Dept ON Exists(SELECT 1 FROM STRING_SPLIT(Emp.DeptId, ',') arr WHERE arr.value = Dept.Id)
取りたい結果が取れるようになった
| Id | Name | DeptId | Id | DeptName |
|---|---|---|---|---|
| 1 | Hanako | 1,2 | 1 | 事務 |
| 1 | Hanako | 1,2 | 2 | 営業 |
| 2 | Taro | 3 | 3 | ITサポート |
motona
Emp.DeptIdがNULLや空文字でも問題ないかを確認
Empテーブルに以下のデータを追加
| Id | Name | DeptId |
|---|---|---|
| 3 | Yoshiko | NULL |
| 4 | Takuma |
実行してもエラーは出ず、以下の結果が出力された。
| Id | Name | DeptId | Id | DeptName |
|---|---|---|---|---|
| 1 | Hanako | 1,2 | 1 | 事務 |
| 1 | Hanako | 1,2 | 2 | 営業 |
| 2 | Taro | 3 | 3 | ITサポート |
| 3 | Yoshiko | NULL |
NULL |
NULL |
| 4 | Takuma | NULL |
NULL |
motonaSuiteQLにはSTRING_SPLITにあたるものがなさそう、、
motonaもしかしたらこっちの方が分かりやすいかも
SELECT *
FROM Emp LEFT OUTER JOIN Dept ON Dept.Id IN (SELECT value FROM STRING_SPLIT(Emp.DeptId, ','))
結果の追記
ODEのサポートチームに連絡したらBUILTIN.のメソッドを使って解決してくれた。
結論、ODEのサポートチームに依頼しましょう。