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.
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 querySELECT 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 pdWHERE 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 pdWHERE 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 querySELECT 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 pdWHERE 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 pdWHERE 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 pdWHERE cfs.CallTypeID = cd.ID AND cfs.NatureOfCallID = cn.ID AND cfs.NatureOfCallID = 1361 AND cfs.CFSID = pd.FormID AND pd.FormTypeID = 10000ORDER 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 pdWHERE cfs.CallTypeID = cd.ID AND cfs.NatureOfCallID = cn.ID AND cfs.NatureOfCallID = 1369 AND cfs.CFSID = pd.FormID AND pd.FormTypeID = 10000ORDER BY cfs.CFSID DESC thanks!james |
|
X002548
Not Just a Number
15586 Posts |
|
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. |
|
|
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)? |
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2012-03-26 : 13:03:41
|
[code]--Complaint querySELECT '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 pdWHERE 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 pdWHERE cfs.CallTypeID = cd.ID AND cfs.NatureOfCallID = cn.ID AND cfs.NatureOfCallID = 4367 AND cfs.CFSID = pd.FormID AND pd.FormTypeID = 10000 UNION ALLect[/code]Brett8-)Hint: Want your questions answered fast? Follow the direction in this linkhttp://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspxWant to help yourself?http://msdn.microsoft.com/en-us/library/ms130214.aspxhttp://weblogs.sqlteam.com/brettk/http://brettkaiser.blogspot.com/ |
|
|
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. |
|
|
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? |
|
|
X002548
Not Just a Number
15586 Posts |
|
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. |
|
|
X002548
Not Just a Number
15586 Posts |
|
|
|
|
|
|