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_type
AS
SELECT qi_id, 'Environmental' AS type FROM qt_ins WHERE
qi_environmental = 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_environmental = 1
UNION all
SELECT qi_id, 'Equipment damage' AS type FROM qt_ins WHERE
qi_equipment_damage = 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 (rayone@gmail.com) 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, esquel@sommarskog.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