mrc logo mrc logo
  • m-Power m-Power
    What is m-Power?
    Overview Demos Build Process Case Studies Specs Pricing Trial
    m-Power Resources
    Overview How-To Videos Webinars & Podcasts White Papers Fact Sheets
  • Solutions Solutions
    What does m-Power build?
    Overview Database Front-Ends Reporting CRM Systems Business Intelligence Dashboards Inventory Management Mobile Apps ERP Enhancements Modernization Spreadsheets to the web MS Access to the web B2B/Web Portals Scheduling Embedded Analytics Web Forms Workflow Data Exploration Budgeting & Forecasting APIs and Web Services Db2 Web Query Alternative
    Solutions by Industry
    Overview Manufacturing Government Foodservice Software Vendors Logistics & Supply Chain Software Consultants Healthcare
  • Services Services
    Development Services Training Mentoring
  • About About
    Overview Partners Press Releases Careers Events Contact Blog
  • Support Support
    Support Home FAQ Documentation Customer Portal Enhancements Updates Roadmap Techblog
Try m-Power

m-Power Manual

Browse:

  • Home
  • m-Painter
  • Override Default SQL Statement
Back to Manual

Override Default SQL Statements

 

m-Power applications allow you to have the ability to manually override the default SQL statement. Some customers wish to do this to be able to fully customize which data is extracted from the database. To do this, build your application as you normally would.

In the illustration provided below, we have selected the Report Template.

Then, instead of running your application, open the Application Properties from the Manage Applications screen.

Click the "SQL Statement" tab.

Look for the "Override SQL Statement" option. Follow the on-screen help to specify your very own SQL statement.

Note: Any field you manually entered into your customized SQL statement must be included within the original application. Similarly, all fields listed in your Pre-Format field list must be included in your SQL override statement.

How do I add runtime parameters to my SQL statement?

Usually, when you need to override the default m-Power SQL statement, your statement will be finite. That is, you will specify fields, tables, and where clauses. However, there may be times where you will need to include end-user selection criteria into the SQL statement.

Generally speaking, the best place to begin is by seeing what the default SQL statement is. To do this, add ?debug=1 (and &run=2 if it is a report) to the end of your URL. Your screen should look something like this:

Where Clause

In this example, we will utilize our record selection in our customer SQL statement. To do this, open Application Properties, navigate to the SQL Statement tab, and copy and paste the above section (the first line only) into the "sql_statement" box:

SELECT T01."CNAME", T01."CCITY", T01."CSTATE", T01."AMTDU" FROM MRCWORKLIB."CUSTMAST" T01 ${where runtime selections}

Notice, that the logic to call the Run-time selection is ${where runtime selections}. The syntax must be used exactly like this.

Note: If you already have specified the where section, simply use this syntax instead: ${and runtime selections}

The latter will form your syntax so that the keyword WHERE is not repeated.

My output will look like this at runtime (with debug showing):

As you can see from the debug statement, the runtime selections were correctly inserted into the SQL statement at runtime.

Note: The five runtime selections that can be used when overriding the SQL statement are:

  • ${RXXX} — Available in reports only. Allows you to utilize any value passed in via a run-time record selection anywhere within your SQL statement (Replace XXX with the record selection number).
  • ${where runtime selection} — Used when only adding runtime record selections to the where clause of the SQL statement
  • ${and runtime selection} — Used when you are adding to an already existing where clause
  • ${order by runtime sequences} — Used when choosing to order your application only by the selected sequence keys
  • ${order by runtime sequences without qualifiers} — Used when wanting to not specify which tables the fields come from in your order by statement

Created: February 29, 2008 | Modified: October 20, 2016

Search


Browse By Category

Build Process (13)
Starting with m-Power (8)
Retrievals (10)
Reports (15)
Summaries (4)
Maintainers (17)
Graphs (8)
m-Power Data Explorer (4)
General (24)
Calculations (5)
Utilities (9)
m-Power Administration (23)
Security (11)
Freemarker (6)
m-Painter (29)
Form Validation (5)
External Objects & UDFs (12)
Deprecated Documentation (23)
Bootstrap Templates (7)

Popular Tags

Summaries Tomcat m-Painter Record Selections SQL Video Compiling Retrieval Administration mrc-Productivity Series Data Dictionary Database Dropdowns RPG Reports Maintainers Dates Graph Properties Prompt Screens Maintainer Popular Graphs Advanced External Objects Form Validation Security Admin Getting Started Email Report Application Properties Bootstrap Templates Graphing Retrievals Calculations Production App Properties Bar Graphs Excel Java Freemarker Performance Build Process DB2 Parameters

See all tags »

michaels, ross & cole, ltd. (mrc)

Privacy Policy Cookie Policy Cookie Settings Notice at Collection Do Not Sell or Share My Personal Information

mrc (US)

2001 Midwest Road
Suite 310
Oak Brook, IL 60523
630-916-0662

mrc (UK)

Mortlake Business Centre
20 Mortlake High Street
London, SW14 8JN
+44-20-335-59566


© 2024 mrc. All rights reserved.