The Mysteries of NULL values, Part 2

So, if we are talking about the OLTP environment, and we want to generate an efficient SQL statement executed from our application, where the user can choose a random set of search criteria, and any of criteria can be omitted. In this case  the process of SQL generation (no matter, whether we generate it in the stored procedure, db function or in the programming language method) should look like sketched below, For the first iteration we assume that we only have four tables to join, and that a select list contains attributes from all 4 tables, so that none can be omitted

DECLARE v_select TEXT;

v_select_list TEXT;

v_conditions TEXT;

BEGIN

v_select_list:=<select list>

v_select:=v_select_list ||

‘FROM t1

LEFT OUTER JOIN t2 ON t2.id=t1.t2_id

LEFT OUTER JOIN t3 ON t3.id=t1.t3_id

LEFT OUTER JOIN t4 ONt4.id=t1.t4-id

WHERE‘;

/*Now we start to generate a condition. Remember, that any of the lists of values of any of the attributes can be NULL. */

IF v_string1 IS NOT NULL THEN

v_condition:=’attr1 IN(‘||v_string1||’)’;

AND IF; — first condition added

IF v_string2 IS NOT NULL THEN

IF v_condition is NOT NULL THEN – if we already have something in conditions

v_condition:=v_condition|| “AND ‘ –then we need to add ‘AND’

END IF;

V_coundition :=v_condition||

‘attr2 IN (‘||v_string2||’)’;

END IF;

Then you need to repeat a similar sequence of actions for tables t3  and t4.

Yes, it has to be written in such a “non-automated” way, unless you can write something, which will analyze this list of tables more or less “automatically”  in less time than this query generation…

And yes, if some of the tables do not contribute to the select list and at the same time the joins with them are not semi-joins, we can skip the join altogether, if the user did not select any conditions on this table. And yes, that means, that you also need to generate the from-list differently, depending on whether you need all tables in the join, or not… And yes, execution seconds may depend on it…

And at the end you append conditions to the select string:

v_select :=v_select||v_condition

And now happily execute!

Advertisements

Leave a comment

Filed under Data management, SQL

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s