Posted by KoliPoki on 07/12/06 06:56

Thanks Folks.

I was dubious of using a derived table, as I had already tried wrapping
some of the logic in derived table and it didn't work, but I never
tried using a derived table for the view...guess what, it work!

Why, I'm still not sure.

The query was never returning when the view and a function were used in
the clause of the query.

Example of not working:
select qi_id, .....
from qt_ins q
inner join v_qt_in_type v on q.qu_id = v.qi_id
inner join ...
inner join ...
....
where
..... and
q.qi_mr_emp_no in(select emp_no from udf_qi_my_subordinates('49549'))
and
q.qi_observation_date>=dateadd(yy,-1,current_timestamp) and
v.type = 'Environmental' and
dbo.udf_qi_em_return_above_reportsto2_1(q.qi_mr_emp_no,'49549') = 'GM
OH&S'


But would return if either
dbo.udf_qi_em_return_above_reportsto2_1(q.qi_mr_emp_no,'49549') = 'GM
OH&S' or v.type = 'Environmental' was removed it would work even if
they were referenced the select string.

Example working:
select qi_id, .....
from qt_ins q
inner join v_qt_in_type v on q.qu_id = v.qi_id
inner join ...
inner join ...
....
where
..... and
q.qi_mr_emp_no in(select emp_no from udf_qi_my_subordinates('49549'))
and
q.qi_observation_date>=dateadd(yy,-1,current_timestamp) and
--v.type = 'Environmental' and
dbo.udf_qi_em_return_above_reportsto2_1(q.qi_mr_emp_no,'49549') = 'GM
OH&S'

Thanks Alexander for your help.


Alexander Kuznetsov wrote:
> Thomas,
>
> thanks for the correction. Untested again, no DDL, no DML!
>
> SELECT qi_id, t.c AS type
> FROM qt_ins join
> (
> select 1 n, 'Injury' c
> union all
> select 2 n, 'environmental' c
> union all
> select 3 n, 'Equipment damage' c
> union all
> select 4 n, 'Vehicle' c
> ) t
> on (qi_injury = 1 and t.n=1) or (qi_environmental = 1 and t.n=2) or
> (qi_equipment_damage = 1 and t.n=3)
> or (qi_vehicle = 1 and t.n=4)

[Back to original message]