The SQL Wrapper view enables you to view and edit basic information about the overall SQL Wrapper. SQL Wrappers are used to query any information in the LemonEdge platform, and any external data you want to pull into the LemonEdge platform through custom SQL.
SQL Wrapper Features
SQL Wrappers allow you to incorporate any piece of custom SQL into the LemonEdge platform. This can be SQL automatically generated through the system, such as through Datasets or other reporting tools, or through standard system SQL Wrappers, or through custom SQL you want to use in the platform.
The custom SQL allows you to tailor any generated SQL, by simply creating a copy, as granular as you like for performance or other related requirements.
Each SQL Wrapper is responsible for creating a single set of data in any shape you require. The data is always accessible through our results grid, or exports, and has full integration with every aspect of the LemonEdge platform.
If you are trying to union, or otherwise merge, multiple sets of data together you can create each set of data using a separate SQL Wrapper and utilise our other reporting tools such as Grouped Queries or Venn Sets for combining them all.
SQL Wrapper Properties
SQL Wrappers have the following top level properties:
Name
Description
[Required] [Key] Name
The unique user friendly name of this SQL Wrapper
Description
A user friendly description of this SQL Wrapper
Type
The type of sql this SQL Wrapper is wrapping:
Function
Stored Procedure
SQL Name
The actual name of this stored procedure or function in the sql database
Always Use Core SQL
Some queries should always use the core sql always utilising the 4 standard parameters (@accountID, @canvasID, @teamID, @lastUpdated) regardless . See here for more info.
Owner Entity
A link to any entity that owns this SQL Wrapper and is responsible for dynamically generating its SQL such as a Dataset.
Help URL
A unique link to an html help page describing the purpose of this SQL Wrapper
Commands
A SQL Wrapper has the standard set of commands and functions when being viewed from a grid. See our standard grid functionality.
SQL Wrappers can be exported and imported as packaged xml files to transfer configuration across systems. The export includes the SQL Wrapper, and any owning entities (such as Datasets), all protected using our standard versioning. Again this is available from the standards grid commands.
SQL Wrappers also have the following custom commands:
Execute And Export Query Data This executes the SQL Wrapper you've designed and pops-up and parameters you need to enter first. It then executes the query as a task service allowing you to download the results whenever into excel, csv, etc.
Edit Core SQL This is the core sql for your function or stored procedure that is being called by this SQL Wrapper. You must enter sql here for the main function. The Main, As Of, and Canvas SQL can be left blank - they are automatically translated and created from the Core SQL anyway. You can however override them - see here for more info.
Edit Main SQL This is optional and can be left blank, the system will calculate it from the Core SQL. Optionally enter the SQL for your function that doesn't take @canvasID or @lastUpdated parameters, and typically will always be calling our _Main functions against entities.
Edit As Of SQL This is optional and can be left blank, the system will calculate it from the Core SQL. Optionally enter the SQL for your function that doesn't take @canvasID parameter, and typically will always be calling our _AsOf functions against entities.
Edit Canvas SQL This is optional and can be left blank, the system will calculate it from the Core SQL. Optionally enter the SQL for your function that doesn't take @lastUpdated parameter, and typically will always be calling our _Canvas functions against entities.
Load Schema If the sql for your stored procedure or function already exists in the database then the system can attempt to load the schema from the database itself. If it can it will then update your parameters and columns to match the database schema.
