{"id":12154,"date":"2022-12-06T09:01:48","date_gmt":"2022-12-06T15:01:48","guid":{"rendered":"https:\/\/www.mrc-productivity.com\/docs\/?post_type=ht_kb&#038;p=12154"},"modified":"2025-02-07T16:58:50","modified_gmt":"2025-02-07T22:58:50","slug":"sql-query-composer","status":"publish","type":"ht_kb","link":"https:\/\/www.mrc-productivity.com\/docs\/knowledge-base\/sql-query-composer","title":{"rendered":"m-Power SQL Composer"},"content":{"rendered":"\n<h2 class=\"wp-block-heading\">Overview<\/h2>\n\n\n\n<p>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.<\/p>\n\n\n\n<figure class=\"wp-block-image size-large\"><a href=\"\/docs\/vue-images\/queryComposer.jpg\"><img decoding=\"async\" src=\"\/docs\/vue-images\/queryComposer.jpg\" alt=\"\"\/><\/a><\/figure>\n\n\n\n<h2 class=\"wp-block-heading\" id=\"video\">Video Walkthrough<\/h2>\n\n\n\n<figure class=\"wp-block-embed is-type-video is-provider-youtube wp-block-embed-youtube wp-embed-aspect-16-9 wp-has-aspect-ratio\"><div class=\"wp-block-embed__wrapper\">\n<iframe title=\"m-Power SQL Composer\" width=\"643\" height=\"362\" src=\"https:\/\/www.youtube.com\/embed\/kgXteil8iyc?feature=oembed\" frameborder=\"0\" allow=\"accelerometer; autoplay; clipboard-write; encrypted-media; gyroscope; picture-in-picture; web-share\" referrerpolicy=\"strict-origin-when-cross-origin\" allowfullscreen><\/iframe>\n<\/div><\/figure>\n\n\n\n<h2 class=\"wp-block-heading\" id=\"templates\">Supporting Templates<\/h2>\n\n\n\n<h4 class=\"wp-block-heading\">Retrievals<\/h4>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Multiple Row Data List<\/li>\n\n\n\n<li>Single Row Data List<\/li>\n\n\n\n<li>JSON Lookup<\/li>\n\n\n\n<li>Option List<\/li>\n<\/ul>\n\n\n\n<h4 class=\"wp-block-heading\">Reports<\/h4>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Regular Report<\/li>\n<\/ul>\n\n\n\n<h2 class=\"wp-block-heading\" id=\"syntax\">m-Power Specific Query Syntaxes<\/h2>\n\n\n\n<p>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. <\/p>\n\n\n\n<div class=\"wp-block-group\"><div class=\"wp-block-group__inner-container is-layout-flow wp-block-group-is-layout-flow\">\n<h3 class=\"wp-block-heading\" id=\"qualifyTable\">Table Qualifications<\/h3>\n\n\n\n<p>In order to utilize any runtime filtering, you must use table aliases in <code>T01<\/code>,<code>T02<\/code>,<code>T03<\/code>, etc. notation. For example:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>SELECT *\nFROM   TABLE1 <mark style=\"background-color:#fffdd0\" class=\"has-inline-color\">T01<\/mark> LEFT\nJOIN   TABLE2 <mark style=\"background-color:#fffdd0\" class=\"has-inline-color\">T02<\/mark> on <mark style=\"background-color:#fffdd0\" class=\"has-inline-color\">T01<\/mark>.FIELD = <mark style=\"background-color:#fffdd0\" class=\"has-inline-color\">T02<\/mark>.FIELD<\/code><\/pre>\n<\/div><\/div>\n\n\n\n<p>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 <code>T01<\/code>, <code>T02<\/code>, <code>T03<\/code>, etc. notation:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>SELECT          <mark style=\"background-color:#fffdd0\" class=\"has-inline-color\">T01<\/mark>.FIELD1,\n                <mark style=\"background-color:#fffdd0\" class=\"has-inline-color\">T01<\/mark>.FIELD2,\n                <mark style=\"background-color:#fffdd0\" class=\"has-inline-color\">T02<\/mark>.FIELD3\nFROM            MYSCHEMA.TABLE1 T01\nLEFT OUTER JOIN MYSCHEMA.TABLE2 T02\nON              T01.ID = T02.ID<\/code><\/pre>\n\n\n\n<div class=\"wp-block-group\"><div class=\"wp-block-group__inner-container is-layout-flow wp-block-group-is-layout-flow\">\n<div class=\"wp-block-group\"><div class=\"wp-block-group__inner-container is-layout-constrained wp-block-group-is-layout-constrained\">\n<h3 class=\"wp-block-heading\" id=\"calcField\">Logical (Calculated) Fields<\/h3>\n\n\n\n<p>If using any computed fields (non-table fields), you must include an SQL alias using the <strong>AS <\/strong>keyword. For example:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>SELECT T01.ONUM,\n       (1+2) <mark style=\"background-color:#fffdd0\" class=\"has-inline-color\">AS<\/mark> \"MYCALC\"<strong> <\/strong>\nFROM   MRCWORKLIB.ORDERHEAD T01<\/code><\/pre>\n<\/div><\/div>\n<\/div><\/div>\n\n\n\n<p>This numeric calculation 1+2 is labeled as &#8220;MYCALC&#8221;. <\/p>\n\n\n\n<p class=\"wp-block-ht-blocks-messages wp-block-hb-message wp-block-hb-message--withicon is-style-danger\">m-Power does not support spaces or special characters in the SQL alias name.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\" id=\"features\">Features and Options<\/h2>\n\n\n\n<div class=\"wp-block-group\"><div class=\"wp-block-group__inner-container is-layout-flow wp-block-group-is-layout-flow\">\n<div class=\"wp-block-group\"><div class=\"wp-block-group__inner-container is-layout-constrained wp-block-group-is-layout-constrained\">\n<h3 class=\"wp-block-heading\"><strong>Append runtime WHERE clause<\/strong><\/h3>\n\n\n\n<p>Appends <code>${where runtime selections}<\/code> to the query as a substitution for any runtime selections the end-user makes. <\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>SELECT T01.FIELD1,\n       T01.FIELD2,\n       T01.FIELD3\nFROM   MYSCHEMA.MYTABLE T01 <code><mark style=\"background-color:#fffdd0\" class=\"has-inline-color\">${where runtime selections}<\/mark><\/code><\/code><\/pre>\n\n\n\n<p class=\"wp-block-ht-blocks-messages wp-block-hb-message wp-block-hb-message--withicon is-style-alert\">This substitution is <strong>required<\/strong> in your query in order for runtime filtering to be used. <\/p>\n<\/div><\/div>\n\n\n\n<div class=\"wp-block-group\"><div class=\"wp-block-group__inner-container is-layout-constrained wp-block-group-is-layout-constrained\">\n<h3 class=\"wp-block-heading\"><strong>Append additional runtime WHERE clauses<\/strong><\/h3>\n\n\n\n<p>Appends <code>${and runtime selections}<\/code> to the query as a substitution for any runtime selections the end-user makes. <\/p>\n\n\n\n<p>The difference between this option, and the above, is that if you have already have a WHERE clause hardcoded into your query, this selection will be need to be used instead to append the additional runtime query filters.<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>SELECT T01.FIELD1,\n       T01.FIELD2,\n       T01.FIELD3\nFROM   MYSCHEMA.MYTABLE T01\nWHERE  T01.FIELD1 = 'someValue'\n<span style=\"background-color: rgba(0, 0, 0, 0); font-size: 11.664px;\"><mark style=\"background-color:#fffdd0\" class=\"has-inline-color\">${and runtime selections}<\/mark><\/span><\/code><\/pre>\n\n\n\n<p class=\"wp-block-ht-blocks-messages wp-block-hb-message wp-block-hb-message--withicon is-style-alert\">This substitution is <strong>required<\/strong> in your query in order for runtime filtering to be used.<\/p>\n<\/div><\/div>\n\n\n\n<div class=\"wp-block-group\"><div class=\"wp-block-group__inner-container is-layout-constrained wp-block-group-is-layout-constrained\">\n<h3 class=\"wp-block-heading\"><strong>Append runtime ORDER BY clauses<\/strong> <\/h3>\n\n\n\n<p>Appends <code>${order by runtime sequences}<\/code> to the query. Adding this substitution will append an <code><strong>ORDER BY<\/strong><\/code> clause when end-users select table columns to re-sort the data at runtime.<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>SELECT T01.FIELD1,\n       T01.FIELD2,\n       T01.FIELD3\nFROM   MYSCHEMA.MYTABLE T01\nWHERE  T01.FIELD1 = 'someValue' \n<code>${and runtime selections} <mark style=\"background-color:#fffdd0\" class=\"has-inline-color\">${order by runtime sequences}<\/mark><\/code><\/code><\/pre>\n\n\n\n<p>The <code>${order by runtime sequences}<\/code> also has an additional significance when utilized within the <strong>Regular Report<\/strong> template. This syntax must be used to perform subtotaling and grand totaling of numeric values at runtime.<\/p>\n\n\n\n<p class=\"wp-block-ht-blocks-messages wp-block-hb-message wp-block-hb-message--withicon is-style-alert\">If wanting to subtotal by any Dimension field(s) in the Regular Report, including this substitution is <strong>required<\/strong>.<\/p>\n<\/div><\/div>\n<\/div><\/div>\n\n\n\n<h3 class=\"wp-block-heading\" id=\"fieldSub\">Field Substitutions<\/h3>\n\n\n\n<p>Insert field substitutions to include user searches in the query at runtime by using the <code>${FIELD}<\/code> syntax:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>SELECT T01.ONUM,\n       T01.CNUM,\n       T01.OORDERDATE,\n       T01.OAMOUNT\nFROM   MRCWORKLIB.dbo.ORDERHEAD T01\n{{WHERE T01.CNUM = ${CNUM}}}<\/code><\/pre>\n\n\n\n<p class=\"wp-block-ht-blocks-messages wp-block-hb-message wp-block-hb-message--withicon is-style-info\">For Multiple Row Data List retrievals, use <code>${val_FIELD}<\/code> to substitute a specific runtime field value from the selections screen in the WHERE clause of your query.<\/p>\n\n\n\n<p>For reports specifically, this can additionally include any runtime prompt filters created by the developer by using the <code>${R00X}<\/code> syntax.<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>SELECT T01.ONUM,\n       T01.CNUM,\n       T01.OORDERDATE,\n       (\n              SELECT SUM(T02.OLPRICE * T02.OQTY)\n              FROM   ESPIMSSQLNEWMP.dbo.ORDERDET AS T02\n              WHERE  T01.ONUM = T02.ONUM {{AND T02.PNUM = <mark style=\"background-color:#fffdd0\" class=\"has-inline-color\">${R001}<\/mark>}}) AS LineTotal\nFROM   MRCWORKLIB.dbo.ORDERHEAD T01<\/code><\/pre>\n\n\n\n<p>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.  <\/p>\n\n\n\n<p class=\"wp-block-ht-blocks-messages wp-block-hb-message wp-block-hb-message--withicon is-style-danger\"><code>${where runtime selections}<\/code> is not compatible with <code>${R00X}<\/code>. They will need to be used separately.<\/p>\n\n\n\n<div class=\"wp-block-group is-vertical is-layout-flex wp-container-core-group-is-layout-8cf370e7 wp-block-group-is-layout-flex\">\n<h3 class=\"wp-block-heading\" id=\"conditionalBrackets\">Conditional Brackets<\/h3>\n\n\n\n<p>As the examples above show, if using field substitutions and it is possible the substitution will be blank at some point during runtime, you will need to wrap the substitution in double curly braces to prevent an error. <\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>... <mark style=\"background-color:#fffdd0\" class=\"has-inline-color\">{{<\/mark>WHERE T01.CNUM = ${R001}<mark style=\"background-color:#fffdd0\" class=\"has-inline-color\">}}<\/mark> <\/code><\/pre>\n\n\n\n<p>In the above example, if <code>${R001}<\/code> is not passed into the query, the contents between the double curly braces (highlighted in yellow) is omitted from the query when executed. <\/p>\n<\/div>\n\n\n\n<div class=\"wp-block-group is-vertical is-layout-flex wp-container-core-group-is-layout-8cf370e7 wp-block-group-is-layout-flex\">\n<div class=\"wp-block-group\"><div class=\"wp-block-group__inner-container is-layout-flow wp-block-group-is-layout-flow\">\n<h3 class=\"wp-block-heading\" id=\"testQuery\">Test Query<\/h3>\n\n\n\n<p>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&#8217;s metadata where you can edit as needed in Field Settings. <\/p>\n\n\n\n<p class=\"wp-block-ht-blocks-messages wp-block-hb-message wp-block-hb-message--withicon is-style-alert\">When using field substitutions, you will need to provide a default value when using the &#8220;Test Query&#8221; button. This ensures a successful query call without missing substitutions values.<\/p>\n\n\n\n<figure class=\"wp-block-image size-large is-style-default\"><a href=\"\/docs\/vue-images\/defaultParms.jpg\"><img decoding=\"async\" src=\"\/docs\/vue-images\/defaultParms.jpg\" alt=\"\"\/><\/a><\/figure>\n<\/div><\/div>\n<\/div>\n\n\n\n<h3 class=\"wp-block-heading\" id=\"disableCount\">Disable Runtime Count<\/h3>\n\n\n\n<p>m-Power&#8217;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.<\/p>\n\n\n\n<h3 class=\"wp-block-heading\" id=\"disablePaging\">Disable Pagination<\/h3>\n\n\n\n<p><strong>Retrievals only<\/strong> &#8212; m-Power includes record pagination by default and limits the number of rows per page by appending a <strong>LIMIT, TOP <\/strong>or <strong>FETCH<\/strong> clause to the SQL query. This may be incompatible with the custom query and in many cases may be best to disable this feature.<\/p>\n\n\n\n<p>If pagination is disabled, the paging arrows at runtime are hidden.<\/p>\n\n\n\n<p class=\"wp-block-ht-blocks-messages wp-block-hb-message wp-block-hb-message--withicon is-style-alert\">When pagination is disabled, the full\/unlimited dataset will return to the table at runtime.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\">Additional Notes<\/h2>\n\n\n\n<ul class=\"wp-block-list\">\n<li>m-Painter Option Lists do not support &#8220;Use Current App Data&#8221; as the source to populate lists when using the SQL Composer as the application data model.<\/li>\n\n\n\n<li>This feature can be disabled for developers from Admin -&gt; Developers -&gt; Select the developer -&gt; User Privileges. Uncheck &#8220;Access SQL Composer&#8221; from the Data Management section.<\/li>\n<\/ul>\n\n\n\n<p><\/p>\n","protected":false},"excerpt":{"rendered":"<p>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&#8230;<\/p>\n","protected":false},"author":1,"comment_status":"closed","ping_status":"closed","template":"","format":"standard","meta":{"footnotes":""},"ht-kb-category":[266],"ht-kb-tag":[],"class_list":["post-12154","ht_kb","type-ht_kb","status-publish","format-standard","hentry","ht_kb_category-general"],"_links":{"self":[{"href":"https:\/\/www.mrc-productivity.com\/docs\/wp-json\/wp\/v2\/ht-kb\/12154","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.mrc-productivity.com\/docs\/wp-json\/wp\/v2\/ht-kb"}],"about":[{"href":"https:\/\/www.mrc-productivity.com\/docs\/wp-json\/wp\/v2\/types\/ht_kb"}],"author":[{"embeddable":true,"href":"https:\/\/www.mrc-productivity.com\/docs\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.mrc-productivity.com\/docs\/wp-json\/wp\/v2\/comments?post=12154"}],"version-history":[{"count":41,"href":"https:\/\/www.mrc-productivity.com\/docs\/wp-json\/wp\/v2\/ht-kb\/12154\/revisions"}],"predecessor-version":[{"id":14449,"href":"https:\/\/www.mrc-productivity.com\/docs\/wp-json\/wp\/v2\/ht-kb\/12154\/revisions\/14449"}],"wp:attachment":[{"href":"https:\/\/www.mrc-productivity.com\/docs\/wp-json\/wp\/v2\/media?parent=12154"}],"wp:term":[{"taxonomy":"ht_kb_category","embeddable":true,"href":"https:\/\/www.mrc-productivity.com\/docs\/wp-json\/wp\/v2\/ht-kb-category?post=12154"},{"taxonomy":"ht_kb_tag","embeddable":true,"href":"https:\/\/www.mrc-productivity.com\/docs\/wp-json\/wp\/v2\/ht-kb-tag?post=12154"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}