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 |
