UNIT 1
What Is SQL/PRO
Structured Query Language (SQL) is a powerful query language that now is supported on virtually every computer made. SQL/PRO brings the power of SQL to your AS/400 for the lowest cost.
SQL provides users - both technical and non-technical - with a simple English-like language that gives them the power to query, print and manipulate any data. You'll be able to select, organize and summarize your information in nearly any way you can imagine - certainly any way you'll ever require! You'll be able to direct your output to the screen, printer or database file. And programmers will appreciate the power of SQL to update files.
How easy is SQL to run? The following statement will alphabetically list all records of the CUSTMAST file where state equals California:
- SELECT * FROM CUSTMAST
- WHERE STATE='CA'
- ORDER BY STATE
What could be simpler?
Our SQL/PRO interface provides truly easy-to use screens that will have you accessing the power of SQL in no time. But don't let the ease-of-us (or the low price) fool you - SQL/PRO is powerful! With field name selection, unlimited history, our unique Saved Queries feature, submission to batch and comprehensive services, SQL/PRO is a mighty tool.
You'll find this manual not only instructs you on the workings of SQL/PRO, but it also includes very complete instructions on the writing of SQL statements themselves.
SQL Statement Entry
The heart of SQL/PRO is the interactive SQL statement entry screen, where you key in any SQL statement you wish to run. The insert, split, copy, delete and split functions make it easy to enter statements. You'll have a full 32,760 characters (over 409 lines by 80) to run your SQL statement. Once the statement is entered you can run it interactively or in batch from this screen.
File Field Name Selection
SQL statements require that you specify the names of fields with which you will be working. With SQL/PRO there's no need to remember field names; just press a function key and you can display the field listing of any file on your system. From this screen, you can select fields to be automatically inserted into your statement.
Report Formatting
When output is directed to a printer SQL does very little in way of report formatting. However, SQL/PRO offers you comprehensive report formatting by creating what is know to QS/400 as a Query Management form (*QMFORM) object.
Report formatting criteria is submitted through a series of prompt screens that are automatically linked to your current SQL SELECT statement. The objects created by SQL/PRO are the same objects used by IBM's SQL/400 Query Manager so, if you ever decide to purchase IBM's SQL/400, all your queries are immediately usable by the IBM product.
History
SQL/PRO remembers every statement you run for as long as you desire. You can browse through the history to look for previous statements and rerun them. The browsing display shows the first 65 characters of the statement. From there, you can select to retrieve or view the entire SQL statement to optionally modify and re-run. At any time you can purge the history field down to any number of entries.
Saved Queries
The Save Queries feature is unique to SQL/PRO. You can name and save any SQL statement you have written. Public access can be indicated for any saved queries making it easy to narrow the users that can use, change or delete query. Queries can be saved directly from the interactive entry screen or indirectly through the history screen. Once saved, you can select and run a Saved Query from the Work With Saved Queries screen interactively or via a job queue. The screen shows all the saved queries alphabetically ordered in a subfile. A special command also lets you run a saved query from a CL program.
Embed SQL in CL
A SQL/PRO command lets you embed SQL statements in CL, something IBM's Sql/400 won't allow. You can run any SQL statement or an already-created Saved Query from a CL program. Parameters allow full control of the outfile selections. Think about how much easier this is than writing cryptic OPNQRYF commands.