Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Union All?

Author  Topic 

WJHamel
Aged Yak Warrior

651 Posts

Posted - 2012-03-26 : 12:39:24
I want the following query (which i'm going to add many more extensions to) to output one result set. I assume i need to use a UNION ALL clause, but not sure where to do so:



--Complaint query
SELECT cfs.CFSID,
cfs.ReceivedTime,
ISNULL(cfs.OtherAgency, 0) AS OtherAgency,
cfs.OccuredStart,
cfs.OccuredEnd,
cd.ReportCode,
CONVERT(CHAR(15), cfs.ReceivedTime, 101) AS CallDate,
NAME = ISNULL(pd.LastName, '') + ', ' + ISNULL(pd.FirstName, '')
FROM CombinedDesc cd,
CFSData cfs,
PersonData pd
WHERE cfs.CallTypeID = cd.ID
AND cfs.CFSID = pd.FormID
AND pd.FormTypeID = 10000
--AND pd.LastName LIKE '%Br%'
--AND pd.FirstName LIKE '%h%'
--AND cfs.OccuredStart < '03/27/2010'
--AND cfs.OccuredStart >= '02/25/2012 0:00:00'
ORDER BY
cfs.CFSID DESC

--Aggravated assault query:
SELECT cfs.CFSID,
cfs.ReceivedTime,
ISNULL(cfs.OtherAgency, 0) AS OtherAgency,
cfs.OccuredStart,
cfs.OccuredEnd,
cd.ReportCode,
CONVERT(CHAR(15), cfs.ReceivedTime, 101) AS CallDate,
cn.Description AS CallNature,
NAME = ISNULL(pd.LastName, '') + ', ' + ISNULL(pd.FirstName, '')
FROM CombinedDesc cd,
CFSData cfs,
CombinedDesc cn,
PersonData pd
WHERE cfs.CallTypeID = cd.ID
AND cfs.NatureOfCallID = cn.ID
AND cfs.NatureOfCallID = 4367
AND cfs.CFSID = pd.FormID
AND pd.FormTypeID = 10000

ORDER BY
cfs.CFSID DESC

--911 Hangup query
SELECT cfs.CFSID,
cfs.ReceivedTime,
ISNULL(cfs.OtherAgency, 0) AS OtherAgency,
cfs.OccuredStart,
cfs.OccuredEnd,
cd.ReportCode,
CONVERT(CHAR(15), cfs.ReceivedTime, 101) AS CallDate,
cn.Description AS CallNature,
NAME = ISNULL(pd.LastName, '') + ', ' + ISNULL(pd.FirstName, '')
FROM CombinedDesc cd,
CFSData cfs,
CombinedDesc cn,
PersonData pd
WHERE cfs.CallTypeID = cd.ID
AND cfs.NatureOfCallID = cn.ID
AND cfs.NatureOfCallID = 1944
AND cfs.CFSID = pd.FormID
AND pd.FormTypeID = 10000

ORDER BY
cfs.CFSID DESC

--911 Misdial:

SELECT cfs.CFSID,
cfs.ReceivedTime,
ISNULL(cfs.OtherAgency, 0) AS OtherAgency,
cfs.OccuredStart,
cfs.OccuredEnd,
cd.ReportCode,
CONVERT(CHAR(15), cfs.ReceivedTime, 101) AS CallDate,
cn.Description AS CallNature,
NAME = ISNULL(pd.LastName, '') + ', ' + ISNULL(pd.FirstName, '')
FROM CombinedDesc cd,
CFSData cfs,
CombinedDesc cn,
PersonData pd
WHERE cfs.CallTypeID = cd.ID
AND cfs.NatureOfCallID = cn.ID
AND cfs.NatureOfCallID = 1944
AND cfs.CFSID = pd.FormID
AND pd.FormTypeID = 10000

ORDER BY
cfs.CFSID DESC

--Abandoned Vehicle:


SELECT cfs.CFSID,
cfs.ReceivedTime,
ISNULL(cfs.OtherAgency, 0) AS OtherAgency,
cfs.OccuredStart,
cfs.OccuredEnd,
cd.ReportCode,
CONVERT(CHAR(15), cfs.ReceivedTime, 101) AS CallDate,
cn.Description AS CallNature,
NAME = ISNULL(pd.LastName, '') + ', ' + ISNULL(pd.FirstName, '')
FROM CombinedDesc cd,
CFSData cfs,
CombinedDesc cn,
PersonData pd
WHERE cfs.CallTypeID = cd.ID
AND cfs.NatureOfCallID = cn.ID
AND cfs.NatureOfCallID = 1361
AND cfs.CFSID = pd.FormID
AND pd.FormTypeID = 10000

ORDER BY
cfs.CFSID DESC


--Advise calls:

SELECT cfs.CFSID,
cfs.ReceivedTime,
ISNULL(cfs.OtherAgency, 0) AS OtherAgency,
cfs.OccuredStart,
cfs.OccuredEnd,
cd.ReportCode,
CONVERT(CHAR(15), cfs.ReceivedTime, 101) AS CallDate,
cn.Description AS CallNature,
NAME = ISNULL(pd.LastName, '') + ', ' + ISNULL(pd.FirstName, '')
FROM CombinedDesc cd,
CFSData cfs,
CombinedDesc cn,
PersonData pd
WHERE cfs.CallTypeID = cd.ID
AND cfs.NatureOfCallID = cn.ID
AND cfs.NatureOfCallID = 1369
AND cfs.CFSID = pd.FormID
AND pd.FormTypeID = 10000

ORDER BY
cfs.CFSID DESC


thanks!

james

X002548
Not Just a Number

15586 Posts

Posted - 2012-03-26 : 12:44:25
Do you want to know which query produced the output as well?

In any case, if all the columns are the same and they all have the same data type, just add UNION ALL Before your comments

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx


Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/


Go to Top of Page

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2012-03-26 : 12:49:01
And remove all the ORDER BYs except for the last one.
Go to Top of Page

WJHamel
Aged Yak Warrior

651 Posts

Posted - 2012-03-26 : 13:00:40
Brett: no, i don't 'really' need to know which query produced which, but lets say I did. What would i mod to make that happen? I'm assuming an alias for each resultset (AS)?
Go to Top of Page

X002548
Not Just a Number

15586 Posts

Posted - 2012-03-26 : 13:03:41
[code]

--Complaint query

SELECT 'Complaint query' AS qry_SOURCE,
cfs.CFSID,
cfs.ReceivedTime,
ISNULL(cfs.OtherAgency, 0) AS OtherAgency,
cfs.OccuredStart,
cfs.OccuredEnd,
cd.ReportCode,
CONVERT(CHAR(15), cfs.ReceivedTime, 101) AS CallDate,
NAME = ISNULL(pd.LastName, '') + ', ' + ISNULL(pd.FirstName, '')
FROM CombinedDesc cd,
CFSData cfs,
PersonData pd
WHERE cfs.CallTypeID = cd.ID
AND cfs.CFSID = pd.FormID
AND pd.FormTypeID = 10000

UNION ALL

--Aggravated assault query:

SELECT 'ggravated assault' AS qry_SOURCE,
cfs.CFSID,
cfs.ReceivedTime,
ISNULL(cfs.OtherAgency, 0) AS OtherAgency,
cfs.OccuredStart,
cfs.OccuredEnd,
cd.ReportCode,
CONVERT(CHAR(15), cfs.ReceivedTime, 101) AS CallDate,
cn.Description AS CallNature,
NAME = ISNULL(pd.LastName, '') + ', ' + ISNULL(pd.FirstName, '')
FROM CombinedDesc cd,
CFSData cfs,
CombinedDesc cn,
PersonData pd
WHERE cfs.CallTypeID = cd.ID
AND cfs.NatureOfCallID = cn.ID
AND cfs.NatureOfCallID = 4367
AND cfs.CFSID = pd.FormID
AND pd.FormTypeID = 10000

UNION ALL

ect

[/code]


Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx


Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/


Go to Top of Page

WJHamel
Aged Yak Warrior

651 Posts

Posted - 2012-03-26 : 13:14:39
Yep. kinda what i figured. Thanks. Also noticed that i have to make sure the number of arguments is equal in each part of the query for the unions to work. Working out nicely. Thanks again.
Go to Top of Page

WJHamel
Aged Yak Warrior

651 Posts

Posted - 2012-03-26 : 13:15:47
Noticing also that it takes exponentially longer for the query to execute with the unions. Would indexes help that at all?
Go to Top of Page

X002548
Not Just a Number

15586 Posts

Posted - 2012-03-26 : 13:18:37
It probably will be less time...if you added all the time up for each query as compared to running the all at once



In this case, you might want to use a temp table and get all the like info in 1 shot, then use then temp table in your unions



Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx


Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/


Go to Top of Page

WJHamel
Aged Yak Warrior

651 Posts

Posted - 2012-03-26 : 13:30:17
yeah, this is going to be problematic because each part of the query returns almost 60,000,000 records and in the end, i will have approximately 30 sections to this query. I wonder if it won't be more efficient to simply incorporate all of the WHERE clauses into one query.
Go to Top of Page

X002548
Not Just a Number

15586 Posts

Posted - 2012-03-26 : 13:34:22
One, problem I see...


1st your JOINS are Not ANSI
2nd I don't see where you are joing all of your tables

Please change the to the format

FROM Table1 t1
INNER JOIN Table2 t2
ON t1.key = t2.key

ect

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx


Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/


Go to Top of Page
   

- Advertisement -