SQL

The ability to modify the SQL or substitute new SQL gives you flexibility in creating reports.

Using ASURXSQL, you can run any SQL statement, including CREATE, INSERT, UPDATE, DELETE, and GRANT.

SQL as argument

The report programs place the SQL in parentheses to represent a single argument.

SQL as argument

22    rc = asurxsql('OPEN ' ,         /* open cursor */
23                  'VL CURSOR FOR SELECT ',
24                  'VOLID ,'              ,
25                  'FREETRK '              ,
26            'FROM ' dmtable 'A '        ,
27            'WHERE VOLID LIKE ''volume'' ' ,
28            '   AND TIMESTMP = (SELECT MAX(TIMESTMP)' ,
29            '        FROM ' dmtable 'B ',
30            '        WHERE B.VOLID = A.VOLID) ' ,
31            'ORDER BY FREETRK ASC ' ,
32            'FOR FETCH ONLY ');

The argument spans several lines to make it easier to read. However, the report programs treat the entire argument as a single line. Consequently, error messages refer to the entire argument by its first line number. For example, if a REXX syntax error occurs anywhere in the SQL in the above figure, the error message refers to line 22.

DECLARE and OPEN cursor

The report programs combine the DECLARE cursor and OPEN cursor statements, as in the following example:

OPEN cursor-name CURSOR FOR SELECT

For an example, see OPEN VL CURSOR in lines 22 and 23 of the sample program.

Variables in the SQL

Variables in the SQL vary by program.

For an example of the variables, see SQL as argument. In unmodified report programs, at a minimum, the variables represent the input table name, dmtable, and EXEC PARM input data, such as volume.

Retrieving the most recent data

Fetching the most recent data from the DASD MANAGER PLUS historical database requires the MAX function, MAX(TIMESTMP), within a subselect (for example, lines 28 through 30), as shown in the following example.

'     AND TIMESTMP = (SELECT MAX(TIMESTMP) ' ,
'        FROM ' dmtable ' B' ,
'        WHERE B.VOLID = A.VOLID)' ,

Quotation marks

When SQL requires single quotation marks within REXX code, you must enclose the single quotation mark with double quotation marks to indicate that the single quotation mark is a literal.

' ' ' variable ' ' '

The first set of double quotation marks can enclose code other than the single quotation mark, as shown below:

'''variable''' ,

In the following example, the first set of double quotation marks encloses the words WHERE VOLID LIKE in addition to the first single quotation mark for the variable 'volume '.

27 'WHERE VOLID LIKE ''volume''' ,

SQL