Tools.ExecSql

The Tools.ExecSql provider enables you to write a Luminesce query that constructs SQL using string concatenation, and then executes it. This has many uses, such as allowing you to build generalised queries that can be easily adapted, programmatically build views based on the current state of the system and more. 

Note that whilst Sys.Admin.SetupView is also capable of the above, it is more complex to use for this purpose and has the power to create views, meaning it is likely to only be accessible to a small set of users. Tools.ExecSql only runs SQL that the user can already run, meaning it can be granted to all users without significant concerns.

Note: The LUSID user running the query must have sufficient access control permissions to use this provider. This should automatically be the case if you are the domain owner.

See also: Sys.Admin.SetupView

Basic usage

@@sql = select 'select <column> from <some-provider> where <filter-expression>';

@x = 
use Tools.ExecSql with @@sql
    {@@sql} 
enduse;

select * from @x;

Input tables

Tools.ExecSql takes in at least one scalar variable with optional additional full tables of data, and outputs one table of data containing the executed string of SQL.

Examples

Note: For more example Luminesce queries, visit our GitHub repo.

Example 1: Executing a simple string of SQL

@@sql = select 'select 1 as column1';

@x = use Tools.ExecSql with @@sql
    {@@sql} 
enduse;

select * from @x;

The table of data returned looks like this:

Example 2: Executing a string of SQL containing a table variable

In this example, Tools.ExecSql is used with a #PARAMETERVALUE macro to select data from a table which could vary in shape.

@table_of_data = select 'this' as SomeText, 'that' as MoreText, 10 as SomeNumber;
    
@x = use Tools.ExecSql with @table_of_data
    select * from #PARAMETERVALUE(@table_of_data)
enduse;

select * from @x

The table of data returned by the query looks like this, populated with the #PARAMETERVALUE macro values:

Example 3: Executing a string of SQL containing a #PARAMETERVALUE macro

@data = select 'this' as SomeText, 'that' as MoreText, 10 as SomeNumber;
@@sql = select 'select * from #PARAMETERVALUE(@data)';
    
@x = use Tools.ExecSql with @@sql, @data
    {@@sql}
enduse;

select * from @x

The table of data returned by the query looks like this, populated with the #PARAMETERVALUE macro values:

Example 4: Nesting a Tools.ExecSql statement within a custom view

In this example, we create a custom view which uses Tools.ExecSql to ensure the string of SQL is executed at a specific point in the query. Tools.ExecSql requires no separators and takes in no options, allowing you to easily nest a Tools.ExecSql statement within a larger query. 

Tools.ExecSql also supports being run inside an --allowExecuteIndirectly custom view. This option is also applied to what Tools.ExecSql itself runs. This means you can create a provider that allows some users to interact with other providers they otherwise cannot access, including the use of SQL-as-a-string. 

@x = use Sys.Admin.SetupView
--provider=Views.ExecSql.Nesting
--distinct
--filter=Isin is not null
--limit=42
--allowExecuteIndirectly
----

@@sql = select 'select
  #DISTINCT
  #SELECT { { Isin : Isin } }
  from Lusid.Instrument where
  #RESTRICT { { Isin : Isin } }
#LIMIT';
   
@y = use Tools.ExecSql with @@sql
  {@@sql}
enduse;

select * from @y;

enduse;

select * from @x

Example 5: Passing a provider name into a custom view using Tools.ExecSql

In this example, we create a custom view which takes in a provider name as a parameter. Tools.ExecSql is used to execute some SQL using the table of data from the named provider. The data from this execution is then used with Notification.ManualEvent.Writer to create a new manual event in the Notification Service.

Whilst this example uses Lusid.Portfolio as an input parameter to create a manual event, you might instead choose to pass in the name of a custom view to, for example, perform a data quality check.

@x = use Sys.Admin.SetupView
--provider=Views.ExecSomeSql
--description="Using the Tools.ExecSql provider to pass in a provider name as a custom view input parameter"
--parameters
ViewName,Text,Lusid.Portfolio,false
----

@@ViewName = select #PARAMETERVALUE(ViewName);


@data = use Tools.ExecSql with @@ViewName
select * from {@@ViewName}
    where PortfolioType = 'Transaction';

enduse;

@data_to_write = select 
  PortfolioScope as Subject,
  PortfolioCode as Message 
from @data;

select * from Notification.ManualEvent.Writer
    where ToWrite = @data_to_write;

enduse;

select * from @x