Previous Topic Next topic Print topic


SQL Expressions

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

SQL supports expressions for the DB-DECLARE, DB-OBTAIN, and DB-PROCESS calls.

Target:

SQL

Sample Expressions:

Expression Return INTO Host Null Indicator
INTEGER (DOB) - INTEGER(PERF - 1) (WS-RET-INT) WS-RET-INT IND-DOB
CURRENT DATE - 1 DAY (WS-CURRDATE) WS-CURRDATE --------
RATE + (DED / 2.0) RATE IND-RATE
YTD + RATE + DED / 2 YTD IND-YTD
PERF / 2 PERF IND-PERF
INTEGER(YTD - DED) * INTEGER(RATE) (WS-INT-FLD) WS-INT-FLD INT-YTD
HIREDTE + YEAR(CURRENT DATE) YEARS HIREDTE IND-HIREDTE
SUM(YTD) / 2 YTD IND-YTD
AVG(DED - 1) / 5 (WS-AVG-DED) WS-AVG-DED IND-DED
SUM(RATE) + SUM(DED) (WS-RATE-DED) WS-RATE-DED IND-RATE

General Rule:

  1. If an INTO variable is not specified, the result returns to the host variable for the first column.

Example:

DB-OBTAIN REC TABLE1
... RATE + (DED / 2.0)
... PERF / 2
... INTEGER(DOB) - INTEGER(PERF - 1) (WS-RET-INT)
... WHERE PERF > 100
DB-OBTAIN REC RECORD1-RED
... RATE + YEAR_TO_DATE              (WS-CALC-RATE)
... MONTH(HIREDATE) - DAY(HIREDATE)  (WS-DATE-RETURN
... AVG(RATE - 1) / 4                (WS-AVG-RATE)
... YEAR((CURRENT DATE - HIREDTE),WS-CALC-DATE,Y)
... CHAR((HIREDTE - 28 DAYS,USA),WS-DATE-AREA,Y)
... WHERE RATE > 7
Previous Topic Next topic Print topic