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:
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.
Alternative solution: add a default value 1=1:
V_Default_Where VARCHAR2 (2000) := '1=1 ';
No substring needed.
With kind regards,
Jornica
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
Post a Comment