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:

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