September 10, 2011 at 5:34 pm
Not sure if this is the right place, but I can't find what I'm missing. It's not working and VS2008 is only telling me there's a error near 'AND', but I can't figure it out! Any help would be appreciated!
SELECT AgricultureFacility.SourceDatabaseName, AgricultureFacility.Name, AgricultureFacility.Status, AgricultureFacility.Stage, AgricultureFacility.DocumentID, AgricultureFacility.EHO, AgricultureFacility.Type,
AgricultureFacility.BillingType, AgricultureFacility.PhysicalAddress, AgricultureFacility.PhysicalMunicipality, AgricultureFacility.PhysicalCity, AgricultureFacility.PhysicalProvince,
AgricultureFacility.PhysicalPostalCode, AgricultureFacility.ContactPhoneArea, AgricultureFacility.ContactPhone, Renewal.RenewalType,
Renewal.PremisesName_1 AS FacilityPhysName, Renewal.BillingAddress2 AS FacilityPhysaddr, Renewal.BillingCity2 AS FacilityPhysCity,
Renewal.BillingProvince2 AS FacilityPhysState, Renewal.BillingPostalCode2 AS FacilityPhysZip, Renewal.BillingContact1 AS MailingContact,
Renewal.BusinessName AS MailingName, Renewal.BillingAddress1 AS MailingAddress, Renewal.BillingCity1 AS MailingCity,
Renewal.BillingProvince1 AS MailingState, Renewal.BillingPostalCode1 AS MailingZip
FROM Renewal INNER JOIN
AgricultureFacility ON Renewal.FacilityID = AgricultureFacility.DocumentID
WHERE (AgricultureFacility.SourceDatabaseName IN (@sourcedb)) AND (Renewal.LastModified > @start) AND (Renewal.LastModified < @end) AND (AgricultureFacility.Status = @status) AND (AgricultureFacility.Stage = @stage) AND (AgricultureFacility.EHO IN (@eho)) AND (Renewal.RenewalType = @renewaltype)
UNION ALL
SELECT BodyartFacility.SourceDatabaseName, BodyartFacility.Name, BodyartFacility.Status, BodyartFacility.Stage, BodyartFacility.DocumentID, BodyartFacility.EHO, BodyartFacility.Type,
BodyartFacility.BillingType, BodyartFacility.PhysicalAddress, BodyartFacility.PhysicalMunicipality, BodyartFacility.PhysicalCity, BodyartFacility.PhysicalProvince,
BodyartFacility.PhysicalPostalCode, BodyartFacility.ContactPhoneArea, BodyartFacility.ContactPhone, Renewal.RenewalType,
Renewal.PremisesName_1 AS FacilityPhysName, Renewal.BillingAddress2 AS FacilityPhysaddr, Renewal.BillingCity2 AS FacilityPhysCity,
Renewal.BillingProvince2 AS FacilityPhysState, Renewal.BillingPostalCode2 AS FacilityPhysZip, Renewal.BillingContact1 AS MailingContact,
Renewal.BusinessName AS MailingName, Renewal.BillingAddress1 AS MailingAddress, Renewal.BillingCity1 AS MailingCity,
Renewal.BillingProvince1 AS MailingState, Renewal.BillingPostalCode1 AS MailingZip
FROM Renewal INNER JOIN
BodyartFacility ON Renewal.FacilityID = BodyartFacility.DocumentID
WHERE (BodyartFacility.SourceDatabaseName IN (@sourcedb)) AND (Renewal.LastModified > @start) AND (Renewal.LastModified < @end) (BodyartFacility.Status = @status) AND (BodyartFacility.Stage = @stage) AND (BodyartFacility.EHO IN (@eho)) AND (Renewal.RenewalType = @renewaltype)
UNION ALL
SELECT CampgroundFacility.SourceDatabaseName, CampgroundFacility.Name, CampgroundFacility.Status, CampgroundFacility.Stage, CampgroundFacility.DocumentID, CampgroundFacility.EHO, CampgroundFacility.Type,
CampgroundFacility.BillingType, CampgroundFacility.PhysicalAddress, CampgroundFacility.PhysicalMunicipality, CampgroundFacility.PhysicalCity, CampgroundFacility.PhysicalProvince,
CampgroundFacility.PhysicalPostalCode, CampgroundFacility.ContactPhoneArea, CampgroundFacility.ContactPhone, Renewal.RenewalType,
Renewal.PremisesName_1 AS FacilityPhysName, Renewal.BillingAddress2 AS FacilityPhysaddr, Renewal.BillingCity2 AS FacilityPhysCity,
Renewal.BillingProvince2 AS FacilityPhysState, Renewal.BillingPostalCode2 AS FacilityPhysZip, Renewal.BillingContact1 AS MailingContact,
Renewal.BusinessName AS MailingName, Renewal.BillingAddress1 AS MailingAddress, Renewal.BillingCity1 AS MailingCity,
Renewal.BillingProvince1 AS MailingState, Renewal.BillingPostalCode1 AS MailingZip
FROM Renewal INNER JOIN
CampgroundFacility ON Renewal.FacilityID = CampgroundFacility.DocumentID
WHERE (CampgroundFacility.SourceDatabaseName IN (@sourcedb)) AND (Renewal.LastModified > @start) AND (Renewal.LastModified < @end) AND (CampgroundFacility.Status = @status) AND (CampgroundFacility.Stage = @stage) AND (CampgroundFacility.EHO IN (@eho)) AND (Renewal.RenewalType = @renewaltype)
UNION ALL
SELECT FoodFacility.SourceDatabaseName, FoodFacility.Name, FoodFacility.Status, FoodFacility.Stage, FoodFacility.DocumentID, FoodFacility.EHO, FoodFacility.Type,
FoodFacility.BillingType, FoodFacility.PhysicalAddress, FoodFacility.PhysicalMunicipality, FoodFacility.PhysicalCity, FoodFacility.PhysicalProvince,
FoodFacility.PhysicalPostalCode, FoodFacility.ContactPhoneArea, FoodFacility.ContactPhone, Renewal.RenewalType,
Renewal.PremisesName_1 AS FacilityPhysName, Renewal.BillingAddress2 AS FacilityPhysaddr, Renewal.BillingCity2 AS FacilityPhysCity,
Renewal.BillingProvince2 AS FacilityPhysState, Renewal.BillingPostalCode2 AS FacilityPhysZip, Renewal.BillingContact1 AS MailingContact,
Renewal.BusinessName AS MailingName, Renewal.BillingAddress1 AS MailingAddress, Renewal.BillingCity1 AS MailingCity,
Renewal.BillingProvince1 AS MailingState, Renewal.BillingPostalCode1 AS MailingZip
FROM Renewal INNER JOIN
FoodFacility ON Renewal.FacilityID = FoodFacility.DocumentID
WHERE (FoodFacility.SourceDatabaseName IN (@sourcedb)) AND (Renewal.LastModified > @start) AND (Renewal.LastModified < @end) AND (FoodFacility.Status = @status) AND (FoodFacility.Stage = @stage) AND (FoodFacility.EHO IN (@eho)) AND (Renewal.RenewalType = @renewaltype)
UNION ALL
SELECT HotelFacility.SourceDatabaseName, HotelFacility.Name, HotelFacility.Status, HotelFacility.Stage, HotelFacility.DocumentID, HotelFacility.EHO, HotelFacility.Type,
HotelFacility.BillingType, HotelFacility.PhysicalAddress, HotelFacility.PhysicalMunicipality, HotelFacility.PhysicalCity, HotelFacility.PhysicalProvince,
HotelFacility.PhysicalPostalCode, HotelFacility.ContactPhoneArea, HotelFacility.ContactPhone, Renewal.RenewalType,
Renewal.PremisesName_1 AS FacilityPhysName, Renewal.BillingAddress2 AS FacilityPhysaddr, Renewal.BillingCity2 AS FacilityPhysCity,
Renewal.BillingProvince2 AS FacilityPhysState, Renewal.BillingPostalCode2 AS FacilityPhysZip, Renewal.BillingContact1 AS MailingContact,
Renewal.BusinessName AS MailingName, Renewal.BillingAddress1 AS MailingAddress, Renewal.BillingCity1 AS MailingCity,
Renewal.BillingProvince1 AS MailingState, Renewal.BillingPostalCode1 AS MailingZip
FROM Renewal INNER JOIN
HotelFacility ON Renewal.FacilityID = HotelFacility.DocumentID
WHERE (HotelFacility.SourceDatabaseName IN (@sourcedb)) AND (Renewal.LastModified > @start) AND (Renewal.LastModified < @end) AND (HotelFacility.Status = @status) AND (HotelFacility.Stage = @stage) AND (HotelFacility.EHO IN (@eho)) AND (Renewal.RenewalType = @renewaltype)
UNION ALL
SELECT PoolFacility.SourceDatabaseName, PoolFacility.Name, PoolFacility.Status, PoolFacility.Stage, PoolFacility.DocumentID, PoolFacility.EHO, PoolFacility.Type,
PoolFacility.BillingType, PoolFacility.PhysicalAddress, PoolFacility.PhysicalMunicipality, PoolFacility.PhysicalCity, PoolFacility.PhysicalProvince,
PoolFacility.PhysicalPostalCode, PoolFacility.ContactPhoneArea, PoolFacility.ContactPhone, Renewal.RenewalType,
Renewal.PremisesName_1 AS FacilityPhysName, Renewal.BillingAddress2 AS FacilityPhysaddr, Renewal.BillingCity2 AS FacilityPhysCity,
Renewal.BillingProvince2 AS FacilityPhysState, Renewal.BillingPostalCode2 AS FacilityPhysZip, Renewal.BillingContact1 AS MailingContact,
Renewal.BusinessName AS MailingName, Renewal.BillingAddress1 AS MailingAddress, Renewal.BillingCity1 AS MailingCity,
Renewal.BillingProvince1 AS MailingState, Renewal.BillingPostalCode1 AS MailingZip
FROM Renewal INNER JOIN
PoolFacility ON Renewal.FacilityID = PoolFacility.DocumentID
WHERE (PoolFacility.SourceDatabaseName IN (@sourcedb)) AND (Renewal.LastModified > @start) AND (Renewal.LastModified < @end) AND (PoolFacility.Status = @status) AND (PoolFacility.Stage = @stage) AND (PoolFacility.EHO IN (@eho)) AND (Renewal.RenewalType = @renewaltype)
UNION ALL
SELECT RadiationFacility.SourceDatabaseName, RadiationFacility.Name, RadiationFacility.Status, RadiationFacility.Stage, RadiationFacility.DocumentID, RadiationFacility.EHO, RadiationFacility.Type,
RadiationFacility.BillingType, RadiationFacility.PhysicalAddress, RadiationFacility.PhysicalMunicipality, RadiationFacility.PhysicalCity, RadiationFacility.PhysicalProvince,
RadiationFacility.PhysicalPostalCode, RadiationFacility.ContactPhoneArea, RadiationFacility.ContactPhone, Renewal.RenewalType,
Renewal.PremisesName_1 AS FacilityPhysName, Renewal.BillingAddress2 AS FacilityPhysaddr, Renewal.BillingCity2 AS FacilityPhysCity,
Renewal.BillingProvince2 AS FacilityPhysState, Renewal.BillingPostalCode2 AS FacilityPhysZip, Renewal.BillingContact1 AS MailingContact,
Renewal.BusinessName AS MailingName, Renewal.BillingAddress1 AS MailingAddress, Renewal.BillingCity1 AS MailingCity,
Renewal.BillingProvince1 AS MailingState, Renewal.BillingPostalCode1 AS MailingZip
FROM Renewal INNER JOIN
RadiationFacility ON Renewal.FacilityID = RadiationFacility.DocumentID
WHERE (RadiationFacility.SourceDatabaseName IN (@sourcedb)) AND (Renewal.LastModified > @start) AND (Renewal.LastModified < @end) AND (RadiationFacility.Status = @status) AND (RadiationFacility.Stage = @stage) AND (RadiationFacility.EHO IN (@eho)) AND (Renewal.RenewalType = @renewaltype)
UNION ALL
SELECT SummerCampFacility.SourceDatabaseName, SummerCampFacility.Name, SummerCampFacility.Status, SummerCampFacility.Stage, SummerCampFacility.DocumentID, SummerCampFacility.EHO, SummerCampFacility.Type,
SummerCampFacility.BillingType, SummerCampFacility.PhysicalAddress, SummerCampFacility.PhysicalMunicipality, SummerCampFacility.PhysicalCity, SummerCampFacility.PhysicalProvince,
SummerCampFacility.PhysicalPostalCode, SummerCampFacility.ContactPhoneArea, SummerCampFacility.ContactPhone, Renewal.RenewalType,
Renewal.PremisesName_1 AS FacilityPhysName, Renewal.BillingAddress2 AS FacilityPhysaddr, Renewal.BillingCity2 AS FacilityPhysCity,
Renewal.BillingProvince2 AS FacilityPhysState, Renewal.BillingPostalCode2 AS FacilityPhysZip, Renewal.BillingContact1 AS MailingContact,
Renewal.BusinessName AS MailingName, Renewal.BillingAddress1 AS MailingAddress, Renewal.BillingCity1 AS MailingCity,
Renewal.BillingProvince1 AS MailingState, Renewal.BillingPostalCode1 AS MailingZip
FROM Renewal INNER JOIN
SummerCampFacility ON Renewal.FacilityID = SummerCampFacility.DocumentID
WHERE (SummerCampFacility.SourceDatabaseName IN (@sourcedb)) AND (Renewal.LastModified > @start) AND (Renewal.LastModified < @end) AND (SummerCampFacility.Status = @status) AND (SummerCampFacility.Stage = @stage) AND (SummerCampFacility.EHO IN (@eho)) AND (Renewal.RenewalType = @renewaltype)
September 10, 2011 at 10:25 pm
Thanks a million! I have been looking over this since yesterday afternoon! I guess I just needed a fresh set of eyes! Thanks again! That was it: Working fine now!
September 11, 2011 at 4:51 am
Please don't take offense but it would be easier on the eyes, shorten the length of your statement and make it easier to identify an issues by using Table Aliases.
SELECT AF.SourceDatabase...
FROM Renewal AS REN INNER JOIN
AgricultureFacility AS AF ON REN.FacilityID = AF.DocumentID
Just a personal preference. 😉
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
September 11, 2011 at 12:33 pm
Hi
Since all your facility tables have the same structure, I would try to centralize your query logic. If you need this UNION ALL only in one script/procedure, I'd use a CTE to concatenate all data, if you need to do this on more than one position I would move this into a VIEW and work with the view instead of the tables.
Here's your statement when working with a CTE:
; WITH Facilities AS (
SELECT SourceDatabaseName, Name, Status, Stage, DocumentID, EHO, Type,
BillingType, PhysicalAddress, PhysicalMunicipality, PhysicalCity, PhysicalProvince,
PhysicalPostalCode, ContactPhoneArea, ContactPhone
FROM AgricultureFacility
UNION ALL
SELECT SourceDatabaseName, Name, Status, Stage, DocumentID, EHO, Type,
BillingType, PhysicalAddress, PhysicalMunicipality, PhysicalCity, PhysicalProvince,
PhysicalPostalCode, ContactPhoneArea, ContactPhone
FROM BodyartFacility
UNION ALL
SELECT SourceDatabaseName, Name, Status, Stage, DocumentID, EHO, Type,
BillingType, PhysicalAddress, PhysicalMunicipality, PhysicalCity, PhysicalProvince,
PhysicalPostalCode, ContactPhoneArea, ContactPhone
FROM CampgroundFacility
UNION ALL
SELECT SourceDatabaseName, Name, Status, Stage, DocumentID, EHO, Type,
BillingType, PhysicalAddress, PhysicalMunicipality, PhysicalCity, PhysicalProvince,
PhysicalPostalCode, ContactPhoneArea, ContactPhone
FROM FoodFacility
UNION ALL
SELECT SourceDatabaseName, Name, Status, Stage, DocumentID, EHO, Type,
BillingType, PhysicalAddress, PhysicalMunicipality, PhysicalCity, PhysicalProvince,
PhysicalPostalCode, ContactPhoneArea, ContactPhone
FROM HotelFacility
UNION ALL
SELECT SourceDatabaseName, Name, Status, Stage, DocumentID, EHO, Type,
BillingType, PhysicalAddress, PhysicalMunicipality, PhysicalCity, PhysicalProvince,
PhysicalPostalCode, ContactPhoneArea, ContactPhone
FROM PoolFacility
UNION ALL
SELECT SourceDatabaseName, Name, Status, Stage, DocumentID, EHO, Type,
BillingType, PhysicalAddress, PhysicalMunicipality, PhysicalCity, PhysicalProvince,
PhysicalPostalCode, ContactPhoneArea, ContactPhone
FROM RadiationFacility
UNION ALL
SELECT SourceDatabaseName, Name, Status, Stage, DocumentID, EHO, Type,
BillingType, PhysicalAddress, PhysicalMunicipality, PhysicalCity, PhysicalProvince,
PhysicalPostalCode, ContactPhoneArea, ContactPhone
FROM SummerCampFacility
)
SELECT F.SourceDatabaseName, F.Name, F.Status, F.Stage, F.DocumentID, F.EHO, F.Type,
F.BillingType, F.PhysicalAddress, F.PhysicalMunicipality, F.PhysicalCity, F.PhysicalProvince,
F.PhysicalPostalCode, F.ContactPhoneArea, F.ContactPhone, REN.RENType,
REN.PremisesName_1 AS FacilityPhysName, REN.BillingAddress2 AS FacilityPhysaddr, REN.BillingCity2 AS FacilityPhysCity,
REN.BillingProvince2 AS FacilityPhysState, REN.BillingPostalCode2 AS FacilityPhysZip, REN.BillingContact1 AS MailingContact,
REN.BusinessName AS MailingName, REN.BillingAddress1 AS MailingAddress, REN.BillingCity1 AS MailingCity,
REN.BillingProvince1 AS MailingState, REN.BillingPostalCode1 AS MailingZip
FROM Renewal AS REN
INNER JOIN Facilities AS F ON REN.FacilityID = F.DocumentID
WHERE (F.SourceDatabaseName = @sourcedb) AND (REN.LastModified > @start) AND (REN.LastModified < @end) AND (F.Status = @status) AND (F.Stage = @stage) AND (F.EHO = @eho) AND (REN.RENType = @RENtype)
Makes the maintenance much easier.
I also changed the IN for SourceDatabaseName and EHO to a "=" operator. SQL will do the same, but this shows more what it does in my opinion. 🙂
For sure, check the execution plans/time before changing. This could cause a better or worse execution 😉
Greets
Flo
September 11, 2011 at 6:20 pm
Welsh Corgi (9/11/2011)
Please don't take offense but it would be easier on the eyes, shorten the length of your statement and make it easier to identify an issues by using Table Aliases.SELECT AF.SourceDatabase...
FROM Renewal AS REN INNER JOIN
AgricultureFacility AS AF ON REN.FacilityID = AF.DocumentID
Just a personal preference. 😉
I will have to look in to how to do that. So far I have never done that. Any sites to get me started on the idea? I would certainly like to make these shorter! It would be nice to not have to copy the same thing multiple times, changing only the table name and unioning them together. Are you saying just do the same thing except only using the full name once? If so, I don't see that as being much shorter... Would it work to rename, in this case, all of the Facilities as "FRom ... AgricultureFacility as AF .... then FoodFacility as AF? I think it may get confusing, but looks like a good idea to make it simpler to copy/paste! Would that be 'acceptable' or might this cause problems when compling large reports? Seems to me that it might throw off the SQL, but as I've said: I never did this before. Thanks!
September 11, 2011 at 6:33 pm
Florian Reischl (9/11/2011)
HiSince all your facility tables have the same structure, I would try to centralize your query logic. If you need this UNION ALL only in one script/procedure, I'd use a CTE to concatenate all data, if you need to do this on more than one position I would move this into a VIEW and work with the view instead of the tables.
Here's your statement when working with a CTE:
<CODE>
Makes the maintenance much easier.
I also changed the IN for SourceDatabaseName and EHO to a "=" operator. SQL will do the same, but this shows more what it does in my opinion. 🙂
For sure, check the execution plans/time before changing. This could cause a better or worse execution 😉
Greets
Flo
Flo,
The tables aren't identical, there about about 20 columns that are the same on all, but the rest are different. We actually use a different program to collect the data and then export it to SQL for reporting.
As for changing the IN to =: I had to change it to 'IN' because 'EHO' and the others I switched to IN threw errors because they are multi-value fields and selections. Currently I'm setting up a dataset for each to union all of the distinct values per table.column.
I'm not sure I understand what you mean by using 'CTE'... I did a google search and it didn't really help me a lot. I will dig more into seeing the benefits of this, as It looks to me like it's more complicated that simple: Is it that it uses less system resources? I have also never worked with views, only tables.
*edit*: After working with this a bit, I see how much easier it is! Thanks again! Until I was actually playing with it and moving it into my next report, I didn't see how much simpler it is to combine multiple tables this way!
September 11, 2011 at 9:45 pm
http://msdn.microsoft.com/en-us/library/ms187455(v=sql.90).aspx
http://www.geekinterview.com/question_details/68355
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
September 12, 2011 at 5:05 am
Hi
A VIEW doesn't need to return all columns of your base tables. If you create a view that returns those 20 columns which are equal you will be able to simplify all places where you have to create reports that need data out of all your facility tables.
For the CTE, search BOL (Books Online, the SQL Server Help) for "Common Table Expressions". It's like an inline-view.
For the IN, as long as you provide your query arguments in a variable, you will not get the results, even if your variable contains a value like "123, 456, 789". If you want to be able to provide more than one argument dynamically, you will need to work with dynamic SQL (the hard way) or work with a temp/variable table and an EXISTS statement.
Greets
Flo
September 23, 2011 at 4:09 pm
Thanks for all of the information everyone. Unfortunately it took me a bit to get back to the project since I have a million other things going on.
I have been using CTE now, easier on the eyes for sure!
Flo: I see what you mean about views: Had I started using that a few months ago I could have saved a LOT of work combining them on all of the reports!
As far as the the comments re: 'IN', I have to disagree. When I use EXISTS or = I get errors. I only get results when I use (XYZ IN (@textvariable)). When I use EXISTS or =, I get errors that there is an invalid syntax. If I use IN as referenced, I get exactly what I need... Is this something that is possibly version specific? I'm using SQL Server 2008 Enterprise (I think), provided on our hosted server.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply