Query: union on self

[KoliPoki]

Hello every body.

I have a small issue.

Problem: I have a table with 4 descriptor columns (type). I need to
formulate a query to retrieve a count for each type so I can group
by...etc. The view I have works, but doesn't work when I supplement the
query with some functions... they just don't like the UNION. The real
problem is I can't change any of the udf's or queries, just the view.
The view is inner joined back on to the primary table 'qt_ins' again
and a heap of other tables. But for this post and to not complicate it
too much I've just included the primary table and the view...
Also my querys work if I don't put a where clause on for the VIEW. eg:
.... and cv.type = 'Environmental' .... for some reason with a clause it
gets stuck in an *infinite loop.
Conditions: The table structure cannot be changed in anyway. The
view/query must return 2 columns qi_id & type.

I considered creating a function to return the Types but then I figured
I would ask you folks for a better way.

Any help with the view appreciated.

Thank you.

The below will create the table, with sample data and the view.

---------------------------Start
Query--------------------------------------------
CREATE TABLE [dbo].[qt_ins] (
[qi_id] [int] NOT NULL ,
[qi_injury] [bit] NULL ,
[qi_environmenta l] [bit] NULL ,
[qi_equipment_da mage] [bit] NULL ,
[qi_vehicle] [bit] NULL
) ON [PRIMARY]
GO

INSERT INTO qt_ins
(qi_id,qi_injur y,qi_environmen tal,qi_equipmen t_damage,qi_veh icle)
VALUES (20,1,0,0,0)
INSERT INTO qt_ins
(qi_id,qi_injur y,qi_environmen tal,qi_equipmen t_damage,qi_veh icle)
VALUES (21,0,1,0,0)
INSERT INTO qt_ins
(qi_id,qi_injur y,qi_environmen tal,qi_equipmen t_damage,qi_veh icle)
VALUES (23,1,0,0,0)
INSERT INTO qt_ins
(qi_id,qi_injur y,qi_environmen tal,qi_equipmen t_damage,qi_veh icle)
VALUES (24,1,0,0,0)
INSERT INTO qt_ins
(qi_id,qi_injur y,qi_environmen tal,qi_equipmen t_damage,qi_veh icle)
VALUES (25,1,1,1,1)
INSERT INTO qt_ins
(qi_id,qi_injur y,qi_environmen tal,qi_equipmen t_damage,qi_veh icle)
VALUES (26,1,0,0,0)
INSERT INTO qt_ins
(qi_id,qi_injur y,qi_environmen tal,qi_equipmen t_damage,qi_veh icle)
VALUES (27,1,0,0,0)
INSERT INTO qt_ins
(qi_id,qi_injur y,qi_environmen tal,qi_equipmen t_damage,qi_veh icle)
VALUES (28,1,0,0,0)
INSERT INTO qt_ins
(qi_id,qi_injur y,qi_environmen tal,qi_equipmen t_damage,qi_veh icle)
VALUES (29,1,0,0,0)
INSERT INTO qt_ins
(qi_id,qi_injur y,qi_environmen tal,qi_equipmen t_damage,qi_veh icle)
VALUES (30,1,1,1,1)
INSERT INTO qt_ins
(qi_id,qi_injur y,qi_environmen tal,qi_equipmen t_damage,qi_veh icle)
VALUES (31,1,0,0,0)
INSERT INTO qt_ins
(qi_id,qi_injur y,qi_environmen tal,qi_equipmen t_damage,qi_veh icle)
VALUES (32,1,1,1,0)
INSERT INTO qt_ins
(qi_id,qi_injur y,qi_environmen tal,qi_equipmen t_damage,qi_veh icle)
VALUES (33,1,0,0,0)
INSERT INTO qt_ins
(qi_id,qi_injur y,qi_environmen tal,qi_equipmen t_damage,qi_veh icle)
VALUES (34,1,1,1,0)
INSERT INTO qt_ins
(qi_id,qi_injur y,qi_environmen tal,qi_equipmen t_damage,qi_veh icle)
VALUES (35,1,0,0,1)
INSERT INTO qt_ins
(qi_id,qi_injur y,qi_environmen tal,qi_equipmen t_damage,qi_veh icle)
VALUES (36,1,0,0,0)
INSERT INTO qt_ins
(qi_id,qi_injur y,qi_environmen tal,qi_equipmen t_damage,qi_veh icle)
VALUES (37,0,0,0,1)
INSERT INTO qt_ins
(qi_id,qi_injur y,qi_environmen tal,qi_equipmen t_damage,qi_veh icle)
VALUES (38,0,0,0,1)
INSERT INTO qt_ins
(qi_id,qi_injur y,qi_environmen tal,qi_equipmen t_damage,qi_veh icle)
VALUES (39,0,1,0,0)
INSERT INTO qt_ins
(qi_id,qi_injur y,qi_environmen tal,qi_equipmen t_damage,qi_veh icle)
VALUES (40,1,0,0,0)
INSERT INTO qt_ins
(qi_id,qi_injur y,qi_environmen tal,qi_equipmen t_damage,qi_veh icle)
VALUES (41,0,0,0,0)
INSERT INTO qt_ins
(qi_id,qi_injur y,qi_environmen tal,qi_equipmen t_damage,qi_veh icle)
VALUES (42,1,0,0,0)
INSERT INTO qt_ins
(qi_id,qi_injur y,qi_environmen tal,qi_equipmen t_damage,qi_veh icle)
VALUES (43,1,0,0,0)
INSERT INTO qt_ins
(qi_id,qi_injur y,qi_environmen tal,qi_equipmen t_damage,qi_veh icle)
VALUES (44,0,1,1,0)
INSERT INTO qt_ins
(qi_id,qi_injur y,qi_environmen tal,qi_equipmen t_damage,qi_veh icle)
VALUES (45,1,0,0,0)
INSERT INTO qt_ins
(qi_id,qi_injur y,qi_environmen tal,qi_equipmen t_damage,qi_veh icle)
VALUES (46,1,0,0,0)
INSERT INTO qt_ins
(qi_id,qi_injur y,qi_environmen tal,qi_equipmen t_damage,qi_veh icle)
VALUES (47,1,0,0,0)
INSERT INTO qt_ins
(qi_id,qi_injur y,qi_environmen tal,qi_equipmen t_damage,qi_veh icle)
VALUES (48,1,0,0,0)
INSERT INTO qt_ins
(qi_id,qi_injur y,qi_environmen tal,qi_equipmen t_damage,qi_veh icle)
VALUES (49,1,0,0,0)
INSERT INTO qt_ins
(qi_id,qi_injur y,qi_environmen tal,qi_equipmen t_damage,qi_veh icle)
VALUES (50,1,0,1,0)
INSERT INTO qt_ins
(qi_id,qi_injur y,qi_environmen tal,qi_equipmen t_damage,qi_veh icle)
VALUES (51,0,0,1,0)
INSERT INTO qt_ins
(qi_id,qi_injur y,qi_environmen tal,qi_equipmen t_damage,qi_veh icle)
VALUES (52,0,1,0,0)
INSERT INTO qt_ins
(qi_id,qi_injur y,qi_environmen tal,qi_equipmen t_damage,qi_veh icle)
VALUES (53,1,0,0,0)
INSERT INTO qt_ins
(qi_id,qi_injur y,qi_environmen tal,qi_equipmen t_damage,qi_veh icle)
VALUES (54,1,0,0,0)
INSERT INTO qt_ins
(qi_id,qi_injur y,qi_environmen tal,qi_equipmen t_damage,qi_veh icle)
VALUES (55,1,0,0,0)
INSERT INTO qt_ins
(qi_id,qi_injur y,qi_environmen tal,qi_equipmen t_damage,qi_veh icle)
VALUES (56,1,1,1,1)
INSERT INTO qt_ins
(qi_id,qi_injur y,qi_environmen tal,qi_equipmen t_damage,qi_veh icle)
VALUES (57,1,0,0,0)
INSERT INTO qt_ins
(qi_id,qi_injur y,qi_environmen tal,qi_equipmen t_damage,qi_veh icle)
VALUES (58,1,0,0,0)
INSERT INTO qt_ins
(qi_id,qi_injur y,qi_environmen tal,qi_equipmen t_damage,qi_veh icle)
VALUES (59,0,1,0,0)
INSERT INTO qt_ins
(qi_id,qi_injur y,qi_environmen tal,qi_equipmen t_damage,qi_veh icle)
VALUES (60,0,1,0,0)
INSERT INTO qt_ins
(qi_id,qi_injur y,qi_environmen tal,qi_equipmen t_damage,qi_veh icle)
VALUES (61,1,0,0,0)
INSERT INTO qt_ins
(qi_id,qi_injur y,qi_environmen tal,qi_equipmen t_damage,qi_veh icle)
VALUES (62,0,1,0,0)
INSERT INTO qt_ins
(qi_id,qi_injur y,qi_environmen tal,qi_equipmen t_damage,qi_veh icle)
VALUES (63,1,0,0,0)
INSERT INTO qt_ins
(qi_id,qi_injur y,qi_environmen tal,qi_equipmen t_damage,qi_veh icle)
VALUES (64,1,0,0,0)
INSERT INTO qt_ins
(qi_id,qi_injur y,qi_environmen tal,qi_equipmen t_damage,qi_veh icle)
VALUES (65,1,0,1,0)
INSERT INTO qt_ins
(qi_id,qi_injur y,qi_environmen tal,qi_equipmen t_damage,qi_veh icle)
VALUES (66,1,0,0,1)
INSERT INTO qt_ins
(qi_id,qi_injur y,qi_environmen tal,qi_equipmen t_damage,qi_veh icle)
VALUES (67,1,1,1,1)
INSERT INTO qt_ins
(qi_id,qi_injur y,qi_environmen tal,qi_equipmen t_damage,qi_veh icle)
VALUES (68,1,0,0,0)
INSERT INTO qt_ins
(qi_id,qi_injur y,qi_environmen tal,qi_equipmen t_damage,qi_veh icle)
VALUES (69,1,0,0,0)
INSERT INTO qt_ins
(qi_id,qi_injur y,qi_environmen tal,qi_equipmen t_damage,qi_veh icle)
VALUES (70,1,1,1,1)
INSERT INTO qt_ins
(qi_id,qi_injur y,qi_environmen tal,qi_equipmen t_damage,qi_veh icle)
VALUES (71,1,1,1,1)
INSERT INTO qt_ins
(qi_id,qi_injur y,qi_environmen tal,qi_equipmen t_damage,qi_veh icle)
VALUES (72,1,1,1,1)
INSERT INTO qt_ins
(qi_id,qi_injur y,qi_environmen tal,qi_equipmen t_damage,qi_veh icle)
VALUES (73,0,0,1,0)
INSERT INTO qt_ins
(qi_id,qi_injur y,qi_environmen tal,qi_equipmen t_damage,qi_veh icle)
VALUES (81,1,0,0,0)
GO
CREATE VIEW dbo.v_qt_in_typ e
AS
SELECT qi_id, 'Injury' AS type FROM qt_ins WHERE qi_injury = 1
UNION all
SELECT qi_id, 'Environmental' AS type FROM qt_ins WHERE
qi_environmenta l = 1
UNION all
SELECT qi_id, 'Equipment damage' AS type FROM qt_ins WHERE
qi_equipment_da mage = 1
UNION all
SELECT qi_id, 'Vehicle' AS type FROM qt_ins WHERE qi_vehicle = 1
GO

select count(*),type from v_qt_in_type group by type
---------------------------END
QUERY--------------------------------------

[Erland Sommarskog]

KoliPoki (ra****@gmail.c om) writes:

Problem: I have a table with 4 descriptor columns (type). I need to
formulate a query to retrieve a count for each type so I can group
by...etc. The view I have works, but doesn't work when I supplement the
query with some functions... they just don't like the UNION. The real
problem is I can't change any of the udf's or queries, just the view.
The view is inner joined back on to the primary table 'qt_ins' again
and a heap of other tables. But for this post and to not complicate it
too much I've just included the primary table and the view...
Also my querys work if I don't put a where clause on for the VIEW. eg:
... and cv.type = 'Environmental' .... for some reason with a clause it
gets stuck in an *infinite loop.

I'm afraid that it's impossible to assist with the information you have
given. First you say "don't like the UNION", which indicates that you
have some trivial syntax error. Then you talk about infinite loops,
which indicates that the query runs for a very long time.

You posted a repro, which is great. Unfortunately, that repro appears to
work without any problems. If you instead post a repro that demonstrates
the problem, it's a lot easier to say what is going on.

--
Erland Sommarskog, SQL Server MVP, es****@sommarsk og.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

[KoliPoki]

Erland, thank you for your reply.

That's correct the UNION doesn't work with my query.
Unfortunately it's very hard to give you all the code as there are 3
functions and another 12 tables, 2 with hierarchical
(parent/child/lineage) data (Business units, Job titles).

There are no syntax errors, the problem is that my query never
returns... like it's stuck in a loop. If I remove the union like:
-----------------------------------------------
CREATE VIEW dbo.v_qt_in_typ e
AS
SELECT qi_id, 'Environmental' AS type FROM qt_ins WHERE
qi_environmenta l = 1
-------------------------------------------------
it works fine and fast, but is obviously incorrect as it only returns
records of 1 type.
So the problem is....

Can any one think of another way to write the below query without the
UNION

---------------Query to write in a different way---------------

SELECT qi_id, 'Injury' AS type FROM qt_ins WHERE qi_injury = 1
UNION all
SELECT qi_id, 'Environmental' AS type FROM qt_ins WHERE
qi_environmenta l = 1
UNION all
SELECT qi_id, 'Equipment damage' AS type FROM qt_ins WHERE
qi_equipment_da mage = 1
UNION all
SELECT qi_id, 'Vehicle' AS type FROM qt_ins WHERE qi_vehicle = 1

------------------------END QUERY-------------------------------------

Thanks for your help, and apologies for not being able to provider the
full context of the issue.

R.

Erland Sommarskog wrote:

KoliPoki (ra****@gmail.c om) writes:

Problem: I have a table with 4 descriptor columns (type). I need to
formulate a query to retrieve a count for each type so I can group
by...etc. The view I have works, but doesn't work when I supplement the
query with some functions... they just don't like the UNION. The real
problem is I can't change any of the udf's or queries, just the view.
The view is inner joined back on to the primary table 'qt_ins' again
and a heap of other tables. But for this post and to not complicate it
too much I've just included the primary table and the view...
Also my querys work if I don't put a where clause on for the VIEW. eg:
... and cv.type = 'Environmental' .... for some reason with a clause it
gets stuck in an *infinite loop.

I'm afraid that it's impossible to assist with the information you have
given. First you say "don't like the UNION", which indicates that you
have some trivial syntax error. Then you talk about infinite loops,
which indicates that the query runs for a very long time.

You posted a repro, which is great. Unfortunately, that repro appears to
work without any problems. If you instead post a repro that demonstrates
the problem, it's a lot easier to say what is going on.

--
Erland Sommarskog, SQL Server MVP, es****@sommarsk og.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

[Alexander Kuznetsov]

You can try something like this:

SELECT qi_id,
case
when qi_injury = 1 then 'Injury'
when qi_environmenta l = 1 then 'environmental'
when qi_Equipment damage = 1 then 'Equipment damage'
when qi_Vehicle = 1 then 'Vehicle'
end
AS type
FROM qt_ins
WHERE (qi_injury = 1) or (qi_environment al = 1 ) or
(qi_equipment_d amage = 1)
or (qi_vehicle = 1)

But it would be interesting to see why is your original query slow. Any
additional infotrmation?

[Thomas R. Hummel]

KoliPoki wrote:

Erland, thank you for your reply.

That's correct the UNION doesn't work with my query.
Unfortunately it's very hard to give you all the code as there are 3
functions and another 12 tables, 2 with hierarchical
(parent/child/lineage) data (Business units, Job titles).

There are no syntax errors, the problem is that my query never
returns... like it's stuck in a loop. If I remove the union like:
-----------------------------------------------
CREATE VIEW dbo.v_qt_in_typ e
AS
SELECT qi_id, 'Environmental' AS type FROM qt_ins WHERE
qi_environmenta l = 1
-------------------------------------------------
it works fine and fast, but is obviously incorrect as it only returns
records of 1 type.
So the problem is....

Can any one think of another way to write the below query without the
UNION

---------------Query to write in a different way---------------

SELECT qi_id, 'Injury' AS type FROM qt_ins WHERE qi_injury = 1
UNION all
SELECT qi_id, 'Environmental' AS type FROM qt_ins WHERE
qi_environmenta l = 1
UNION all
SELECT qi_id, 'Equipment damage' AS type FROM qt_ins WHERE
qi_equipment_da mage = 1
UNION all
SELECT qi_id, 'Vehicle' AS type FROM qt_ins WHERE qi_vehicle = 1

------------------------END QUERY-------------------------------------

Thanks for your help, and apologies for not being able to provider the
full context of the issue.

Alexander has given what I think is a pretty good solution to this. To
the original problem though, did you get a query plan for the select
statement that ran for so long? What was the query plan doing?

One thing to keep in mind is that if you are using UDFs, they can often
dramatically impact a querie's performance. It's impossible to know
without your data and the full table, UDF, and view SQL code, but maybe
when you use the UNION with the UDF it has to actually select the
entire dataset, applying the UDF to each row and then performing the
UNION operations before it is able to filter out rows based on your
WHERE clause criteria. I'm afraid that I don't know how smart SQL
Server is when you do a union like this within a view and then select
from the view based on a column that is calculated within the view.
Even if the UDF has nothing to do with it, it may be that when you
select WHERE qi_environmenta l = 1 then SQL Server can use an index, but
when you have a column hard-coded the way that you do in the unions it
can no longer use that index when you do WHERE type = 'Environmental' .

Anyway, just some stuff to look into for you.

-Tom.

[Thomas R. Hummel]

Alexander Kuznetsov wrote:

You can try something like this:

SELECT qi_id,
case
when qi_injury = 1 then 'Injury'
when qi_environmenta l = 1 then 'environmental'
when qi_Equipment damage = 1 then 'Equipment damage'
when qi_Vehicle = 1 then 'Vehicle'
end
AS type
FROM qt_ins
WHERE (qi_injury = 1) or (qi_environment al = 1 ) or
(qi_equipment_d amage = 1)
or (qi_vehicle = 1)

But it would be interesting to see why is your original query slow. Any
additional infotrmation?

I just posted that this was a good solution in another post, but now it
occurs to me that it will not return the same resultset as the original
poster's solution. The query above will return only one row per qi_id,
but the original solution could return up to four for each qi_id.

-Tom.

[Alexander Kuznetsov]

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_environment al = 1 and t.n=2) or
(qi_equipment_d amage = 1 and t.n=3)
or (qi_vehicle = 1 and t.n=4)

[KoliPoki]

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_subor dinates('49549' ))
and
q.qi_observatio n_date>=dateadd (yy,-1,current_times tamp) and
v.type = 'Environmental' and
dbo.udf_qi_em_r eturn_above_rep ortsto2_1(q.qi_ mr_emp_no,'4954 9') = 'GM
OH&S'
But would return if either
dbo.udf_qi_em_r eturn_above_rep ortsto2_1(q.qi_ mr_emp_no,'4954 9') = '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_subor dinates('49549' ))
and
q.qi_observatio n_date>=dateadd (yy,-1,current_times tamp) and
--v.type = 'Environmental' and
dbo.udf_qi_em_r eturn_above_rep ortsto2_1(q.qi_ mr_emp_no,'4954 9') = '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_environment al = 1 and t.n=2) or
(qi_equipment_d amage = 1 and t.n=3)
or (qi_vehicle = 1 and t.n=4)