PowerWeb - SQL Anywhere Survival Guide

Using IF-THEN-ELSE in SELECTS

SQL Anywhere Survival Guide
This is one of those amazing features I was talking about. Did you know that the following is a valid Watcom-SQL statement -

SELECT

emp_fname,

emp_lname,

IF status = 'A' THEN

'ACTIVE'

ELSE

'TERMINATED'

ENDIF emp_status

FROM employee

Or even this one, which emulates a CASE kind of construct -

SELECT

emp_fname,

emp_lname,

IF status = 'A' THEN

'ACTIVE'

ELSE IF status = 'L' THEN

'ON LEAVE'

ELSE

'TERMINATED'

ENDIF ENDIF emp_status

FROM employee

Defining CHAR and VARCHAR AUTOINCREMENT COLUMNS

SQL Anywhere Survival Guide
Did you know that the SQL Anywhere column default AUTOINCREMENT can be applied to even CHAR and VARCHAR columns.
Example:

CREATE TABLE "dba"."auto"

("id" char(10) NOT NULL DEFAULT autoincrement,

"name" char(25) , PRIMARY KEY ("id"));

And that's not all, did you know that you can assign the default CURRENT TIMESTAMP to a CHAR and VARCHAR columns. SQL Anywhere takes care of the datatype conversion.
Example:

CREATE TABLE "dba"."auto"

("id" varchar(10) NOT NULL DEFAULT autoincrement,

"name" varchar(25) NOT NULL ,

"str_date" char(20) NOT NULL DEFAULT current timestamp

, PRIMARY KEY ("id"));

The basic behaviour seems to be that as long as implicit conversion between the datatypes is supported by SQL Anywhere the datatype of the default and the column can be different.

You can attach multiple triggers to the same event

SQL Anywhere Survival Guide
SQL Anywhere's support for triggers is truly fantastic-

Statement Level Triggers and Row Level Triggers
Before and After Triggers
Conditional Triggers
And even multiple triggers attached to the same event

You can even attach multiple triggers to same event(Insert, Delete & Update) and you can also specify the order in which the triggers are executed. Here's the syntax( or use the Trigger Wizard in SQL Central)-

CREATE TRIGGER "<trigger name>" BEFORE INSERT

ORDER <order no> ON "DBA"."<table name>"

[ REFERENCING NEW AS <new_name> ]

FOR EACH ROW

[ WHEN( search_condition ) ]

BEGIN

;

END

Obtaining TSQL source

SQL Anywhere Survival Guide
You can open a Trigger or a Stored Procedure in TSQL or Watcom SQL dialect. In SQL Central right click on the Trigger or Stored Procedure and then choose 'Open as Watcom-SQL' or 'Open as Transact-SQL'.

Back to my HomePage