Rocket® Enterprise Developer (formerly a product of Micro Focus or formerly a product of Open Text) documentation, created before Rocket Software acquired certain products from OpenText, may include outdated references to "Micro Focus" or "OpenText", both of which are trademarks of OpenText or its affiliates. Rocket Software is not affiliated with Micro Focus or OpenText and has since rebranded these products as Rocket® products. You may also encounter outdated links in this documentation. If you do, please contact Rocket Support (support@rocketsoftware.com) for assistance.
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