Examples

Click on this push button to display samples of valid SQL expressions.

You can use the following operators in an SQL expression for a computed column or a WHERE clause. All normal restrictions apply to the operators. See your database's SQL reference guide for information about additional operators and restrictions for your database.

Operators:

CONCAT

||

/

*

+

-

( )

Column functions:

AVG

COUNT

MAX

MIN

SUM

Scalar functions:

CHAR

DATE, DAY, DAYS

DECIMAL

FLOAT

HOUR

INTEGER

LENGTH

MICROSECOND

MINUTE

MONTH

SECOND

SUBSTR

TIME

TIMESTAMP

TRANSLATE

VALUE

VARGRAPHIC

YEAR

Predicate comparison operators:

=

<>

<

>

<=

>=

Predicate quantifiers:

SOME

ANY

ALL

Special registers:

CURRENT DATE

CURRENT SERVER

CURRENT TIME

CURRENT TIMESTAMP

CURRENT TIMEZONE

USER

Sample SQL expressions:

• Expression with a column function

  MAX_BILLED = MAX(QUANTITY_BILLED)
  

• Expression with an operator

  CURRENT_BALANCE = AMOUNT_OWED - PAYMENT_AMOUNT
  

• Expression with multiple operators

  PERCENT_CHANGE = ((COLUMN1 - COLUMN2) * 100)/COLUMN2
  

• Expression with a scalar function and a special register value

  LAST_UPDATE = DATE(CURRENT TIMESTAMP)
  

• Expression that requires a GROUP BY clause

  Scenario: You selected the following columns and added them to the target columns list: AVG_PRICE, SALESID, and UNIT_PRICE. You now want to add a column that contains a summary of the AVG_PRICE column. The new column is named SUMMARY_AVGPRICE and the expression is derived from the SQL expression:

  SUM(AVG_PRICE)

  1. Enter the new column name in the Column name field of the Create Column window:

     SUMMARY_AVGPRICE
     

     Because you selected more than one target table column name and used an SQL column function with a column name, you must enter a GROUP BY clause on the Rows page.

  2. In the WHERE field of the Rows page, enter:

     GROUP BY AVG_PRICE, SALESID, UNIT_PRICE