if-then-else

Syntax

IF [NOT] 
   { Literal1 | :BindVariable1 } 
   { =  | IN } 
   { Literal2 | :BindVariable2 | SelectStatement } 
 THEN "ThenCommands" 
 [ ELSE "ElseCommands" ] ;

The ttIsql IF-THEN-ELSE command has the parameters:

Parameter	Description
`IF`	The `IF` command must end in a semicolon (`;`). The `IF` command fails if improper syntax is given, the `BindVariables` do not exist or the `SELECT` statement fails to run or does not return just a single column.
`NOT`	Using `NOT` reverses the desired result of the condition.
`Literal1`, `Literal2`	A value that can be part of a comparison.
`BindVariable1`, `BindVariable2`	A bind variable is equivalent to a parameter. You can use the `:BindVariable1` notation for passing bind variables into this construct. The variable can be created and set using the `variable` or `setvariable` `ttIsql` commands.
= \| `IN`	You can use the `IN` operator only with the `SelectStatement`. You can use the `IN` operator with zero or more returned rows. You can use the equal (`=`) operator only with a single returned row.
`SelectStatement`	A provided `SELECT` statement must start with `SELECT`. The `SELECT` statement can return only one column. In addition, it can return only one row when the equal (`=`) operator is provided. The `SelectStatement` is not available if you are not connected to the database.
`ThenCommands`, `ElseCommands`	All commands in the `THEN` or `ELSE` clauses must be delimited by a semicolon and cannot contain embedded double quotes. These clauses can conditionally run `ttIsql` commands, such as `host` or `run`, which cannot be run through PL/SQL. You can use the `CALL` statement within the `THEN` or `ELSE` clauses. You cannot use PL/SQL blocks.

Description

The if-then-else command construct enables you to implement conditional branching logic in a ttIsql session. The IF command tests a condition and decides whether to run commands within the THEN clause or the optional ELSE clause. The commands can be SQL statements, SQL scripts, PL/SQL blocks, or TimesTen utilities.

Example

Command> if :a = 1 then "e:a is 1"  else "e:a is not 1";

The following example creates and tests a bind variable to see which type of locking is enabled for the TimesTen database. It uses the autovariables command to create the bind variable from the result of the call to ttConfiguration. The value can be tested within the IF-THEN-ELSE conditional by testing the paramvalue variable.

Command> SET AUTOVARIABLES ON;
Command> CALL TTCONFIGURATION('LockLevel');
PARAMNAME, PARAMVALUE
< LockLevel, 0 >
1 row found.
Command> IF :paramvalue = 1 THEN "e:Database-level locking is enabled"
> ELSE "e:Row-level locking is enabled";
Row-level locking is enabled

The following example checks to see that the employees table exists. If it does not, it runs the SQL script that creates the employees table; otherwise, a message is printed out.

Command> IF 0 = "SELECT COUNT(*) FROM SYS.TABLES 
WHERE TBLNAME LIKE 'employees';"
THEN "e:EMPLOYEES table already exists"
ELSE "@HR_CRE_TT.SQL;";
EMPLOYEES table already exists

Restrictions for the IF-THEN-ELSE construct are as follows:

You cannot compare variables of the LOB data type.
The values are compared case-sensitive with strcmp. A character padded value might not match a VARCHAR2 because of the padding.