How Many Unions?
Jul 18, 2000I know the answer must be somewhere, but for the moment every place I look ...
How many tables can be unioned together (I think I remember that 6.5 had a limit of 16) in SQL7?
Thanks,
Judith
ADVERTISEMENT
Multiple Unions Where There Are No Results In First Set Causes All Following Unions To Return Nothing.
Mar 21, 2008
I have two tables one with current data and a second with the same design that holds purged history.
I was going to create view and then jsut use a where clause to filter both tables but I figured it would be faster if the where clause was passed into each query as opposed to the whoel view having to load and then be filtered.
Select PatientName, MemberId
FROM CLAIMS
WHERE MemberID = @MemberID
UNION
Select PatientName, MemberId
FROM CLAIMSHistory
WHERE MemberID = @MemberID
But it appears that if there are no records in the first statement that the whole thing fails. I tried the union all operator with out any luck either.
Now if had a view like this;
Create view vAllClaims as
Select PatientName, MemberId
FROM CLAIMS
UNION
Select PatientName, MemberId
FROM CLAIMSHistory
And said select * from vAllClaims where memberid = 12345 would the query optimizer put the build the where statement onto each subquery or pull all the data first and query it?
Groupby Unions
May 6, 2004hi
I have MSSQL query that performs multiple UNION ,but I would like to perform a GROUPBY on the whole result set.
How Can i do this?
plz help...
bono
Creating Unions In TSQL
Aug 11, 2004Hi everyone. I was wondering if I could get some pointers in creating a union between two tables. This is the sproc I currently have:
CREATE Procedure spGetReturnCheckForCriteria
@SearchCriteria VARCHAR(8000),
@SortOrder VARCHAR(8000),
@PageSize INT
AS
-- Declare vars
DECLARE @SQLStatement NVARCHAR(4000)
DECLARE @bldSQLStatement VARCHAR(8000)
DECLARE @retValue INT
-- Initialize vars
SET @SQLStatement = ''
SET @bldSQLStatement = ''
SET @retValue = -1
-- Sanity Check(s)
IF (@PageSize IS NULL OR @PageSize < 1)
-- Paging Size can not be Null, nor less than One
BEGIN
SET @RetValue = -30 -- "Must have a valid Paging Size for pagination: Error (-30)
RETURN
END
-- Build the Paging SQL Statement
SET @bldSQLStatement = 'SELECT TOP '
-- Add the Page Size
SET @bldSQLStatement = @bldSQLStatement + CAST(@PageSize AS VARCHAR)
-- Add Columns/Tables/Relationships
SET @bldSQLStatement = @bldSQLStatement + '
ReturnCheck.ReturnCheckID AS ReturnCheckID,
ReturnCheck.FiscalNumber AS FiscalNumber,
ReturnCheck.ReturnedDate AS ReturnedDate,
ReturnCheck.CheckNumber AS CheckNumber,
ReturnCheck.AssessPenaltyIndicator AS AssessPenaltyIndicator,
ReturnCheck.ReturnCheckCollectionStatusCode AS ReturnCheckCollectionStatusCode,
ReturnCheck.ReturnCheckReasonCode AS ReturnCheckReasonCode,
ReturnCheck.CentralCollectionsID AS CentralCollectionsReferralNumber,
TaxPayment.PaymentID AS PaymentID,
TaxPayment.DocumentLocatorNumber AS DocumentLocatorNumber,
TaxPayment.PaymentEffectiveDate AS PaymentEffectiveDate,
TaxPayment.PaymentAmount AS PaymentAmount,
TaxPayment.PaymentQuarter AS PaymentQuarter,
TaxPayment.PaymentYear AS PaymentYear,
TaxPayment.InternalReferenceNumber AS InternalReferenceNumber,
TaxPayment.PaymentTypeCode AS PaymentTypeCode,
TaxPayment.PaymentOriginCode AS PaymentOriginCode,
TaxPayment.VoucherNumber AS VoucherNumber,
TaxPayment.ReversedIndicator AS ReversedIndicator,
TaxPayment.PaymentDate AS PaymentDate,
CAST(NULL AS DATETIME) AS CCReferralDate,
DistributionPoint.UIDPrime AS UIDPrime,
DistributionPoint.UIDCheck AS UIDCheck,
DistributionPoint.UIDDistPoint AS UIDDistPoint,
CASE
WHEN ReturnCheck.UpdatedDate IS NULL THEN ReturnCheck.CreatedDate
ELSE ReturnCheck.UpdatedDate
END AS ReturnCheckTimeStamp
FROM TaxPayment
INNER JOIN DistributionPoint
ON (TaxPayment.DistributionPointID = DistributionPoint.DistributionPointID)
INNER JOIN ReturnCheck
ON (TaxPayment.PaymentID = ReturnCheck.PaymentID)
'
-- Add Search Criteria
If (@SearchCriteria IS NOT NULL)
SET @bldSQLStatement = @bldSQLStatement + ' WHERE ' + @SearchCriteria
-- Add Sort Order
IF (@SortOrder IS NOT NULL)
SET @bldSQLStatement = @bldSQLStatement + ' ' + @SortOrder
-- Set the SQLStatement
SET @SQLStatement = @bldSQLStatement
-- Execute the Paging Query
EXECUTE @retValue = sp_executeSQL @SQLStatement
GO
Look at the SQL build where I'm doing an INNER JOIN between TaxPayment and RefundCheck. Instead of this INNER JOIN, I'd like to do a union instead. If I can get any help on this I'd greatly appreciate it. Cheers.
Syntax Error With Unions?
Oct 2, 2001I doing a union of two select queries, and I keep getting
the following error:
syntax error converting the nvarchar value 'foo' to a column of data type int.
I've tried using CAST and CONVERT in the select statement, but it doesn't change the outcome.
The table it's complaining about (the one containing the value 'foo'), is of data type nVarChar, so I don't have any idea why SQL server would try to convert it to an integer.
Is this a common problem? I'd love to know what I'm doing wrong.
Syntax Error With Unions?
Oct 2, 2001I doing a union of two select queries, and I keep getting
the following error:
syntax error converting the nvarchar value 'foo' to a column of data type int.
I've tried using CAST and CONVERT in the select statement, but it doesn't change the outcome.
The table it's complaining about (the one containing the value 'foo'), is of data type nVarChar, so I don't have any idea why SQL server would try to convert it to an integer.
Is this a common problem? I'd love to know what I'm doing wrong.
Ordering In Conjunction With Unions
Oct 7, 2004Why does this not work? When I run this query in Query Analyzer, I get the error "Incorrect syntax near the keyword 'UNION'." This seems simple enough...
SELECT * FROM SalesLead WHERE Age = '50' ORDER BY FirstName
UNION ALL
SELECT * FROM SalesLead WHERE Age = '60' ORDER BY FirstName
Thanks for any advice
Aaron
Performance Issue With Unions In Sp's?
Jul 20, 2007I was having a chat with a chap over lunch today and he asked if I knew of any performance issues when doing unions in stored procedures. I couldn't think of anything but he seemed sure there was.
Is there such an issue I've missed?
Mike
Doing Unions With Uneven Column Amounts
Mar 14, 2006I'm creating a program that allows users to submit a report on equipment at regular intervals. If a piece of equipment has a problem, it is given a job entry that refers back to the report for various information.
However, there will be times when a problem is noticed, and someone wants to submit it immediately; these are made into extra jobs.
To this end, I have three tables:
Reports
Jobs
ExtraJobs
Because ExtraJobs cannot be associated with a report, they have their own table, which holds information that would otherwise be grabbed from both Reports and Jobs. While there are seperate submission forms for regular jobs and extra jobs, I would like them to appear on the same query result when a user looks at submitted jobs or reports.
What I'm currently trying to do is this:
Code:
SELECT*
FROMReports LEFT JOIN Jobs
ON Reports.reportID = Jobs.reportID
UNION ALL
SELECT ExtraJobs.*
FROM ExtraJobs
This won't work because the first half of the union has an extra column (reportID) that the second half does not. Is there any way to add in a value for that non-existant column (say, ExtraJobs.reportID = -1) to make sure that both sides have an equal number of columns?
If worst comes to worst, I could add a reportID column to ExtraJobs and have it set to -1 for everything, but I'd like to keep from adding fat, if at all possible.
Unions Two Tables Into A Temp Table
Aug 31, 2007How do I do this? I have two queries that create temp tables. I need to union them together and create one temp table. Anyone done this with success?
View 4 Replies View RelatedWay To Group / Count Multiple Unions Together?
Dec 9, 2013I have 4 archive tables and 1 active table that are created the same, but contain different data based on the date. I need to get results that have three columns: AuthorName, Month, Total. This is currently working, but through my research I can't find how to start going about dealing with the fact that each Author has some of his results from one month in one table and some in another table and how to add those together into one row. Example:
(What I'm Getting)
AuthorName Month Total
Test, Fred 3 43
Test, Fred 3 12
Test, Fred 2 56
Test, Fred 5 35
[code]....
View That Unions Two Other Views Does Not Work
May 1, 2006I created a view V1 that uses an outer join with a table and calls a sub-view VS1 (ds_proj_report_date) which uses an inner join) and does an inner join with VS1. I also created another view V2 that uses inner join but does not call the sub-view VS1.
When I run the two views as below it works fine
Select * from V1
Union
Select * from V2
I then created another view V3 of the above union as
Create view V3
As
Select * from V1
Union
Select * from V2
Now when I run select * from V3, I get the following error.
Joined tables cannot be specified in a query containing outer join operators. View or function 'ds_proj_report_date' contains joined tables
SQL Server 2012 :: Using Unions To Write Out Each Select Query As Distinct Line In Output
Aug 20, 2015Basically I'm running a number of selects, using unions to write out each select query as a distinct line in the output. Each line needs to be multiplied by -1 in order to create an offset balance (yes this is balance sheet related stuff) for each line. Each select will have a different piece of criteria.
Although I have it working, I'm thinking there's a much better or cleaner way to do it (I use the word better loosely)
Example:
SELECT 'Asset', 'House', TotalPrice * -1
FROM Accounts
WHERE AvgAmount > 0
UNION
SELECT 'Balance', 'Cover', TotalPrice
FROM Accounts
WHERE AvgAmount > 0
What gets messy here is having to write a similar set of queries where the amount is < 0 or = 0
I'm thinking something along the lines of building a table function contains all the descriptive text returning the relative values based on the AvgAmount I pass to it.
Join First Query With A Query That Contains Unions
Jun 10, 2013I have to queries I need to combine with a left join and I am having trouble figuring out the syntax. I need to join the first query with a query that contains Unions. The queries need to by joined on File_NBR which is contained in vw_SBC_Employee_Info, vw_ADPFile and SBC_Best_Scores.
Query 1
SELECT
e1.File_NBR,
e1.Division,
e1.Department,
e1.Program,
e1.UNIT,
(e6.Mngr_FName + ' ' + e6.Mngr_LName) AS President,
[Code] .....
Query 2
SELECT
'Skill Rating' as Assessment_Type,
bs.File_NBR as ID,
bs.Skill_NBR,
bs.Best_Score as Score,
bs.Assesment_RND
[Code] .....