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

• 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