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

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

Agenda

• 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

• 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.

• 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.

• 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.

• 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.

• 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.

• 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.

• 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.

• 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.

• 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.

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

• 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.

• 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

• 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.

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

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

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

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

• 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.

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

• 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.

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

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.