SQL 式参照
SQL式は、様々なデータ統合の目的に使用できます。サポートされるSQL言語機能は、SQL文が実行されるシステムによって異なります。例:
- ステージングにインポートされたデータに対するクエリは、すべての SQL 言語機能を使用できます。
- NetSuiteなどの外部システム向けのクエリでは、SQL フィルターを活用して機能のセットを減らすことができます。
データ ソースの種類に固有の制限事項と使用上の注意については、以下のリンクを参照してください。
- NetSuiteSQL フィルター
- スプレッドシート SQL フィルタ
- JDBCSQL フィルター
- SalesforceSQL フィルター
- IntacctSQL フィルター
- Microsoft Dynamics GPSQL フィルター
リテラル値
固定の DateTime 値やリテラル値を使用した式にある不変文字列/数などの埋め込み定数。
| データ タイプ | 構文 | 説明 | 使用例 |
|---|---|---|---|
| テキスト | '*****' | シングルクォートで囲まれたテキスト文字列。テキスト内にシングルクォートを含めるには、 2 番目のシングルクォートを使用してエスケープする必要があります |
'It''s hot outside' |
| 整数 | # | 表示される通り正確に入力されています | 999 |
| 浮動小数 | #.# | 小数部分が0であっても常にフロート値にピリオド（.）を入れる必要があります | 7.7 |
| DateTime | TIMESTAMP '*****' | キーワード TIMESTAMP の後に、シングルクォート、一定の長さの、yyyy-mm-dd 形式の日付 の表示 hh:mm:ss.SSS |
TIMESTAMP '20130102 03:04:05.006' |
| 日付 | DATE '*****' | キーワード TIMESTAMP の後に、シングルクォート、一定の長さの、日付の表示 の表示 |
DATE '20130102' |
| ブール値 | TRUE（または FALSE） |
キーワード TRUE またはキーワード FALSE | FALSE |
演算子
数値演算子は、数値（整数、フロート、ビット）の式、値または列に対して数学的演算を実行します。テキスト演算子は、2 つ以上のテキスト式、値、または列を結合します。
| 適用対象 | オペレータ | 説明 | 使用例 |
|---|---|---|---|
| 数字 | + | 2 つの数値を加算します | MyNumericColumn + 1000 |
| 数字 | - | 左側の値から右側の値を引きます | MyNumericColumn 1000 |
| 数字 | / | 左側の値を右側の値で割ります | MyNumericColumn / 1000 |
| 数字 | * | 2 つの値を掛け合わせます | MyNumericColumn * 1000 |
| テキスト | || | 2 つのテキスト値を連結します | MyTextColumn || ' a suffix' |
比較式と論理式
これらの式は 1（true）または 0（false）に解決され、テーブル結合式または CASE WHEN [expr] THEN [value] END 比較の [expr] として使用できます。比較式および論理式は、テキスト、数値または DateTime の列で演算できます
| 適用対象 | 構文 | 説明 | 使用例 |
|---|---|---|---|
| 任意 | = | 2 つの値または式が等しいかどうかをチェックします | MyColumn1 = MyColumn2 |
| 任意 | <> | 2 つの値または式が等しくないかどうかをチェックします | MyColumn1 <> MyColumn2 |
| 任意 | IS NULL | 値または式が NULL かどうかをチェックします。NULL は空の文字列と等価ではありません | MyColumn1 IS NULL |
| 任意 | IS NOT NULL | 値または式が nonNULL 値に解決されるかどうかをチェック します |
MyColumn1 IS NOT NULL |
| 任意 | < | 左側の値または式が右側の値または式より小さいかどうかをチェックします | MyColumn1 < MyColumn2 |
| 任意 | <= | 左側の値または式が右側の値または式以下かどうかをチェックします | MyColumn1 <= MyColumn2 |
| 任意 | > | 左側の値または式が右側の値または式より大きいかどうかをチェックします | MyColumn1 > MyColumn2 |
| 任意 | >= | 左側の値または式が右側の値または式以上かどうかをチェック します |
MyColumn1 >= MyColumn2 |
| 任意 | IN | 値または式がセット内に含まれているかどうかをチェックします | MyColumn1 IN (1, 2, 3) |
| 任意 | NOT IN | 値または式がセット内に含まれていないかどうかをチェックします | MyColumn1 NOT IN (1, 2, 3) |
| テキスト | LIKE | テキスト値または式がパターンに一致するかどうかをチェックします。% 文字は ワイルドカードとして動作します |
MyColumn1 LIKE '%Apple' |
| テキスト | NOT LIKE | テキスト値または式がパターンに一致しないかどうかをチェックします。% 文字はワイルドカードとして動作します | MyColumn1 NOT LIKE '%Apple' |
| 比較 | AND | 2 つの比較を評価し、両方の式が真である場合にのみ true を返します | MyColumn1 >= MyColumn2 AND MyColumn1 IN (1, 2, 3) |
| 比較 | または | 2 つの比較を評価し、いずれかの式が真である場合に true を返します | (MyColumn1 >= MyColumn2) OR MyColumn1 IN (1, 2, 3) |
スカラ関数
スカラ関数は入力値を受け取り、単一の値を返します
| 構文 | 説明 | 使用例 |
|---|---|---|
| ビット関数 | ||
| CAST(expr AS BIT) | テキスト/フロート/ビット値をビット（0または1）値に変換します | CAST('1' AS BIT) => 1 |
| 整数関数 | ||
| CAST(expr AS INTEGER) | テキスト/フロート/ビット値を整数値に変換します | CAST('2' AS INTEGER) => 2 |
| TIMESTAMPDIFF([datepart] FROM [datetime_expr1] TO [datetime_expr2]) | [datetime_expr1] から [datetime_expr2] までの [datepart]（DAY）の数を取得します |
TIMESTAMPDIFF(DAY FROM TIMESTAMP '2013-02-01 00:00:00.000' TO TIMESTAMP '20130210 00:00:00.000') => 9 |
| DATEDIFF([datepart] FROM [date_expr1] TO [date_expr2]) | [datetime_expr1] から [datetime_expr2] までの [date_expr1] to [date_expr2] |
DATEDIFF(DAY FROM DATE '2013-02-01' TO DATE '2013-02-10') => 9 |
| EXTRACT([datepart] FROM [datetime_expr]) | [datetime_expr] から [datepart]（年/月/日/時/分/秒）を取得します | EXTRACT(MONTH FROM DATE '2013-02-01') => 2 |
| LENGTH([text_expr]) | [text_expr] の長さを取得します | LENGTH('Hello') => 5 |
| POSITION([find_text_expr] IN [search_text_expr]) | [search_text_expr] の最初のインデックスを 取得します。最初の文字は 1 です。 |
POSITION('at' IN 'hat') => 2 |
| POSITION([find_text_expr] IN [search_text_expr] FROM [start]) | [search_text_expr] の最初のインデックスを [start] インデックスの後の [search_text_expr]（1の [start] は 最後を見つけることを意味します）。最初の文字は 1 です。 |
POSITION('a' IN 'a hat' FROM 1) => 4 |
| フロート関数 | ||
| CAST(expr AS FLOAT) | テキスト/整数/ビット値をフロート値に変換します | CAST('1.01' AS FLOAT) => 1.01 |
| テキスト関数 | ||
| CAST(expr AS NVARCHAR) | フロート/整数/ビット値をテキスト値に変換します | CAST(1.01 AS NVARCHAR) => '1.01' |
| TRIM([text_expr]) | [text_expr] から先頭と末尾のスペースを削除します | TRIM(' xxx ') => 'xxx' |
| SUBSTRING([text_expr] FROM [start_int_expr]) |
[text_expr] の一部を [start_int_expr] の位置 から抽出します。最初の文字はポジション 1 です |
SUBSTRING('aaabbbccc' FROM 3) => 'abbbccc' |
| SUBSTRING([text_expr] FROM [start_int_expr] FOR [len_int_expr]) |
ポジション [start_int_expr] から [text_expr] の [len_int_expr] 文字を抽出します。最初の文字はポジション 1 です | SUBSTRING('aaabbbccc' FROM 3 FOR 3) => 'abb' |
|
REPLACE([text_expr_1] WITH [text_expr_2] IN [text_expr_3]) カスタムSQL列でのみ使用可能 |
[text_expr_3] における [text_expr_1] のすべてのインスタンスを [text_expr_2] の値に置き換えます。 | REPLACE('z' WITH 'a' IN 'zba') => 'aba' |
|
REGEX_REPLACE([text_expr_1] WITH [text_expr_2] IN [text_expr_3]) カスタムSQL列でのみ使用可能 |
[text_expr_3] において通常の式 [text_expr_1] に一致するすべてのインスタンスを [text_expr_2] に置き換えます。 | REGEX_REPLACE('[0-9]' WITH 'a' IN 'a7b5c') => 'aabac' |
|
SPLIT_PART([string],[delimiter],[part]) カスタムSQL列でのみ使用可能 |
特定の文字によって文字列を区切り、指数で設定した値を選択します。これにより、あるパターンのnth発生が抽出され、それが返されます。最初の要素は1から始まります。インデックスが範囲外の場合、式は空の文字列を返します。 | SPLIT_PART('Plan|1991|Tennis', '|', 2) =>'1991' SPLIT_PART('Plan|1991|Tennis', '/', 1) => 'Plan|1991|Tennis' SPLIT_PART('Plan|1991|Tennis', '/', 2) => '' |
|
TO_ACCOUNT_CODE([text_expr_1]) カスタムSQL列でのみ使用可能 |
値がプランニング科目コード フィールドと互換性があることを確認します。すべてのスペースを削除し、英数字以外の文字をアンダースコアに置き換え、2048文字を超える値を切り捨てます | TO_ACCOUNT_CODE('A - 860+') => 'A_860' |
| DateTime 関数 | ||
| CAST([text_expr] AS TIMESTAMP FROM '[timestamp_format]') |
既知の構造体/形式のテキスト値を DateTime 値に変換します。特定の [timestamp_format] 値のみが許可されます（下記を参照してください） | CAST('20130102' AS TIMESTAMP FROM 'yyyy-mm-dd') => TIMESTAMP '2013-01-02 00:00:00.000' |
| TRUNCATE_TIMESTAMP([datetime_part] FROM [datetime_expr]) |
DateTime [datetime_expr] をグレゴリオ暦 [datetime_part] の YEAR/MONTH/DAY/HOUR に短縮します |
TRUNCATE_TIMESTAMP(MONTH FROM TIMESTAMP '2013-11-22 12:13:14.015') => TIMESTAMP '2013-11-01 00:00:00.000' |
| 日付関数 | ||
| CAST([text_expr] AS DATE FROM '[date_format]') |
既知の構造体/形式のテキスト値を日付値に変換します。特定の [date_format] 値のみが許可されます （下記を参照してください） |
CAST('2013-01-02' AS DATE FROM 'yyyy-mm-dd') => DATE '2013-01-02 00:00:00.000' |
| TRUNCATE_DATE([date_part] FROM [date_expr]) |
日付 [date_expr] をグレゴリオ暦 [date_part] の YEAR/MONTH/DAY/HOUR に短縮します |
TRUNCATE_DATE(MONTH FROM DATE '2013-11-22') => DATE '2013-11-01 00:00:00.000' |
時定数は、
IntegrationSQL 式で使用できる現在の日付と時刻に関連する以下の 2 つの定数をサポートしています。
| 構文 | 説明 | 使用例 |
|---|---|---|
| CURRENT_TIMESTAMP | 現在の DateTime を取得し、DateTime オブジェクトが使用されているすべての場所で使用できます。 | EXTRACT(YEAR FROM CURRENT_TIMESTAMP) |
| CURRENT_DATE | 現在の日付 を取得し、日付 オブジェクトが使用されているすべての場所で使用できます。 | (DATEDIFF(DAY FROM CURRENT_DATE TO [column_reference])) <= 30 |
CASE 文
Case 文は、多くの言語の if 文と同様に、他の値に基づいて値を選択するのに使用されます
| 構文 | 説明 | 使用例 |
|---|---|---|
| CASE WHEN [logic_expr1] THEN [result_expr1] WHEN [logic_expr#] THEN [result_expr#] ELSE [result_expr_def] END | true を返す最初の [logic_expr] の [result_expr] が返されます |
CASE WHEN 1>2 THEN 'x' ELSE 'y' END => 'y' |
| CASE [expr] WHEN [expr1] THEN [result_expr1] WHEN [expr#] THEN [result_expr#] ELSE [result_expr_def] END | 最初の [expr#] から [expr] に等しい [result_expr] が返されます |
CASE 2 WHEN 1 THEN 'x' ELSE 'y' END => 'y' |
COALESCEステートメント
Coalesceは、引数を順番に評価し、定義された引数リストから最初の非ヌル値を返します。CoalesceはSQL列、ローダ内のSQLフィルター、および結合式で使用できます。Coalesceは、ステージング テーブルのインポート フィルターでは使用できません。
| 構文 | 説明 | 使用例 |
|---|---|---|
| COALESCE ([expr]) | [expr] で最初の非ヌルを返します | COALESCE (NULL,NULL,20,NULL,NULL,10) => 20 |
テーブル関係式
[Table Relationship] 項目を使用してテーブルを結合する場合、結合式を指定する必要があります。結合されている 2 つのテーブルに同じ名前の列がある可能性があります。この場合、主テーブルの列は「P.」を使用して修飾し（例: P."MyColumn"）、関連テーブルは「R.」(例: R."MyColumn")を使用できます。
次のテーブルは、CAST（[text_expr] AS TIMESTAMP FROM '[timestamp_format]'）関数で使用できる [timestamp_format] 値の詳細です
| フォーマット |
|---|
| [mon dd yyyy hh:mitt] |
| [mm/dd/yyyy] |
| [yyyy.mm.dd] |
| [dd/mm/yyyy] |
| [dd.mm.yyyy] |
| [dd-mm-yyyy] |
| [dd mon yyyy] |
| [mon dd yyyy] |
| [mon dd yyyy hh:mi:ss:mmmmmmtt] |
| [mm-dd-yyyy] |
| [yyyy/mm/dd] |
| [yyyymmdd] |
| [dd mon yyyy hh:mi:ss:mmmmmm] |
| [yy-mm-dd hh:mi:ss] |
| [yy-mm-dd hh:mi:ss.mmmmmm] |
| [yy-mm-ddThh:mi:ss.mmmmmm] |
データ ソース固有のデータインポートフィルター SQL の制限事項および使用上の注意
データ ソース固有のデータインポートフィルター SQL の制限事項および使用上の注意を以下に記載しています。
NetSuite データ ソース テーブル
（NetSuiteからステージングにインポートされた記録をクエリするのではなく）NetSuite を直接クエリする場合、フィルター式はウェブサービスを通じてNetSuite によって公開される機能に限定されます。
- 比較式と論理式を持つ単純な列フィルターは、NetSuiteをクエリする場合に使用できます。
- フィルターは AND で結合できますが、OR で結合することはできません。
- 演算子（+、/、*、$、|| など）は使用できません。
- スカラ関数は使用できません。
- CASE 文は使用できません。
- カスタム列をフィルタリングするには、インポートするカスタム列にマークを付ける必要があります。
- 一部の列フィルターでは、フィルターを動作させるために特定の NetSuite 機能を有効にする必要があります。
- 一部のテーブルおよび一部の列は、フィルタリングをサポートしていません。
スプレッドシート データ ソース テーブル
スプレッドシートファイルを直接クエリする場合は、（スプレッドシートからステージングにインポートされた記録をクエリするのではなく）、フィルター式はクエリ対象のファイルの"アップロード ID" のみを指定できます。"アップロード ID" が指定されていない場合は、最も最近にインポートされたファイルからのデータが表示されます。
JDBCデータ ソース テーブル
- 比較式と論理式を持つ単純な列フィルターは、JDBC データ ソースをクエリする場合に使用できます。
- 演算子（+、/、*、$、|| など）は使用できません。
- スカラ関数は使用できません。
- CASE 文は使用できません。
Salesforce データ ソース テーブル
- 比較式と論理式を持つ単純な列フィルターは、Salesforceをクエリする場合に使用できます。
- 演算子（+、/、*、$、|| など）は使用できません。
- スカラ関数は使用できません。
- CASE 文は使用できません。
Intacct データ ソース テーブル
- 比較式と論理式を持つ単純な列フィルターは、Intacctをクエリする場合に使用できます。これには、IN(..)、IS NULL、IS NOT NULL、LIKE および NOT LIKE 文が含まれます。
- Intacct は演算子 <> をサポートしていない代わりに、NOT IN() の比較を使用しています。
- 演算子（+、/、*、$、|| など）は使用できません。
- スカラ関数は使用できません。
- CASE 文は使用できません。
- ブール値列に対するフィルターでは、true/false キーワードと同じ列を使用する必要があります。この理由は、Intacct は 1/0 を同じ true/false と認識しないからです。
Microsoft Dynamics GPデータ ソース テーブル
- 比較式と論理式を持つ単純な列フィルターは、Microsoft Dynamics GPをクエリする場合に使用できます。
- 演算子（+、/、*、$、|| など）は使用できません。
- スカラ関数は使用できません。
- CASE 文は使用できません。