Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


What would the SQL2008 equivilent be to outer union corr?


What would the SQL2008 equivilent be to outer union corr?

Author
Message
kinderdesign
kinderdesign
SSC Rookie
SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)

Group: General Forum Members
Points: 25 Visits: 66
I'm still trying to get the hang of SQL2008.... I've been using SAS and had no problem joining these tabes before there using

SELECT * 
FROM TABLEA
OUTER UNION CORR
SELECT *
FROM TABLEB
OUTER UNION CORR
SELECT *
FROM TABLEC



Yes, I know that most will say you don't want to use * as a select because it's not efficient, but this is a server that doesn't care about efficiency...
We have 14 tables that have varying columns. All have a handful the same (about 20) whereas many are different. They are different because most reports need to treat them differently... We have 2 reports that combine all of their commone fields together and we try to keep that in a view... however, when we go to update in SQL2008 it's a bear to find what is still the same from IT and what has changed. We are not the DBA, I only have query access of the db. The DBA says this can't be done, but I'm hoping someone on here can show otherwise!

Does anyone know how to make this other than

SELECT 'ID' AS SOURCE, X.1,X.2,X.3,X.4,X.5,X.6,X.7,X.8,X.9,X.10,X.11,X.12,X.13,X.14,X.15 
FROM TABLEA AS X
UNION ALL
SELECT 'ID' AS SOURCE, X.1,X.2,X.3,X.4,X.5,X.6,X.7,X.8,X.9,X.10,X.11,X.12,X.13,X.14,X.15
FROM TABLEB AS X
OUTER UNION CORR
SELECT 'ID' AS SOURCE, X.1,X.2,X.3,X.4,X.5,X.6,X.7,X.8,X.9,X.10,X.11,X.12,X.13,X.14,X.15
FROM TABLEC AS X



When 13 changes on all of them to 39 (Don't ask why, they can't give me an answer) or they add 68, 69, 70 to all of them, it would be nice not to have to rewrite everything other than the report definintion.

Currently this is done in a view and the report using reporting services just has 'SELECT * FROM GROUPLIST'.... When they add a field we update the view then update the reports that need the new field or chnged field...
Gianluca Sartori
Gianluca Sartori
SSCertifiable
SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)

Group: General Forum Members
Points: 6964 Visits: 13323
The only way I can think of is a stored procedure with dynamic sql:


-- SETUP
CREATE TABLE TableA (
id int PRIMARY KEY CLUSTERED,
[1] int,
[2] int,
[3] int,
[4] int,
[5] int,
[6] int,
[7] int,
[8] int,
[9] int,
[10] int
)



CREATE TABLE TableB (
id int PRIMARY KEY CLUSTERED,
[1] int,
[3] int,
[4] int,
[5] int,
[8] int,
[9] int,
[10] int,
[11] int,
[12] int
)


CREATE TABLE TableC (
id int PRIMARY KEY CLUSTERED,
[1] int,
[2] int,
[3] int,
[5] int,
[6] int,
[7] int,
[9] int,
[11] int,
[12] int,
[13] int
)




ALTER PROCEDURE unionTables
AS
BEGIN

SET NOCOUNT ON;

DECLARE @sql nvarchar(max)

SELECT @sql = STUFF((
SELECT ',' + QUOTENAME(name) AS [text()]
FROM sys.columns
WHERE object_id IN (
OBJECT_ID('TableA'),
OBJECT_ID('TableB'),
OBJECT_ID('TableC')
)
GROUP BY name
HAVING COUNT(*) = 3
FOR XML PATH('')
),1,1,SPACE(0))

SELECT @sql = STUFF((
SELECT 'UNION ALL ' + char(10) + 'SELECT ' + @sql + ' FROM ' + name + CHAR(10) AS [text()]
FROM sys.tables
WHERE object_id IN (
OBJECT_ID('TableA'),
OBJECT_ID('TableB'),
OBJECT_ID('TableC')
)
FOR XML PATH('')
),1,10,SPACE(0))

EXEC(@sql)
END



--Gianluca Sartori

How to post T-SQL questions
spaghettidba.com
@spaghettidba
kinderdesign
kinderdesign
SSC Rookie
SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)

Group: General Forum Members
Points: 25 Visits: 66
Thanks Gianluca, but it seems as though this would be pretty complex. I also forgot to mention that we have 5 sets of theses tables, so it appears at the current time we'll have to keep doing it manually in views until SQL catches up with what we've been doing in SAS for years...

SELECT     'Beach' AS Module, UNID, ParentUNID, SourceDatabaseName, Name, FacilityType, BillingType, EHO, InspectionDate, Type, FollowupInspectionRequired, NextInspection, Comments, 
ReviewedBy, NumCritical, NumNonCritical, NumRepeat, '' AS TotalViolations, TotHazardRating, '' AS HazRatingType, '' AS EventName
FROM BeachInspection
UNION ALL
SELECT 'BodyArt' AS Module, UNID, ParentUNID, SourceDatabaseName, Name, FacilityType, BillingType, EHO, InspectionDate, Type, FollowupInspectionRequired, NextInspection, Comments,
ReviewedBy, NumCritical, NumNonCritical, NumRepeat, '' AS TotalViolations, TotHazardRating, '' AS HazRatingType, '' AS EventName
FROM BodyArtInspection
.......
UNION ALL
SELECT 'Water' AS Module, UNID, ParentUNID, SourceDatabaseName, Name, FacilityType, BillingType, EHO, InspectionDate, Type, FollowupInspectionRequired, NextInspection, Comments,
ReviewedBy, NumCritical, NumNonCritical, NumRepeat, '' AS TotalViolations, TotHazardRating, '' AS HazRatingType, '' AS EventName
FROM WaterInspection



SELECT     'Beach' AS Module, UNID, ParentUNID, ViolationDisplay, ViolationSectionDescription, ViolationSubModule, ViolationSection, ViolCode, ViolCorrectedSet, ViolCorrectiveActions, IsCritical, ViolDescription, ViolObservations, ViolRepeat, ViolSection
FROM BeachViolation
UNION ALL
......
SELECT 'Summer Camp' AS Module, UNID, ParentUNID, ViolationDisplay, ViolationSectionDescription, ViolationSubModule, ViolationSection, ViolCode, ViolCorrectedSet, ViolCorrectiveActions, IsCritical, ViolDescription, ViolObservations, ViolRepeat, ViolSection
FROM SummerCampViolation
UNION ALL
SELECT 'Water' AS Module, UNID, ParentUNID, ViolationDisplay, ViolationSectionDescription, ViolationSubModule, ViolationSection, ViolCode, ViolCorrectedSet, ViolCorrectiveActions, IsCritical, ViolDescription, ViolObservations, ViolRepeat, ViolSection
FROM WaterViolation



SELECT     'BeachFacility' AS Module, ID, UNID, SourceDatabaseID, SourceDatabaseName, ParentUNID, LastModified, Name, EHO, Stage, OpenedDate, ClosedDate, FacilityID, 
WebHide, TimeOpen, TimeClosed, OperationID, Source, DateCreated, DocumentID, CensusTract, Lot, Section, LegalDescription, GPIN, Block, Subdivision, PropertyID,
Branch, MagDistrict, ProblemFacility, Type, ApplicationDate, Status, PermitDate, InvoiceTo, TerminationDate, IssueDate, ExpirationDate,
PermitNumber, AllYearRound, MonthsOpen, LocalPermit, Violations, LastInspection, LastInspectionTime, TotalViolations, Enforcement, ReleaseDate, NextInspDue,
NextInspection, Freq, LastRoutineInsp, RiskRanking, NextStateInspDue, OwnerSource, OwnerID, OwnersName, ContactLast, ContactFirst, PhysicalBuilding,
PhysicalDirection, PhysicalStreetName, PhysicalStreetType, PhysicalStreetSuffix, PhysicalSuite, PhysicalPostalCode, PhysicalPostalCode AS PhysicalZip, PhysicalCity, PhysicalMunicipality, PhysicalMunicipality AS PhysicalCounty,
PhysicalProvince, PhysicalCountry, Directions, PhysicalAddress, MailingContactLast, MailingContactFirst, MailingBuilding, MailingDirection, MailingStreetName,
MailingStreetType, MailingStreetSuffix, MailingSuite, MailingPostalCode, MailingPostalCode AS MailingZip, MailingCity, MailingMunicipality, MailingMunicipality AS MailingCounty, MailingProvince, MailingCountry, MailingAddress,
PhoneDayArea, PhoneDayNumber, PhoneDayExt, PhoneEveningArea, PhoneEveningNumber, PhoneEveningExt, PhoneCellArea, PhoneCellNumber, PhoneFaxArea,
PhoneFaxNumber, Email, Website, PhoneOther, Comments, FacilityUNID, BillingType, BillingPrimaryID, BillingSecondaryID, MunicipalityCode, DocumentID3,
DateModifiedInThisFile
FROM BeachFacility
UNION ALL
.....
SELECT 'WaterFacility' AS Module, ID, UNID, SourceDatabaseID, SourceDatabaseName, ParentUNID, LastModified, Name, EHO, Stage, OpenedDate, ClosedDate, FacilityID,
WebHide, TimeOpen, TimeClosed, OperationID, Source, DateCreated, DocumentID, CensusTract, Lot, Section, LegalDescription, GPIN, Block, Subdivision, PropertyID,
Branch, MagDistrict, ProblemFacility, Type, ApplicationDate, Status, PermitDate, InvoiceTo, TerminationDate, IssueDate, ExpirationDate,
PermitNumber, AllYearRound, MonthsOpen, LocalPermit, Violations, LastInspection, LastInspectionTime, TotalViolations, Enforcement, ReleaseDate, NextInspDue,
NextInspection, Freq, LastRoutineInsp, RiskRanking, NextStateInspDue, OwnerSource, OwnerID, OwnersName, ContactLast, ContactFirst, PhysicalBuilding,
PhysicalDirection, PhysicalStreetName, PhysicalStreetType, PhysicalStreetSuffix, PhysicalSuite, PhysicalPostalCode, PhysicalPostalCode AS PhysicalZip, PhysicalCity, PhysicalMunicipality, PhysicalMunicipality AS PhysicalCounty,
PhysicalProvince, PhysicalCountry, Directions, PhysicalAddress, MailingContactLast, MailingContactFirst, MailingBuilding, MailingDirection, MailingStreetName,
MailingStreetType, MailingStreetSuffix, MailingSuite, MailingPostalCode, MailingPostalCode AS MailingZip, MailingCity, MailingMunicipality, MailingMunicipality AS MailingCounty, MailingProvince, MailingCountry, MailingAddress,
PhoneDayArea, PhoneDayNumber, PhoneDayExt, PhoneEveningArea, PhoneEveningNumber, PhoneEveningExt, PhoneCellArea, PhoneCellNumber, PhoneFaxArea,
PhoneFaxNumber, Email, Website, PhoneOther, Comments, FacilityUNID, BillingType, BillingPrimaryID, BillingSecondaryID, MunicipalityCode, DocumentID3,
DateModifiedInThisFile
FROM WaterFacility


....And it continues....
Gianluca Sartori
Gianluca Sartori
SSCertifiable
SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)

Group: General Forum Members
Points: 6964 Visits: 13323
kinderdesign (3/15/2012)
Thanks Gianluca, but it seems as though this would be pretty complex.


Not complex at all.
5 tables? No problem: just add them inside the IN predicates:


... object_id IN (
OBJECT_ID('TableA'),
OBJECT_ID('TableB'),
OBJECT_ID('TableC'),
OBJECT_ID('TableD'),
OBJECT_ID('TableE')
)



Obviously, the choice is yours.

--Gianluca Sartori

How to post T-SQL questions
spaghettidba.com
@spaghettidba
kinderdesign
kinderdesign
SSC Rookie
SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)

Group: General Forum Members
Points: 25 Visits: 66
How would this work for combine multiple different tables though? The way I'm reading it, each set would need it's own stored procedure, right? For instance, the 3 examples above are of the full sets that need to be combined. Each combination would be a separate join in SAS currently, as the groups themselves have nothing in common.

I'm also a bit confused, as it looks like it's modifying the union function: Is that only when that function is running or is that a permanant thing?
kinderdesign
kinderdesign
SSC Rookie
SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)

Group: General Forum Members
Points: 25 Visits: 66
Here's the union on how these particular three are related:

SELECT     FacilitiesList.Name
FROM FacilitiesList INNER JOIN
InspectionsList ON InspectionsList.ParentUNID = FacilitiesList.UNID RIGHT OUTER JOIN
ViolationList ON InspectionsList.UNID = ViolationList.UNID


Gianluca Sartori
Gianluca Sartori
SSCertifiable
SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)

Group: General Forum Members
Points: 6964 Visits: 13323
The procedure builds a dynamic SQL statement that UNIONs all the tables selecting all the columns that are present in all the tables.
You can see the statement being run adding "PRINT @sql" right before "EXEC(@sql)".
The statement runs in the moment you call it and does not affect the tables.
If you prefer having a view, you could modify the procedure to update the view definition and invoke it when the tables change.


CREATE PROCEDURE refresh_view_for_unioned_tables
AS
BEGIN

SET NOCOUNT ON;

DECLARE @sql nvarchar(max);

DECLARE @tables TABLE (
table_id int PRIMARY KEY CLUSTERED
);

INSERT INTO @tables
VALUES (OBJECT_ID('TableA')),
(OBJECT_ID('TableB')),
(OBJECT_ID('TableC')),
(OBJECT_ID('TableD')),
(OBJECT_ID('TableE'));

SELECT @sql = STUFF((
SELECT ',' + QUOTENAME(name) AS [text()]
FROM sys.columns
WHERE object_id IN (
SELECT table_id
FROM @tables
)
GROUP BY name
HAVING COUNT(*) = (SELECT COUNT(*) FROM @tables)
FOR XML PATH('')
),1,1,SPACE(0));

SELECT @sql = STUFF((
SELECT 'UNION ALL ' + char(10) + 'SELECT ' + @sql + ' FROM ' + name + CHAR(10) AS [text()]
FROM sys.tables
WHERE object_id IN (
SELECT table_id
FROM @tables
)
FOR XML PATH('')
),1,10,SPACE(0));

EXEC('IF OBJECT_ID(''unioned_tables'',''V'') IS NOT NULL DROP VIEW unioned_tables;');

SET @sql = 'CREATE VIEW unioned_tables AS ' + @sql;

EXEC(@sql);
END



--Gianluca Sartori

How to post T-SQL questions
spaghettidba.com
@spaghettidba
Gianluca Sartori
Gianluca Sartori
SSCertifiable
SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)

Group: General Forum Members
Points: 6964 Visits: 13323
kinderdesign (3/15/2012)
Here's the union on how these particular three are related:

SELECT     FacilitiesList.Name
FROM FacilitiesList INNER JOIN
InspectionsList ON InspectionsList.ParentUNID = FacilitiesList.UNID RIGHT OUTER JOIN
ViolationList ON InspectionsList.UNID = ViolationList.UNID



I'm sorry, but I don't understand what this code is supposed to do.
Can you clarify please?

--Gianluca Sartori

How to post T-SQL questions
spaghettidba.com
@spaghettidba
kinderdesign
kinderdesign
SSC Rookie
SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)

Group: General Forum Members
Points: 25 Visits: 66
I tried modifiying/adding the code to what I thought should be changed. Here's what I put in the procedure:
ALTER PROCEDURE refresh_view_for_inspection_tables
AS
BEGIN

SET NOCOUNT ON;

DECLARE @sql nvarchar(max);

DECLARE @tables TABLE (
table_id int PRIMARY KEY CLUSTERED
);

INSERT INTO @tables
VALUES (OBJECT_ID('BeachInspection')),
(OBJECT_ID('BodyArtInspection')),
(OBJECT_ID('CampInspection')),
(OBJECT_ID('ChildCareInspection')),
(OBJECT_ID('GeneralInspection')),
(OBJECT_ID('HotelInspection')),
(OBJECT_ID('LaborCampInspection')),
(OBJECT_ID('MobileHomeInspection')),
(OBJECT_ID('PoolInspection')),
(OBJECT_ID('SchoolInspection')),
(OBJECT_ID('SepticRemovalInspection')),
(OBJECT_ID('SummerCampInspection')),
(OBJECT_ID('WaterInspection'));

SELECT @sql = STUFF((
SELECT ',' + QUOTENAME(name) AS [text()]
FROM sys.columns
WHERE object_id IN (
SELECT table_id
FROM @tables
)
GROUP BY name
HAVING COUNT(*) = (SELECT COUNT(*) FROM @tables)
FOR XML PATH('')
),1,1,SPACE(0));

SELECT @sql = STUFF((
SELECT 'UNION ALL ' + char(10) + 'SELECT ' + @sql + ' FROM ' + name + CHAR(10) AS [text()]
FROM sys.tables
WHERE object_id IN (
SELECT table_id
FROM @tables
)
FOR XML PATH('')
),1,10,SPACE(0));

EXEC('IF OBJECT_ID(''InspectionListAutoTest'',''V'') IS NOT NULL DROP VIEW InspectionListAutoTest;');

SET @sql = 'CREATE VIEW InspectionListAutoTest AS ' + @sql;

EXEC(@sql);
END



But here's what resulted, with nothing added to the view:
Running [dbo].[refresh_view_for_unioned_tables].

No rows affected.
(0 row(s) returned)
@RETURN_VALUE = 0
Finished running [dbo].[refresh_view_for_unioned_tables]



Question: It looks like this dumps them all together into one big table then is sepparating them out: Am I reading that correctly? If so, that would be even more ideal: It would be great to have one big table that has all of the common fields (merged where column name is identical) and non-common fields, but I didn't think that was possible. Right now we use these views so that the reports aren't having to jump everything together in the report select, using that join that I had noted. That wasn't part of what we are trying to accomplish here, rather what we use on the reports to join these groups of similar tables together with their respective relationships.
I appreciate the help: Please let me know if that explains it better.
Gianluca Sartori
Gianluca Sartori
SSCertifiable
SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)

Group: General Forum Members
Points: 6964 Visits: 13323
The procedure does not run any query now, but it refreshes the view.
Right click the view and select "Script View AS... --> Create to... --> New Query Editor Window".
Has the view definition changed?

--Gianluca Sartori

How to post T-SQL questions
spaghettidba.com
@spaghettidba
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search