Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

What would the SQL2008 equivilent be to outer union corr? Expand / Collapse
Author
Message
Posted Wednesday, March 14, 2012 7:51 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, July 21, 2014 4:37 AM
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...
Post #1267182
Posted Thursday, March 15, 2012 4:19 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 7:03 AM
Points: 5,019, Visits: 10,559
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

Get your two-cent-answer quickly
spaghettidba.com
@spaghettidba
Post #1267337
Posted Thursday, March 15, 2012 8:28 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, July 21, 2014 4:37 AM
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....
Post #1267529
Posted Thursday, March 15, 2012 8:46 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 7:03 AM
Points: 5,019, Visits: 10,559
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

Get your two-cent-answer quickly
spaghettidba.com
@spaghettidba
Post #1267557
Posted Thursday, March 15, 2012 9:02 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, July 21, 2014 4:37 AM
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?
Post #1267572
Posted Thursday, March 15, 2012 9:09 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, July 21, 2014 4:37 AM
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

Post #1267576
Posted Thursday, March 15, 2012 9:21 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 7:03 AM
Points: 5,019, Visits: 10,559
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

Get your two-cent-answer quickly
spaghettidba.com
@spaghettidba
Post #1267590
Posted Thursday, March 15, 2012 9:23 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 7:03 AM
Points: 5,019, Visits: 10,559
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

Get your two-cent-answer quickly
spaghettidba.com
@spaghettidba
Post #1267592
Posted Thursday, March 15, 2012 9:55 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, July 21, 2014 4:37 AM
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.
Post #1267620
Posted Thursday, March 15, 2012 10:19 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 7:03 AM
Points: 5,019, Visits: 10,559
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

Get your two-cent-answer quickly
spaghettidba.com
@spaghettidba
Post #1267639
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse