m-Power SQL Composer

Overview

From time to time, some advanced m-Power users find themselves wanting to use their own custom SQL query when creating an m-Power application. Perhaps there is a complicated SELECT statement or stored procedure that returns a dataset the developer wishes to base their m-Power application over or maybe the developer just wants complete control over the query that builds their application. The m-Power SQL Composer allows advanced users to enter an SQL query and m-Power will parse the statement to generate the necessary fields foregoing the traditional Data Dictionary approach to an m-Power build. Once compiled, m-Painter may be used to further customize the application as normal.

Video Walkthrough

Supporting Templates

Retrievals

• Multiple Row Data List
• Single Row Data List
• JSON Lookup
• Option List

Reports

• Regular Report

m-Power Specific Query Syntaxes

The following query syntaxes are specific to the functionality of the m-Power SQL Composer and must be adhered to in order for the generated application to run successfully.

SELECT *
FROM   TABLE1 T01 LEFT
JOIN   TABLE2 T02 on T01.FIELD = T02.FIELD

The above example selects all fields from TABLE1 and TABLE2. If selecting a handful of fields (rather than all), every selected field will also need to be qualified by the T01, T02, T03, etc. notation:

SELECT          T01.FIELD1,
                T01.FIELD2,
                T02.FIELD3
FROM            MYSCHEMA.TABLE1 T01
LEFT OUTER JOIN MYSCHEMA.TABLE2 T02
ON              T01.ID = T02.ID

SELECT T01.ONUM,
       (1+2) AS "MYCALC" 
FROM   MRCWORKLIB.ORDERHEAD T01

This numeric calculation 1+2 is labeled as “MYCALC”.

Features and Options

SELECT T01.FIELD1,
       T01.FIELD2,
       T01.FIELD3
FROM   MYSCHEMA.MYTABLE T01 ${where runtime selections}

SELECT T01.FIELD1,
       T01.FIELD2,
       T01.FIELD3
FROM   MYSCHEMA.MYTABLE T01
WHERE  T01.FIELD1 = 'someValue'
${and runtime selections}

SELECT T01.FIELD1,
       T01.FIELD2,
       T01.FIELD3
FROM   MYSCHEMA.MYTABLE T01
WHERE  T01.FIELD1 = 'someValue' 
${and runtime selections} ${order by runtime sequences}

Field Substitutions

Insert field substitutions to include user searches in the query at runtime by using the ${FIELD} syntax:

SELECT T01.ONUM,
       T01.CNUM,
       T01.OORDERDATE,
       T01.OAMOUNT
FROM   MRCWORKLIB.dbo.ORDERHEAD T01
{{WHERE T01.CNUM = ${CNUM}}}

For Multiple Row Data List retrievals, use ${val_FIELD} to substitute a specific runtime field value from the selections screen in the WHERE clause of your query.

For reports specifically, this can additionally include any runtime prompt filters created by the developer by using the ${R00X} syntax.

SELECT T01.ONUM,
       T01.CNUM,
       T01.OORDERDATE,
       (
              SELECT SUM(T02.OLPRICE * T02.OQTY)
              FROM   ESPIMSSQLNEWMP.dbo.ORDERDET AS T02
              WHERE  T01.ONUM = T02.ONUM {{AND T02.PNUM = ${R001}}}) AS LineTotal
FROM   MRCWORKLIB.dbo.ORDERHEAD T01

The above query indicates the first (R001) runtime value prompt filter in the report application will have its value passed into the query at runtime.

${where runtime selections} is not compatible with ${R00X}. They will need to be used separately.

... {{WHERE T01.CNUM = ${R001}}} 

Test Query

After a query is entered, you may test the query against your database. Once clicked, the returned dataset will display in a table below the query text box. Additionally, the fields from the query are generated in the application’s metadata where you can edit as needed in Field Settings.

When using field substitutions, you will need to provide a default value when using the “Test Query” button. This ensures a successful query call without missing substitutions values.

Disable Runtime Count

m-Power’s runtime record counter uses the SQL statement to get a count of total records available (for pagination). With custom SQL queries, this may not be compatible and developers may wish to disable the count.

Disable Pagination

Retrievals only — m-Power includes record pagination by default and limits the number of rows per page by appending a LIMIT, TOP or FETCH clause to the SQL query. This may be incompatible with the custom query and in many cases may be best to disable this feature.

If pagination is disabled, the paging arrows at runtime are hidden.

When pagination is disabled, the full/unlimited dataset will return to the table at runtime.

Additional Notes

• m-Painter Option Lists do not support “Use Current App Data” as the source to populate lists when using the SQL Composer as the application data model.
• This feature can be disabled for developers from Admin -> Developers -> Select the developer -> User Privileges. Uncheck “Access SQL Composer” from the Data Management section.