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
  • General
  • Register SQL Views
Back to Manual

Register SQL Views

 

An SQL View can be thought of as a virtual table — one that does not physically contain any data but rather stores useful SQL queries to improve database/user efficiency. While most developers realize how powerful an SQL view can be while running queries, they can now harness the same functionality within m-Power. Below are just two examples of excellent reasons to build and use an SQL view:

  • Limiting Records — Let's say that you have a Sales History file with data from the past 10 years and you know that you would never want to report over more than the last 3 years. Of course, you could build a record selection within each of your m-Power applications to handle this. Your other option would be to create an SQL view over this table that only includes the last 3 years of data.
  • Joining — Another example of a useful view could be that you have a complicated query — one that utilizes numerous tables, joining on many fields. You could, one hand, build your application manually, using m-Power to join out to all of the necessary tables. Or, you could create an SQL join that includes all necessary tables. Once your SQL View is created, all included fields will appear in m-Power as belonging to one table, which makes creating each subsequent application much easier!

Best of all, as soon as your data changes, your view will automatically be updated! Even more information on SQL Views can be found here.

Step 1 — Create an SQL View

To create your SQL View, using the "Limiting Records" example, simply enter in your SQL query using the following syntax:

CREATE VIEW SCHEMALIB/VIEWNAME AS SELECT FIELD1 as FIELDABC, FIELD2 as FIELDDEF, FIELD3 FROM SCHEMALIB/TABLE1 WHERE FIELD1 = VAL1 and FIELD2 = VAL2

Note: In the example above, only 3 fields were included in the view. Additionally, FIELD1 and FIELD2 were renamed to FIELDABC and FIELDDEF, respectively.

To create an SQL View that uses the "Joining" example, simply enter in your SQL query using the following syntax:

CREATE VIEW SCHEMALIB/VIEWNAME AS SELECT * FROM SCHEMALIB/TABLE2 T01 left outer join SCHEMALIB/TABLE1 T02 on T02.FIELD2 = T01.FIELD1

Note: In this example, all fields from Table1 and Table2 have been added to this view.

Editor's Note: There are countless reasons you would want to create and utilize SQL Views. For the sake of brevity, we have listed two of the more common approaches.

Step 2 — Load Your SQL View into m-Power

Registering SQL Views to m-Power is the same process as registering tables. Simply access the "Admin" screen, then "Registered Tables". Click the "Register button". If you know the schema/view name, enter it here. If not, click the browse button. A "Type" column will help you determine if your data is contained in a physical table or in an SQL view. Click the green "plus" icon to add your view.

Note: In browse mode, all physical tables are listed first, followed by all views, if the selected schema contains any. If you do not see your view at first, please be sure to scroll to the end to find your newly created view.

Step 3 — Using your SQL View in an Application

Create your application as you normally would — your SQL view will be in the same list as your physical table!

Note: Building a Maintenance application over an SQL view, and adding/updating/deleting a record will also modify the record in the physical table.

Note: SQL is unable to add/update/delete records from an SQL view where multiple tables have been joined together. If you try, you will see an SQL error message

Created: September 21, 2010 | Modified: August 2, 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

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

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.