Aspen SQLplus for Aspen InfoPlus.21: Using and Configuring for Power Users

This course will help you prepare for the certification exam and the exam fee is waived with this course.
Learn how to write and execute SQL queries to maintain, view, or manipulate Aspen InfoPlus.21 data and how to integrate data from Aspen InfoPlus.21 with other data sources. Configure versatile reports using Aspen SQLplus Reporting Tool.

Audience:

  • Users who have no SQL skills yet, but who want to learn what it has to offer and how it can help them to access InfoPlus.21 data
  • Users who already have a basic understanding of SQLplus and who want to extend their knowledge to include more advanced topics and features
  • Users who understand the basics of Aspen SQLplus and who want to create simple applications or to configure reports
  • Existing or prospective system integrators and database system administrators

Training Details

  • Course Id:

    MES201

  • Duration:

    5 day(s)

  • CEUs Awarded:

    3.5

  • Level:

    Intermediate

Benefits

  • Reduce the time and effort required to implement and maintain Aspen InfoPlus.21 applications
  • Using SQL to populate your database saves time and effort - this course shows you how
  • Improve the performance of your existing system by using SQLplus efficiently
  • Versatile reporting means better-informed decision making by integrating many data sources
  • Easily transfer your skills to other SQL vendors and organizations

Approach

  • Interesting and relevant course material taught by knowledgeable instructors; covers all versions of the product
  • In-depth discussion on the most important Aspen InfoPlus.21-specific extensions to the SQL language
  • Frequent hands-on exercises, featuring examples relevant to a plant information environment, used throughout the course to illustrate language features, useful utilities, and application ideas

Pre-requisites

Some knowledge of Aspen InfoPlus.21 database concepts is expected. Prior completion of the PME101 or MES101 Aspen InfoPlus.21 Real Time Information Management Foundation Course will greatly enhance the value of this course

Agenda

What is Aspen SQLplus
  • Identify various features of Aspen SQLplus and the potential benefits of these features
  • Interpret the Aspen Process Information database as a relational database so that SQL can be applied to it

Select Statements
  • Use the Aspen SQLplus Query Writer to create and execute queries
  • Apply the syntax of a SELECT statement to query the Aspen database
  • Sort data from a query using the ORDER BY
  • Workshop: Use the Aspen SQLplus editor to construct simple queries with the SELECT statement and become familiar with the Query Editor.

The WHERE Clause
  • Learn how to filter data using the WHERE clause with examples of the following
  • Logical and comparison operators
  • Character and timestamp constants
  • Know which criteria to use to restrict results
  • Learn the order of precedence when using Logical Operators
  • Introduce Wildcards and show how they can be used in queries
  • Workshop: Use a WHERE clause to restrict output from the database using various techniques.

SELECT Revisited
  • Create queries that use arithmetic functions, expressions, and apply the rules that govern their use
  • Utilize data conversion as performed by Aspen InfoPlus.21
  • Use Pseudo Columns and Pseudo Tables to access system-type data from the InfoPlus.21 database
  • Workshop: Use arithmetic functions, expressions, and Pseudo columns to enhance the SELECT statement.

Reports
  • Create Customized reports using the SQLplus Report “Toolkit”
  • Be able to produce formatted server-based reports using pre-defined templates
  • Learn to use the Aspen SQLplus web based reporting tool
  • Workshop: Create a formatted report featuring; reporting headings, breaks, calculations and page numbering.

SET (Group) Functions
  • Create queries that use SET (Group) Functions effectively
  • Create subsets using the GROUP BY clause
  • Restrict the output with a HAVING clause and comparing with use of WHERE
  • Create cross-tabulated lists
  • Workshop: Use the SET functions to calculate values for groups of data.

Record Based Queries
  • Save queries as files and as records in the InfoPlus.21 Database
  • View the structure of a Query Record and see how the query is stored
  • Execute query records using different methods of activation
  • Demonstrate an understanding of the purpose of Compressed queries
  • Create queries that use the system functions ACTIVATION_RECORD and ACTIVATION_FIELD within a Generic Query
  • Workshop: Create a query that is saved as a record. Activate the query using various methods.
  • Workshop: Create and populate a data record from a prepared custom definition record that allows a link to a query. Write a query to process this record.

Text and System Queries
  • Discuss SQL character string handling functions
  • How to read ASCII text files and output ASCII text
  • How to use Pseudocolumns LINE and LINENUM
  • Discuss SYSTEM command and its use as a table
  • Workshop: Use the text handling features of Aspen SQLplus to interrogate a text file.
  • Workshop: Write a query that uses the SYSTEM command to execute operating system commands.

Modifying the Database
  • Learn about mechanisms that can be put in place to secure the database from unauthorized change
  • Learn how to use SQL to add, delete, or modify data
  • Workshop: Use SQL to: create records in a definition family table, modify data and delete records from a definition family table.

Writing Simple Programs
  • Understand basic procedural statements
  • Use simple programming techniques
  • Workshop: Use the Aspen SQLplus programming extensions to write some short program routines.
  • Workshop: Modify the IQ task so that it can run in multi-threaded mode.

Summative Lab 1
  • Workshop: Use techniques that have been covered in this course to solve a more realistic problem.

Extending SELECT and Temporary Tables
  • Understand the purpose, scope, and applications of Temporary Tables
  • Be able to set up, populate, and report the contents of temporary tables
  • Workshop: Create a temporary table that contains the following fields: Batch, Target, Prod, and Over_Prod.
  • Workshop: Use the record and field indirection facilities.

Debug Tools
  • Become familiar with the various items in the Debug menu provided with Aspen SQLplus
  • Understand the concept of Breakpoints and how to use them to analyze your queries
  • Understand the concepts of stepping into and out of queries
  • Understand the Watch window to evaluate expressions

Parameterized SQL
  • Explain how queries can START other queries
  • Learn how parameters can be passed, allowing queries to be generic
  • Understand what Aspen SQLplus macros are, and how to use them
  • Using the Aspen SQLplus PROMPT command to make interactive queries
  • Workshop: Create and execute queries that use; parameters, Macros and the PROMPT command.

Subquery Uses
  • Review subqueries in Condition clauses and Subquery Tables
  • Workshop: Use simple subquery conditions. Write a correlated subquery. Use a subquery as a table.

Table Joins
  • Review the different types of table joins
  • Workshop: Use different kinds of join to combine data from different tables.

Views
  • Review how to create, use and delete (drop) views.
  • Workshop: Create and execute views using the: CREATE VIEW statement and the Query editor.

Combined Queries
  • Understand how more than one SQL query can be combined using various expressions.
  • Workshop Experiment in combining queries with UNION, EXCEPT, and INTERSECT.

Remote Database Links and ODBC
  • Overview of Remote Links
  • Use of COMMIT and ROLLBACK commands
  • Use of EXECUTE for remote SQL commands
  • Learn how to obtain current and historical process data from an AspenTech real-time database using an ODBC link
  • Learn how to extract data selectively
  • Workshop: Use Desktop ODBC to bring data from Aspen InfoPlus.21 into an Excel spreadsheet using Query Wizard.
  • Workshop: Use Microsoft Query to Obtain Data.
  • Workshop: Connect to Aspen InfoPlus.21 and select a repeat area data table.

Summative Lab 2
  • Workshop: Modify the SQL so that several orders can be read and stored.

The Excel Add-In Interface
  • Demonstrate the SQLplus-based portions of the COM Aspen Process Data Excel Add-In.
  • Take advantage of existing Aspen SQLplus queries and stored procedures from within Microsoft Excel.

Hints and Tips
  • Review a collection of hints and tips that may prove useful in applying Aspen SQLplus
Certification Exam

Register for a Class

Date Class Type Location Price Language
Date(s): 10/9/2023 - 10/13/2023 Type: Public Virtual Location: Virtual-EMEA Price: (USD) 5000.00 Language: English Register
Date(s): 10/30/2023 - 11/3/2023 Type: Public Virtual Location: Virtual-EMEA Price: (USD) 5000.00 Language: English Register
Date(s): 11/27/2023 - 12/1/2023 Type: Public Virtual Location: Virtual-EMEA Price: (USD) 5000.00 Language: English Register
Date(s): 12/4/2023 - 12/8/2023 Type: Public Virtual Location: Virtual-Latin America Price: (USD) 3000.00 Language: Spanish Register
Date(s): 10/23/2023 - 10/27/2023 Type: Public Classroom Location: 2500 Citywest Blvd, Suite 1600
Houston , Texas USA 77042 		Price: (USD) 4500.00 Language: English Register
Date(s): 11/6/2023 - 11/10/2023 Type: Public Classroom Location: 2500 Citywest Blvd, Suite 1600
Houston , Texas USA 77042 		Price: (USD) 4500.00 Language: English Register
Date(s): 11/27/2023 - 12/1/2023 Type: Public Classroom Location: 2500 Citywest Blvd, Suite 1600
Houston , Texas USA 77042 		Price: (USD) 4500.00 Language: English Register
Date(s): 12/18/2023 - 12/22/2023 Type: Public Classroom Location: 2500 Citywest Blvd, Suite 1600
Houston , Texas USA 77042 		Price: (USD) 4500.00 Language: English Register
Date(s): 10/23/2023 - 10/27/2023 Type: Public Virtual Location: Virtual-Americas Price: (USD) 4500.00 Language: English Register
Date(s): 11/6/2023 - 11/10/2023 Type: Public Virtual Location: Virtual-Americas Price: (USD) 4500.00 Language: English Register
Date(s): 11/27/2023 - 12/1/2023 Type: Public Virtual Location: Virtual-Americas Price: (USD) 4500.00 Language: English Register
Date(s): 12/18/2023 - 12/22/2023 Type: Public Virtual Location: Virtual-Americas Price: (USD) 4500.00 Language: English Register
Date(s): 10/16/2023 - 10/20/2023 Type: Public Virtual Location: Virtual-India Price: (INR) 105000.00 Language: English Register
Date(s): 11/27/2023 - 12/1/2023 Type: Public Virtual Location: Virtual-APAC Price: (USD) 1500.00 Language: English Register
Date(s): 01/8/2024 - 01/12/2024 Type: Public Virtual Location: Virtual-EMEA Price: (USD) 5000.00 Language: English Register
Date(s): 02/5/2024 - 02/9/2024 Type: Public Virtual Location: Virtual-EMEA Price: (USD) 5000.00 Language: English Register
Date(s): 03/4/2024 - 03/8/2024 Type: Public Virtual Location: Virtual-EMEA Price: (USD) 5000.00 Language: English Register
Date(s): 01/8/2024 - 01/12/2024 Type: Public Classroom Location: C2, Reading International Business Park, Basingstoke Road
Reading , United Kingdom RG2 6DT 		Price: (USD) 5000.00 Language: English Register
Date(s): 02/5/2024 - 02/9/2024 Type: Public Classroom Location: C2, Reading International Business Park, Basingstoke Road
Reading , United Kingdom RG2 6DT 		Price: (USD) 5000.00 Language: English Register
Date(s): 03/4/2024 - 03/8/2024 Type: Public Classroom Location: C2, Reading International Business Park, Basingstoke Road
Reading , United Kingdom RG2 6DT 		Price: (USD) 5000.00 Language: English Register
Date(s): 01/22/2024 - 01/26/2024 Type: Public Virtual Location: Virtual-Americas Price: (USD) 4500.00 Language: English Register
Date(s): 02/12/2024 - 02/16/2024 Type: Public Virtual Location: Virtual-Americas Price: (USD) 4500.00 Language: English Register
Date(s): 03/11/2024 - 03/15/2024 Type: Public Virtual Location: Virtual-Americas Price: (USD) 4500.00 Language: English Register
Date(s): 04/15/2024 - 04/19/2024 Type: Public Virtual Location: Virtual-Americas Price: (USD) 4500.00 Language: English Register
Date(s): 05/13/2024 - 05/17/2024 Type: Public Virtual Location: Virtual-Americas Price: (USD) 4500.00 Language: English Register
Date(s): 06/10/2024 - 06/14/2024 Type: Public Virtual Location: Virtual-Americas Price: (USD) 4500.00 Language: English Register
Date(s): 01/22/2024 - 01/26/2024 Type: Public Classroom Location: 2500 Citywest Blvd, Suite 1600
Houston , Texas USA 77042 		Price: (USD) 4500.00 Language: English Register
Date(s): 02/12/2024 - 02/16/2024 Type: Public Classroom Location: 2500 Citywest Blvd, Suite 1600
Houston , Texas USA 77042 		Price: (USD) 4500.00 Language: English Register
Date(s): 03/11/2024 - 03/15/2024 Type: Public Classroom Location: 2500 Citywest Blvd, Suite 1600
Houston , Texas USA 77042 		Price: (USD) 4500.00 Language: English Register
Date(s): 04/15/2024 - 04/19/2024 Type: Public Classroom Location: 2500 Citywest Blvd, Suite 1600
Houston , Texas USA 77042 		Price: (USD) 4500.00 Language: English Register
Date(s): 05/13/2024 - 05/17/2024 Type: Public Classroom Location: 2500 Citywest Blvd, Suite 1600
Houston , Texas USA 77042 		Price: (USD) 4500.00 Language: English Register
Date(s): 06/10/2024 - 06/14/2024 Type: Public Classroom Location: 2500 Citywest Blvd, Suite 1600
Houston , Texas USA 77042 		Price: (USD) 4500.00 Language: English Register

Aspen Technology, Inc. awards Continuing Education Units (CEUs) for training classes conducted by our organization. One CEU is granted for every 10 hours of class participation.