SYMPTOM
In certain situations Strategy SQL Engine generates seemingly illogical SQL "where 1 = 2" which causes no data to be returned.
STEPS TO REPRODUCE
Consider the following example shown in the image below: a report with Rank in the metric qualification is executed against a database that does not support a ranking method will generate the above where clause.
If this report is executed against a database that supports ranking (such as Oracle 9i), the following SQL is generated, and no data is returned:
create table ZZAM00 nologging as
select a12.region_id region_id,
rank () over( order by sum(a11.TOT_DOLLAR_SALES) desc nulls last) WJXBFS1
from CITY_CTR_SLS a11,
LU_CALL_CTR a12
where a11.CALL_CTR_ID = a12.CALL_CTR_ID
and a12.region_id = 111
group by a12.region_id
create table ZZMQ01 nologging as
select pa1.region_id region_id
from ZZAM00 pa1
where (pa1.WJXBFS1 <= 3.0)
select a12.region_id region_id,
a13.REGION_NAME REGION_NAME,
sum(a11.TOT_DOLLAR_SALES) alias1
from CITY_CTR_SLS a11,
LU_CALL_CTR a12,
ZZMQ01 pa2,
LU_REGION a13
where a11.CALL_CTR_ID = a12.CALL_CTR_ID and
a12.region_id = pa2.region_id and
a12.region_id = a13.region_id
and a12.region_id = 111
group by a12.region_id,
a13.REGION_NAME
drop table ZZAM00
drop table ZZMQ01
However, if the same report is executed against database that does NOT support ranking (such as MS SQL Server 2000), rank is evaluated using ODBC ranking. ODBC ranking is a default rank method set up as a VLDB property on the Database Instance level as shown in the image below:
In this case, the following SQL is generated:
select a12.region_id region_id,
sum(a11.TOT_DOLLAR_SALES) WJXBFS1
into #ZZMR00
from CITY_CTR_SLS a11
join LU_CALL_CTR a12
on (a11.CALL_CTR_ID = a12.CALL_CTR_ID)
where a12.region_id = 111
group by a12.region_id
A DBQualValue SQL. Can not be completed at this time.
select pa1.region_id region_id
into #ZZMQ01
from #ZZMR00 pa1
where (pa1.WJXBFS1 >= )
select a12.region_id region_id,
max(a13.REGION_NAME) REGION_NAME,
sum(a11.TOT_DOLLAR_SALES) alias1
from CITY_CTR_SLS a11
join LU_CALL_CTR a12
on (a11.CALL_CTR_ID = a12.CALL_CTR_ID)
join #ZZMQ01 pa2
on (a12.region_id = pa2.region_id)
join LU_REGION a13
on (a12.region_id = a13.region_id)
where a12.region_id = 111
group by a12.region_id
drop table #ZZMR00
drop table #ZZMQ01
The presence of the "DummyMQ" in the where clause is due to the fact that the SQL is resolved at run-time and that a dynamically created value is inserted in place of the "DummyMQ". The SQL for this report after it has been executed looks as follows:
Pass0 - Duration: 0:00:00.03
select a12.region_id region_id,
sum(a11.TOT_DOLLAR_SALES) WJXBFS1
into #ZZTP000LMB1MR000
from CITY_CTR_SLS a11
join LU_CALL_CTR a12
on (a11.CALL_CTR_ID = a12.CALL_CTR_ID)
where a12.region_id = 111
group by a12.region_id
Pass1 - Duration: 0:00:00.03
select pa1.WJXBFS1 WJXBFS1
from #ZZTP000LMB1MR000 pa1
order by 1 desc
Pass2 - Duration: 0:00:00.03
select pa1.region_id region_id
into #ZZTP000LMB1MQ001
from #ZZTP000LMB1MR000 pa1
where (1 = 2)
Pass3 - Duration: 0:00:00.03
select a12.region_id region_id,
max(a13.REGION_NAME) REGION_NAME,
sum(a11.TOT_DOLLAR_SALES) alias1
from CITY_CTR_SLS a11
join LU_CALL_CTR a12
on (a11.CALL_CTR_ID = a12.CALL_CTR_ID)
join #ZZTP000LMB1MQ001 pa2
on (a12.region_id = pa2.region_id)
join LU_REGION a13
on (a12.region_id = a13.region_id)
where a12.region_id = 111
group by a12.region_id
Pass4 - Duration: 0:00:00.06
drop table #ZZTP000LMB1MR000
Pass5 - Duration: 0:00:00.01
drop table #ZZTP000LMB1MQ001
CAUSE
Upon closer examination, the presence of this where clause can be justified by the lack of intermediate data returned by the preceding SQL passes.
Because Region with ID 111 does not exist in the Strategy Tutorial warehouse, the first (Pass0) and second (Pass1) SQL passes do not return any data.
Therefore, the third pass ensures that there is no data to be ranked over by generating "where (1 = 2)" clause. If some data was returned by the first and second passes, the where clause would reflect the actual rank cutoff value.
