July 10, 2007

Concatenate big Default-Where-Clauses

The simplest way to concatenate a Where-Clause is this:

Example: An empty form with table DEPT. Let's say you have three rules, which controls the concatenation of the blocks Default-Where. The problem is: From the second IF on you have to check the value of V_Default_Where. If it is filled you must concatenate ' AND ' before each new part of the string.


DECLARE
V_Default_Where VARCHAR2 (2000);
BEGIN
IF Rule_1_is_TRUE THEN
V_Default_Where := 'DEPTNO IN (10, 20, 30)';
END IF;

IF Rule_2_is_TRUE THEN
IF V_Default_Where IS NOT NULL THEN
V_Default_Where := V_Default_Where ||
' AND DNAME != ''SALES'' ';
ELSE
V_Default_Where := 'DNAME != ''SALES'' ';
END IF;
END IF;

IF Rule_3_is_TRUE THEN
IF V_Default_Where IS NOT NULL THEN
V_Default_Where := V_Default_Where ||
' AND LOC IS NOT NULL';
ELSE
V_Default_Where := 'LOC IS NOT NULL';
END IF;
END IF;

Set_Block_Property ('DEPT', DEFAULT_WHERE,
V_Default_Where);
Go_Block ('DEPT');
Execute_Query;
END;

These IF's are not maintainable. Changes in the where-clauses result in two changes in the code:

...
V_Default_Where := V_Default_Where ||
' AND LOC IS NULL';
ELSE
V_Default_Where := 'LOC IS NULL';
...

A better approach is to use the ' AND ' at the beginning of each concatenation:

DECLARE
V_Default_Where VARCHAR2 (2000);
BEGIN
IF Rule_1_is_TRUE THEN
V_Default_Where := ' AND DEPTNO IN (10, 20, 30)';
END IF;

IF Rule_2_is_TRUE THEN
V_Default_Where := V_Default_Where ||
' AND DNAME != ''SALES'' ';
END IF;

IF Rule_3_is_TRUE THEN
V_Default_Where := V_Default_Where ||
' AND LOC IS NOT NULL';
END IF;

Set_Block_Property ('DEPT', DEFAULT_WHERE,
Substr (V_Default_Where, 6));
Go_Block ('DEPT');
Execute_Query;
END;


the Substr (V_Default_Where, 6) eliminates the first ' AND '.

Really easy and readable code!

PS: Read in the comments, why I didn't use "1=1"

3 comments:

John Scott said...

The other option is to add 'WHERE 1 = 1' to the end of your query, that way you don't need to use the SUBSTR to remove the first 'AND'.

John.

Jornica said...

Alternative solution: add a default value 1=1:

V_Default_Where VARCHAR2 (2000) := '1=1 ';

No substring needed.

With kind regards,

Jornica

Gerd Volberg said...

1=1 is the good old way to do it. True.

I showed a method, that works pure. Means: The where clause has only those components, which are needed. The execution plan is the original