このトピックでは、テーブル関数とテーブル関数パラメータとは何か、そしてそれらがInvantive UniversalSQL内でどのように適用されるかを学びます。Invantive UniversalSQLの背後にある概念についての詳細は、UniversalSQL文法で確認できます。
Invantive UniversalSQLには3種類のテーブルがあります:
- テーブル(tables):名前を指定することで取得できるデータの行のリスト
- ビュー(views):名前を指定することで取得できるデータの行のリスト
- テーブル関数(table functions):名前とパラメータの値を組み合わせて指定することで取得できるデータの行のリスト
テーブルとビューは一般的にProjectsのような英語の複数形の名前を持ちます。テーブル関数は一般的に英語の複数形の名前に、接尾辞Byと最も重要なパラメータが続く形式を持ちます。
Invantive Query Tool
Invantive Query Toolでは、この3種類は名前の前にあるアイコンで見分けることができます:
テーブル:
![]()
ビュー:
![]()
テーブル関数:
![]()
Invantive Cloud SQL Editor
Invantive CloudのSQL Editorでも、この3種類を見分けることができます:
テーブル:
![]()
ビュー:
![]()
テーブル関数:
![]()
テーブルとビューの違い
テーブルは、その全体または大部分が背後にあるデータコンテナから1対1で取得されるデータです。Invantive UniversalSQLドライバーは、テーブルの内容を取得するために、データコンテナが期待するプロトコルに従ってリクエストを送信し、データを受け取ります。場合によっては、Exact OnlineのDivisionLabelのように、派生データを持つ限られた数の列が追加されることがあります。
ビューはテーブルと同じ構造を持ちますが、その内容はテーブルや他のビューに基づくSQLクエリによって導き出されます。したがって、各ビューの内容は、最終的にテーブルの内容を組み合わせることによって生成されます。
テーブルに基づくシンプルなビューの例は以下の通りです:
select gat.id
, gat.code
from exactonlinerest..glaccounts gat
where gat.code not like '4%'
and gat.code not like '5%'
and gat.code not like '6%'
and gat.code not like '7%'
and gat.code not like '8%'
and gat.code not like '9%'
このビューは、Brouwer方式の勘定科目表において、貸借対照表項目の一意のIDとコードのみを表示します。
テーブル関数
テーブル関数は、少し特殊な存在です。
テーブル関数は、従来の意味での(“セット”)データの集合ではありません。テーブル関数は、多数のデータ集合を内包しています。数学関数のようにパラメータに値を指定することで、クエリで利用可能なデータ集合が初めて得られます。
パラメータの異なる組み合わせによって返される集合は、背後にある関数の設計者が必要と考える通りに、互いに素であったり、重複したり、完全に同一であったりする可能性があります。
なぜテーブル関数が存在するのか?
Invantive UniversalSQLでは、より手続き型志向のAPIからのデータもSQLによる処理のために利用可能にするために、テーブル関数が多用されています。
例えば、TeamleaderはそのV1とV2の両方のAPIにおいて、特定の1人の連絡先のすべてのフィールド(カスタムフィールドの値を含む)を取得するためのAPIを持っています。Teamleader V1では、以下のように行います:
-- とりあえず最初のIDを取得する(Teamleader V1では10進数)。
select *
from v1.contacts
limit 1
select *
from v1.contact(10814620)
そしてTeamleader V2のAPIでは以下のように行います:
-- とりあえず最初のIDを取得する(Teamleader V2ではGUID)。
select id
from v2.contacts
limit 1
select *
from v2.contactbyid('57da4314-d89a-0d9f-8771-f8c921391d74')
テーブル関数パラメータ
各テーブル関数には、値を指定できる1つ以上のパラメータがあります。パラメータは、数値、日付、BLOB、テキストなど、さまざまなデータ型を持つことができます。
パラメータの値は全体的または部分的に必須である場合があり、startDate < endDateのようにパラメータ値同士に相互関係がある場合もあります。
テーブル関数パラメータの一覧とドキュメントは、以下のExact OnlineのAccountancy Profiles APIの例のように、Invantive Query Toolでテーブル関数名を選択することで確認できます:
テーブル関数パラメータには、位置指定または名前指定で値を与えることができます。位置指定を使用する場合は、指定する必要がある最初のパラメータから最後のパラメータまで、すべてのパラメータ値を指定する必要があります。名前指定では、特定のパラメータにのみ値を割り当てることができます。
位置指定の例は以下の通りです:
select *
from BalancesEx@eol(10523, 2022, 3)
名前指定の例は以下の通りです:
select *
from BalancesEx@eol(Params_VorigeJaren => 3)
位置指定と名前指定を組み合わせることも可能です。以下の例では、10523が最初のパラメータ(Deling)に、3が3番目のパラメータ(Params_PrevYears)に割り当てられています:
select *
from BalansenEx@eol
( 10523
, Params_PrevYears => 3
)
企業ごとのデータのようにパーティション化されたデータを持つプラットフォームでは、異なるcompany_code、division、またはoffice_codeを指定することで、選択された企業ごとにクエリが1回ずつ実行されることに注意してください。選択された企業のリストは、Administratie(s) selecteren voor een Invantive Cloud database に記載されているuseステートメントで指定できます。
ドキュメント
テーブル関数パラメータは、該当するプラットフォームのオンラインドキュメントでも確認できます。例えば、Teamleaderのテーブル関数ContactByIdについては以下の通りです:
テーブル関数パラメータ
以下のパラメータは、テーブル関数ContactByIdの動作を制御するために使用できます。必須パラメータには常に値を指定する必要がありますが、オプションのパラメータには一般的に値を指定する必要はなく、実行時には事前定義された動作がデフォルトとして使用されます。値は位置または名前によって指定できます。どちらの場合も、指定されていないすべてのパラメータはデフォルト値で処理されます。
位置による値の指定は、必要な最初の値から最後の値までをすべて列挙することで行います。例えば、4つのパラメータを持つテーブルに対してselect * from table(value1, value2, value3)とすると、4番目のパラメータにはデフォルト値が使用され、最初の3つには指定された値が使用されます。
名前による値の指定は、値を必要とするすべての値を列挙することで行います。例えば、同じテーブルに対してselect * from table(name1 => value1, name3 => value3)とすると、2番目と4番目のパラメータにはデフォルト値が使用され、最初と3番目には指定された値が使用されます。
データディクショナリ
この情報は、ビューSystemTableFunctionParametersを使用してデータディクショナリでも取得できます:
select name
, presence_required
, database_data_type
, dotnet_data_type
, documentation
from systemtablefunctionparameters@datadictionary
where table_name = 'ContactById'
order
by sorting_order
テーブル関数とPower BI
少なくとも1つのパラメータに必ず値が必要なテーブル関数は、現時点ではPower BIに公開されていません。これは、Power BIがパラメータ値を指定したり、多数のパラメータ値の組み合わせを反復処理したりするための便利な方法をまだ持っていないためです。Invantiveの知識を持つ経験豊富なPower BIコンサルタントは、例えばインメモリテーブルを介して自分自身でデータを取得することができます。
ビューとPower BI
多くのテーブル関数には、Power BIやData Replicatorでの使用のために、LoketのPayrollAdministrationPayrollRunPeriodResultsのようなビューが追加されています。これらのビューは最も一般的な情報を返しますが、数十万件のAPI呼び出しを実行する必要がある場合があるため、実行に時間がかかるという欠点があります。したがって、大量のデータに対してはクエリを最適化する必要がありますが、このステップは、ビューが必要な情報を確実に含んでいることが明らかになってから行うことを推奨します。
一部のプラットフォームはリレーショナル理論に近い形を取っており、フィルタを渡すことができ、すべての可能性を反復処理する必要がないフォーマットでデータを提供します。他のプラットフォームは、例えば特定の実務上の問題に対する既存のソリューションからAPIが発展してきたことなどにより、より伝統的なAPIのアプローチを採用しており、その結果、Invantive UniversalSQL上では非常に多くのテーブル関数と少数のテーブルを持っています。
Power BIとInvantive Data Replicatorでは、データがテーブル形式で利用可能であることが望ましいです。利用可能なビューがあれば、それがより容易になります。この例として、Exact Online SalarisのビューPayrollComponentsが挙げられます。
このビューPayrollComponentsは、すべての雇用条件グループを反復処理し、対応する給与コンポーネントを取得します:
select pct.*
from EmploymentConditionGroups ecp
join ExactOnlineREST..PayrollComponentsByEmploymentConditionGroupCode(ecp.Division, ecp.Code) pct
ビューExactOnlineREST.Payroll.PayrollComponentsにはパラメータがないため、データはすぐに利用可能になります:
付属のビューは、データディクショナリビューSystemViewsで確認できます:
select QUERY
from SYSTEMVIEWS@DataDictionary
where name = 'PayrollComponents'
ビューの種類
Invantive製品には2種類のビューがあります:
- プロバイダー固有のビュー:これらはInvantiveによって作成・保守されます。ユーザーによる変更はできず、単一のデータコンテナのデータのみを組み合わせます。
- 独自ビュー:これらはユーザーによって、例えばデータベースのStartup SQLを通じて作成されます。独自ビューは、複数のデータコンテナのデータを組み合わせることができます。Eigen database-views gebruiken voor SQL en BI-tools も参照してください。
新しいプロバイダー固有のビューが必要な場合は、フォーラムトピックを作成してください。その際、仕様の基礎となる独自のビュー定義をあらかじめ追加しておくと最も便利です。
ビュー内でのテーブル関数のパフォーマンス
通常のテーブルから異なるパラメータを使ってテーブル関数を繰り返し呼び出すビューは、かなり遅くなることがあります。特にTeamleaderのように呼び出し速度が制限されているプラットフォームでは、ビューからすべてのデータを取得するのが非常に遅くなることがあります。例えばTeamleaderでは、呼び出し回数の制限と、1回の呼び出しで複数の行を返す代替手段がないことにより、V1FlatおよびV2Flatビューを使用した場合、1秒あたり1行しか取得できません。
他のプラットフォームにおけるテーブル関数
他のハイエンドSQLプラットフォームにも、Invantive UniversalSQLのテーブル関数に類似した概念があります:
- Oracle:
SELECT * FROM TABLE(f()) - SQL Server:
select * from myfunctionname(10, 'CODE')のようなインラインテーブル値関数(inline table-value functions) - SAP HANA:
select * from "SCHEMA"."TMP::WORKDAYS"('2003-01-01', '2003-02-01') - Teradata:
select * from table(getStoreData(9005))
構文はInvantive UniversalSQLとは異なる場合がありますが、パラメータを伴う関数呼び出しをデータソースとして使用できるという基本は変わりません。

