SQLplus Overview

InfoPlus.21 Foundation Course

©2004 AspenTech. All Rights Reserved.

 Objectives
• Become aware of the main features of SQLplus and their potential
benefits
• Understand how SQLplus interprets InfoPlus.21 data structures as
tables
• Learn basic syntax of simple queries
• Use the Query Writer to create and execute simple queries
• Learn how to save queries as a:
– File on disk
– Record in the database

• Understand the possibilities offered by record-based Queries

• Be introduced to data transfer via Desktop ODBC

©2004 AspenTech. All Rights Reserved.

 Glossary of Terms
• Activation

• ANSI

• GUI

• OAM

• ODBC, Desktop ODBC

• Relational Database

• Structured Query Language

• Table

©2004 AspenTech. All Rights Reserved.

 Module Overview
• In this module:
– Main features and benefits of SQLplus
– Typical uses of SQLplus
– How SQLplus models tables in InfoPlus.21
– The basic syntax of SQL
– How to use the Query Writer Tool
– How to save queries as text or as records
– Query output possibilities
– SQLplus data source options
– Query generation options
– Example of report generation capabilities of SQLplus using Desktop
ODBC

©2004 AspenTech. All Rights Reserved.

 Features and Benefits of SQLplus
• Provides a standardized • Skills learned are transferable
method of reading and writing
InfoPlus.21 data • GUI is easy to use. Storing text
queries saves database space.
• Queries written with the GUI Storing records gives tight
can be stored as text files or as integration with database
records in InfoPlus.21
database • Versatile tool for processing
InfoPlus.21 data for large or
• Queries can be executed trivial tasks
based on change-of-state or
scheduled • Mass record creation from
“taglists”
• SQLplus can read text files
and operating system data

©2004 AspenTech. All Rights Reserved.

 More Features and Benefits of SQLplus
• Programming Extensions to • Eliminates need for traditional
ANSI standard are provided programs
• Relational database read/write • Facilitates Enterprise-wide data
using optional OAM integration
• Enables data transfer to • Report creation does not
Windows applications via involve learning new tools
Desktop ODBC
• Client applications easily
written using Visual languages
• Supports InfoPlus.21 security
system

©2004 AspenTech. All Rights Reserved.

 Common Uses of SQLplus
• Common uses include:
– Mass record creation and modification
– Report Generation and enterprise-wide data publishing
– Automated processing within the InfoPlus.21 database
– Transfer of data to and from other systems:
• Relational Databases
• ASCII based systems (for example, Lab Data)
• ERP systems
• Windows programs (for example, Excel, Access, Word, Lotus)
– Development tool for complete InfoPlus.21 applications

©2004 AspenTech. All Rights Reserved.

 Definition Records as Tables
• Definition records can be likened to a data table
• The entire fixed area of each record created from the
definition record represents one row in the table
• Each field in the fixed area is a column in the table
Table: IP_AnalogDef
Name IP_Description IP_Value IP_Alarm_State
Ana1
Ana2
Ana3
Ana4
Ana5
Ana6
Ana7
©2004 AspenTech. All Rights Reserved.
 Example Query 1

Query:
SELECT Name, IP_Description, IP_Value, IP_Plant_Area
FROM IP_AnalogDef;

Results:
NAME IP_DESCRIPTION IP_VALUE IP_PLANT_AREA
SaltFlow Rate of Salt Flow 57.3 Reactor
SaltTankLvl Level of Salt Tank 8.5 Reactor
ProductFlow Rate of Product Flow 81.0 Reactor
RecircFlow Rate of Recirculation Flow 96.5 Reactor

©2004 AspenTech. All Rights Reserved.

 Individual Records as Data Tables
• A repeat area of an individual record is like a data table
– One occurrence of a repeat area represents one row
– Each field in the repeat area is a column

©2004 AspenTech. All Rights Reserved.

 Example Query 2
Query:
SELECT IP_Trend_Value, IP_Trend_Time,
IP_Trend_Qstatus FROM SaltFlow
WHERE IP_Trend_Time > ‘27-Aug-03 08:00:00.0’;

Results:
IP_TREND_VALUE IP_TREND_TIME IP_TREND_QSTATUS
57.3 27-Aug-03 08:10:00.0 Good
56.5 27-Aug-03 08:09:00.0 Good
56.4 27-Aug-03 08:08:00.0 Good
55.2 27-Aug-03 08:07:00.0 Good

©2004 AspenTech. All Rights Reserved.

 Basic SQL Query Language Syntax
• SELECT list_of_fields

• FROM list_of_tables
– WHERE clause
– ORDER BY clause

• Extension to ANSI standard allows direct assignment –

“programs” can be written:
recordname.fieldname = value

• Arithmetic and other functions available

recordname.fieldname = value*10

©2004 AspenTech. All Rights Reserved.

 Query Writer
Click here

Write Query
SELECT Name, IP_Description FROM IP_AnalogDef;
here

Results Area

©2004 AspenTech. All Rights Reserved.

 Query Building Wizard

Scroll

©2004 AspenTech. All Rights Reserved.

 Save Query as Text File on Disk

©2004 AspenTech. All Rights Reserved.

 Save Query as an InfoPlus.21 Record

©2004 AspenTech. All Rights Reserved.

 Example QueryDef Record

Query is stored in “#Query_Lines” repeat area

Results stored in “#Output_Lines” repeat area

Query is executed when record “activated” Repeat Area

Record can be activated multiple ways
Fixed Area

YES Execute

©2004 AspenTech. All Rights Reserved.

 Query Record Output Possibilities
Activatio When Query Record is activated,
n Output from a Query record can go to:

Repeat area of the same record
Query Record 
Can be viewed as a summary
on GCS screen

Another record

Text file on disk

Table in a relational database
Calculation

Tag Record

Disk File 42
Microsoft SQLServer
ORACLE
Other Rdb
©2004 AspenTech. All Rights Reserved.
 What Data Can SQLplus Query?
• Any data in InfoPlus.21 database or Historian All appear to
• Data in a text file SQLplus as
• Data in another relational database Tables
• Data from operating system calls
Process
Equipment InfoPlus.21 Database
“TagList”
Query Record DownTime Record

Tag Record
Lab Data

Quality Analysis
Data Microsoft SQLServer
ORACLE
Other Rdb
©2004 AspenTech. All Rights Reserved.
 Query Generation Options
• SQLplus queries can originate from:
– Query Writer Tool
– Text File executed from
Command Line
– Advanced Tag Browser
– ODBC via
• Excel or Lotus spreadsheets
• User GUI in Visual Basic
• Web page on an Intranet

©2004 AspenTech. All Rights Reserved.

 Reports via Desktop ODBC Example (1)

Step 1

©2004 AspenTech. All Rights Reserved.

 Reports via Desktop ODBC Example (2)
Step 2

Step 3

Step 4

©2004 AspenTech. All Rights Reserved.

 Returning Data into Application

Step 5

©2004 AspenTech. All Rights Reserved.

 Where to Find Out More
• Essential SQLplus Course (5 days)
– Everything you need to know about SQLplus
– Requires no prior SQL knowledge

©2004 AspenTech. All Rights Reserved.

 Review (1)
• SQLplus is one of the most powerful layered products
available for manipulating the database
• In SQLplus, a table can be a:
– Definition record
– Data record
– Text file
– Table on a relational database

• SQLplus Query Writer provides a productive

environment for writing, executing, and saving queries
• Queries can be stored as text files or InfoPlus.21 records

©2004 AspenTech. All Rights Reserved.

 Review (2)
• Query output can be directed to many places, including:
– InfoPlus.21 records
– A GCS summary
– Another database
– A text file

• Queries can be generated from many sources, including:

– The Query Writer
– A text editor
– An ODBC-compliant Windows application (for example, Excel,
Lotus, Visual Basic Application)

©2004 AspenTech. All Rights Reserved.

 SQLplus Overview: Lab
• In this Lab, you will:
– Investigate the system configuration for SQLplus
– Use the Query Writer to create and execute queries using
InfoPlus.21 data
– Save queries as text files and InfoPlus.21 records
– Inspect and execute a record-based query
– Create an SQLplus record that performs a simple
calculation for the Reactor Expansion Lab

©2004 AspenTech. All Rights Reserved.