Complex SQL Select statements with before and after SQL

​Normally Datamodels or Custom Tables will define SQL table names and their columns so that can be used to create select statements and also update,insert and delete statements.  These table names which can be real tables or views are used in combination with the columns to produce the final select statement based on whats used in the report.  However you may wish to create your own dataset simular to a SQL view but without creating a view, a sort of inline sql table or virtual table.

This article will first show a simple SQL virtual table wrapping a SQL statement in brackets and then a more complex SQL table that requires a bit of preprocessing to build the dataset before selecting from it.

Simple SQL Virtual Table

Virtual SQL Tables look something like this

select Code,Description from
(
select ‘A100‘ as Code, ‘B100‘ as Description
union
select ‘A200‘ , ‘B200‘
) X

Custom Table showing virtual SQL Table with brackets in the SQL Table Name property

Query Builder – Preview of Virtual Table SQL generated by the Sharperlight Engine

 SQL Preprocessing Table Example

Virtual In this example a SQL Table variable is created and rows are inserted into the Table then a SQL Select statement is run

declare @MyTableVarible table
(
[Code] nvarchar(20),
[Description] nvarchar(20)
)
insert into @MyTableVarible
select [Code],[Description] from
(
select ‘A100‘ as [Code], ‘B100‘ as [Description]
union
select ‘A200‘ , ‘B200‘
) X
select * from @staging-sharperlight-com.stackstaging.comMyTableVarible

Create another Custom Table or edit the example above to extend it.  Edit the Table SQL Name Property

In the Contol Tags lookup do a find on the keyword move and you should see /*MoveSQLBlockToTopBegin*/

SQL Table Name Property

There special tags will move whatever SQL is between the Begin and End to the very top of any SQL statement generated by the engine at runtime.   We are using the SQL Table Name property to place the preprocessing SQL section as a general rule but it could have been in any SQL property.  In the example blow the section will be moved to the top of the SQL statement just leaving the SQL table name at runtime

/*MoveSQLBlockToTopBegin*/
declare @MyTableVarible table
(
[Code] nvarchar(20),
[Description] nvarchar(20)
)
insert into @MyTableVarible
select [Code],[Description] from
(
select ‘A100’ as [Code], ‘B100’ as [Description]
union
select ‘A200’ , ‘B200’
) X
/*MoveSQLBlockToTopEnd*/

@MyTableVarible

Query Builder showing example

Query Builder Preview showing what the final SQL statement looks like