Query Generation in OBIEE server

OBIEE Server will sometimes generate queries that look like this:

NVL(“Column1”,’q’)=NVL(“Column2”,’q’) and NVL(“Column1”,’z’) =NVL(“Column2”,’z’)

which seems a bit redundant – both the conditions are the same except for the constant q and z…

The reason both parts of this condition are required is to guard against “Column1” or “Column2” having the values ‘q’ or ‘z’ as legitimate values. Let us do a truth table:

 

Truth Table for NVL(“Column1”,’q’)=NVL(“Column2”,’q’)

	NULL	q	z	foo	bar
NULL	True	True
q	True	True
z			True
foo				True
bar					True

 

Note the values are true when Column1=’q’ and Column2 is NULL – or vice versa.

Truth Table for NVL(“Column1”,’z’)=NVL(“Column2”,’z’)

	NULL	q	z	foo	bar
NULL	True		True
q		True
z	True		True
foo				True
bar					True

This can be true if either column is ‘z’ and the other is NULL.

However, if we and the two, we get:

Truth Table for NVL(“Column1”,’q’)=NVL(“Column2”,’q’) and NVL(“Column1”,’z’)=NVL(“Column2”,’z’)

	NULL	q	z	foo	bar
NULL	True
q		True
z			True
foo				True
bar					True

 

which is equivalent to the much more readable:

“Column1”=”Column2” or (“Column1” is null and “Column2” is null)

In fact, the latter formulation is what OBIEE used to generate for these joins in previous versions.