SQL Functions

Restriction: This topic applies only when the AppMaster Builder AddPack has been installed, and applies only to Windows platforms.

Code SQL built-in and scalar functions as keywords for the database access calls DB-DECLARE, DB-OBTAIN, and DB-PROCESS.

Target:

SQL

Syntax: for DB-DECLARE

DB-DECLARE cursorname [correlname1.]copylibname-REC
... function1[(](expression)[,expression[,Y][)]]
... function2[(](expression)[,expression[,Y][)]]
          .
          .
          .
... functionN[(](expression)[,expression[,Y][)]]
... WHERE ...

Syntax: for DB-OBTAIN

DB-OBTAIN REC [correlname1.]copylibname-REC
... function1[(](expression)[,expression[,Y][)]]
... function2[(](expression)[,expression[,Y][)]]
          .
          .
          .
... functionN[(](expression)[,expression[,Y][)]]
... WHERE ...

Syntax: for DB-PROCESS

DB-PROCESS REC [correlname1.]copylibname-REC
... [DB-PROCESS-ID name]
... function1[(](expression)[,expression[,Y][)]]
... function2[(](expression)[,expression[,Y][)]]
          .
          .
          .
... functionN[(](expression)[,expression[,Y][)]]
... WHERE ...

General Rules:

If you code a column function, every column in the call must use column functions, unless you code GROUP BY.

You can code functions in a WHERE clause. For example:

DB-OBTAIN
.. REC PARTDESC-REC
.. WHERE
.. MAX (PD_LONG_DESC) = 3

You can mix scalar functions with individual column selections in a call.
If you apply a function against a single column, the result is returned to the host variable. To override the result, specify a result field in the call and either:
- Create a field in Working-Storage with the same data type as its corresponding columns or expression result. For scalar functions, the data type is determined by the function used and its associated rules.
- Enter Y after the result field in the column function to let AMB create the field.
If you apply a function against multiple columns, a literal, or an expression for which there is no host-variable, declare a result field in Working-Storage. The result field name can be as large as 26 characters; use COBOL naming conventions.
If the function can return a null value, it requires an associated indicator variable as follows:
- When AMB creates the result field, it creates a null indicator variable with the name based in the result field, such as resultfield-IND.
- If you create the indicator variable in Working-Storage, use the same naming convention so that AMB can correctly name each indicator variable in the resulting SQL.
- Limit resultfield to 26 characters to allow room for -IND. Define resultfield-IND as PIC S9(4) COMP.; for SQL for DB2, PIC S9(4) COMP-5.
If you do not specify a result field with the COUNT function, the AMB-created field APS-COUNT-ROWS returns the COUNT result.

Example: Select the minimum unit base price and count the number of colors it finds for a given part number; create the result fields and appropriate indicator variables in Working-Storage.

DB-DECLARE D2MAST-CURSOR D2TAB-REC
... MIN((PM_UNIT_BASE_PRICE),WS-PM-UNIT-BASE-PRICE,Y)
... COUNT((DISTINCT PM_COLOR),WS-PM-COLOR,Y)
... WHERE PM_PART_NO = :WS-PART-NO

Example: SQL code with MIN and MAX functions

DB-OBTAIN REC D2TAB-REC
... MAX((PM_UNIT_BASE_PRICE),WS-MAX-PRICE,Y)
... MIN((PM_UNIT_BASE_PRICE * PM_UNITS),WS-MIN-RESULT,Y)
... WHERE PM_PART_SHORT_DESC = 'WIDGET'

Generated code:

EXEC SQL SELECT
    MAX(PM_UNIT_BASE_PRICE)
    MIN(PM_UNIT_BASE_PRICE * PM_UNITS)
    into :WS-MAX-PRICE, :WS-MAX-PRICE-IND
    :WS-MIN-RESULT :WS-MIN-RESULT-IND
    FROM AUTHID.D2MASTER
    WHERE PM_PART_SHORT_DESC = 'WIDGET'
END-EXEC.

Example: SQL code with SUM and AVG functions

DB-OBTAIN REC D2TAB-REC
... SUM(PM_UNITS)
... AVG((PM_UNITS),WS-AVG-UNITS,Y)
... WHERE PM_PART_NO = '23432'

Generated code:

EXEC SQL select
    SUM(PM_UNITS)
    AVG(PM_UNITS)
    INTO :D2TAB-REC.PM-UNITS :IND-D2TAB-REC.IND-PM-UNITS,
    :WS-AVG-UNITS :WS-AVG-UNITS-IND
    FROM AUTHID.D2MASTER
    WHERE PM_PART_NO = '23432'
END-EXEC.

Example: SQL code with COUNT function

DB-OBTAIN REC D2MASTER-REC 
... MAX((PM-UNITS),WS-MAX-PM-UNITS)
... COUNT((*),WS-PM-COUNT-FLD)
... AVG((PM-UNIT-BASE-PRICE),WS-AVG-PRICE)
... WHERE PM-PART-SHORT-DESC='WIDGET'
... AND PM-COLOR='RED'

Generated code:

EXEC SQL select
    MAX(PM-UNITS)
    MIN(PM-UNIT-BASE-PRICE)
    COUNT(*)
    AVG(PM-UNIT-BASE-PRICE)
    INTO WS-MAX-PM-UNITS WS-MAX-PM-UNITS-IND,
    WS-PM-COUNT-FLD,
    WS-AVG-PRICE WS-AVG-PRICE-IND
    FROM AUTHID.D2MASTER
    WHERE PM-PART-SHORT-DESC='WIDGET'
    AND PM-COLOR='RED'
END-EXEC.

Example: SQL code with DATE, TIME, and AVG scalar functions

DB-OBTAIN REC D2INVEN-REC
... IN_PART_NO
... DATE(IN_DATE_LAST_UPDTE)
... TIME((IN_TIME_LAST_UPDTE),WS-TIME-RETURN,Y)
... CHAR((IN_DATE_LAST_ORDER,ISO),WS-CHAR-RETURN)
... IN_QTY_ONHAND
... WHERE IN_PART_NO = '23432'

Generated code:

EXEC SQL SELECT
    IN_PART_NO
    DATE(IN_DATE_LAST_UPDTE)
    TIME(IN_TIME_LAST_UPDTE)
    CHAR(IN_DATE_LAST_ORDER,ISO)
    IN_QTY_ONHAND
    INTO :D2INVEN-REC.IN-PART-NO,
    :D2INVEN-REC.IN-DATE-LAST-UPDTE 
    :IND-D2INVEN-REC.IN-DATE-LAST-UPDTE,
    :WS-TIME-RETURN :WS-TIME-RETURN-IND,
    :WS-CHAR-RETURN :WS-CHAR-RETURN-IND,
    :D2INVEN-REC.IN-QTY-ONHAND 
    :IND-D2INVEN-REC.IN-QTY-ONHAND
    FROM AUTHID.D2INVTRY
    WHERE IN_PART_NO = '23432'
END-EXEC.

Note: In this last example, the field IN_DATE_LAST_UPDTE is of type DATE. Because the result field WS-TIME-RETURN is followed by Y and the TIME function can return a null value, AMB creates both a result field with a picture appropriate for a TIME data type and a WS-TIME-RETURN-IND indicator in Working-Storage. Because the result field WS-CHAR-RETURN is not followed by Y and the CHAR function can return a null value, you must create both the result field and the WS-CHAR-RETURN-IND indicator variable in Working-Storage.