How can I run SQL SELECT statements in OLQ?
Article ID: 10129
Updated On:
Products
Issue/Introduction
How can SQL SELECT statements be used in OLQ?
There are two methods for using SQL SELECT statements in OLQ. Both methods can be used online and in OLQBATCH jobs.
Environment
Resolution
Method 1: Native OLQ SQL … “SET ACCESS OLQ” (the default)
By just using record names as table names, and element names as column names, simple SELECT statements can be coded. For example, an OLQBATCH input stream for the demonstration database provided at install such as this could be used:-
//SYSIPT DD *
SET USER your-user-name
SET ACCESS OLQ
SET DICTNAME APPLDICT
SET DBNAME EMPDEMO
SIGNON SS EMPSS01 SCHEMA EMPSCHM ( 100)
SELECT CLAIM-YEAR-0405, PATIENT-LAST-NAME-0405 FROM DENTAL-CLAIM -
ORDER BY 2
DISPLAY
Method 2: CA-IDMS/SQL option … “SET ACCESS IDMS”
The same SELECT statement can be executed in such a way that OLQ simply passes the syntax to the CA-IDMS SQL/Option for execution. Some small changes need to be made to the syntax for this to work:-
* Provide the SQL schema name on the table name.
* Hyphens, "-", in column names must be changed to underscores, "_".
* Record (table) names with hyphens in them must be double-quoted.
For example:-
//SYSIPT DD *
SET USER your-user-name
SET ACCESS IDMS
SET DICTNAME APPLDICT
SELECT CLAIM_YEAR_0405, PATIENT_LAST_NAME_0405 -
FROM EMPNET."DENTAL-CLAIM" ORDER BY 2
DISPLAY
In the above example, the following definition for EMPNET would have to be made in the APPLDICT SQL catalog:-
CREATE SCHEMA EMPNET
FOR NONSQL SCHEMA APPLDB.EMPSCHM VERSION 100
DBNAME EMPDEMO
;
Method 2 can also be used in OLQ to execute SELECT statements against native SQL-defined tables.
Method 2 is also a more robust and function rich implementation of SQL than method 1.
Additional Information
For more information, see the following CA IDMS DocOps pages:
Feedback
