The query I'm working on involves a UNION of four or five smaller queries, each doing different things. Each query, however, uses the same base set of records. eg:
SELECT ...
FROM Products p
JOIN ... -- many other tables
WHERE p.ProductIsActive = 1
UNION
SELECT ...
FROM Products p
JOIN ... -- a different set of tables
WHERE p.ProductIsActive = 1
UNION ... -- etc etc
So each subquery is working from the same base set of Products rows.
My idea was to pull that out into a CTE. Something like this:
WITH ActiveProducts AS (
SELECT ProductID
FROM Products
WHERE ProductIsActive = 1
)
SELECT ...
FROM ActiveProducts p
JOIN ... -- many other tables
UNION
SELECT ...
FROM ActiveProducts p
JOIN ... -- a different set of tables
UNION ... -- etc etc
So my question is: If I do it this way, will the "ActiveProducts" CTE get executed for every subquery? Or will it just get executed once and used like a view by each subquery?
Or is there a better way to do this that I'm overlooking?
In v. 7 I could create union queries and save them as views, which I found very usefull for feeding data to reporting apps etc. In SQL 2000 the view builder balks at this. (The message states "View definition includes no output columns or includes no items in the from clause.") Version 7 used to complain as well but would still save the view (it's just a text SQL statement, after all). Is it still possible to save a union query as a view in SQL 2000? The view I was trying to create this morning is:
SELECT idx, slots_idx, partno, priority FROM table 1 UNION ALL SELECT idx, slots_idx, partno, priority FROM table 2
Three sql statements whose result has to be unioned
SELECT orders_recipients as Email FROM vendors where vendorID in (select Vendorid from Restaurants_VendorsAssoc where restaurantid = @resid) union SELECT str_eMail as EMail from tbl_contactmanager where (bit_shared =1) and int_restaurantId in (select restaurantid from users where userID in (select UserID2 from Users_Linked where UserID1 = @userid)) union SELECT str_eMail as EMail from tbl_contactmanager where int_restaurantId = @resid
I am getting an error message as follows The text, ntext, or image data type cannot be selected as DISTINCT. pls help
why this code wont work? Attempting to join Home Games and Away games data from two queries.
SELECTSoccer_Base.dbo.Teams.TeamName ,Sum(Games) as games ( SELECTSoccer_Base.dbo.Results.HomeTeam as Team ,COUNT(Soccer_Base.dbo.Results.HomeTeam) as [Games] ,SUM(Case WHEN Soccer_Base.dbo.Results.HomeFT> Soccer_Base.dbo.Results.AwayFT THEN 1 ELSE 0 END) AS [Win] ,SUM(Case WHEN Soccer_Base.dbo.Results.HomeFT= Soccer_Base.dbo.Results.AwayFT THEN 1 ELSE 0 END) AS [Draw]
Hi I have the following union query that retrieves two counts. Can Isum them up within this query, like wrap this in a sum functionsomehow to get the total count? Or is there a better way to do this.Please help. Using SQL 2000.select count(user_id) from table1UNIONselect count(user_id) from table2
I've run into some trouble with an apparently simple UNION query:
SELECT [Date] AS D, C7, NULL AS L72A, NULL AS P02, PAV FROM [Process Variables Level 1] UNION SELECT [Date] as D, NULL AS C7, L72A, P02, NULL AS PAV FROM [Process Variables Level 2] ORDER BY D
Records in the 2 tables may or may not have the same Date/Time values.
The product of the above UNION query appears to be using dates from the [Process Variables Level 2] table only. I'm getting values returned for field C7 with Date/Time values from table [Process Variables Level 2]??
I following two queries First query is SELECT HomeTeam, 1 AS Pld, CASE WHEN HomeScore > AwayScore THEN 1 ELSE 0 END AS Won, CASE WHEN HomeScore = AwayScore THEN 1 ELSE 0 END AS Draw, CASE WHEN HomeScore < AwayScore THEN 1 ELSE 0 END AS Lost, HomeScore AS Scored, AwayScore AS Against, HomeScore - AwayScore AS Agg, CASE WHEN HomeScore > AwayScore THEN 3 ELSE 0 END AS Pts FROM tblFixtures WHERE (CompID = 1) AND (HomeScore IS NOT NULL)
and second query is
SELECT AwayTeam, 1 AS Pld, CASE WHEN HomeScore < AwayScore THEN 1 ELSE 0 END AS Won, CASE WHEN HomeScore = AwayScore THEN 1 ELSE 0 END AS Draw, CASE WHEN HomeScore > AwayScore THEN 1 ELSE 0 END AS Lost, AwayScore AS Scored, HomeScore AS Against, AwayScore - HomeScore AS Agg, CASE WHEN HomeScore < AwayScore THEN 3 WHEN HomeScore = AwayScore THEN 1 ELSE 0 END AS Pts FROM tblFixtures WHERE (CompID = 1) AND (HomeScore IS NOT NULL) what i need is I want to create another joint result of two queries which adds up all the totals from each of the other two queries.
I have a quite big SQL query which would be nice to be used using UNION betweern two Select and Where clauses. I noticed that if both Select clauses have Where part between UNION other is ignored. How can I prevent this?
I found a article in StackOverflow saying that if UNION has e.g. two Selects with Where conditions other one will not work. [URL] ....
I have installed SQL Server 2014 and I tried to use tricks mentioned in StackOverflow's article but couldn't succeeded.
Any example how to write two Selects with own Where clauses and those Selects are joined with UNION?
I have a query which does 3 selects and Union ALLs each to get a final result set. The performance is unacceptable - takes around a minute to run. If I remove the Union All so that the result sets are returned individually it returns all 3 from the query in around 6 seconds (acceptable performance).
Any way to join the result sets together without using Union All.
Each result set has exactly the same structure returned...
Query below [for reference]...
WITH cte AS ( SELECT A.[PoleID], ISNULL(B.[IsSpanClear], 0) AS [IsSpanClear], B.[SurveyDate], ROW_NUMBER() OVER (PARTITION BY A.[PoleID] ORDER BY B.[SurveyDate] DESC) rownum FROM[UT_Pole] A LEFT OUTER JOIN [UT_Surveyed_Pole] B ON A.[PoleID] = B.[PoleID]
I have a Union All transformation with 4 inputs and one output when I debug the package the sum of the different inputs rows does not match the row count in output.
I don't understand, I've used the Union All transform many times and I've never seen this.
HelloWhen I use a PreparedStatement (in jdbc) with the following query:SELECT store_groups_idFROM store_groupsWHERE store_groups_id IS NOT NULLAND type = ?ORDER BY group_nameIt takes a significantly longer time to run (the time it takes forexecuteQuery() to return ) than if I useSELECT store_groups_idFROM store_groupsWHERE store_groups_id IS NOT NULLAND type = 'M'ORDER BY group_nameAfter tracing the problem down, it appears that this is not preciselya java issue, but rather has to do with the underlying cost of runningparameterized queries.When I open up MS Enterprise Manager and type the same query in - italso takes far longer for the parameterized query to run when I usethe version of the query with bind (?) parameters.This only happens when the table in question is large - I am seeingthis behaviour for a table with > 1,000,000 records. It doesn't makesense to me why a parameterized query would run SLOWER than acompletely ad-hoc query when it is supposed to be more efficient.Furthermore, if one were to say that the reason for this behaviour isthat the query is first getting compliled and then the parameters aregetting sent over - thus resulting in a longer percieved executiontime - I would respond that if this were the case then A) it shouldn'tbe any different if it were run against a large or small table B) thisperformance hit should only be experienced the first time that thequery is run C) the performance hit should only be 2x the time for thenon-parameterized query takes to run - the difference in response timeis more like 4-10 times the time it takes for the non parameterizedversion to run!!!Is this a sql-server specific problem or something that would pertainto other databases as well? I there something about the coorect use ofbind parameters that I overall don't understand?If I can provide some hints in Java then this would be great..otherwise, do I need to turn/off certain settings on the databaseitself?If nothing else works, I will have to either find or write a wrapperaround the Statement object that acts like a prepared statement but inreality sends regular Statement objects to the JDBC driver. I wouldthen put some inteligence in the database layer for deciding whetherto use this special -hack- object or a regular prepared statementdepending on the expected overhead. (Obviously this logic would onlybe written in once place.. etc.. IoC.. ) HOWEVER, I would desperatelywant to avoid doing this.Please help :)
I just found out that I can do an ORDER BY clause on entire records set retrieve from a query that combines several sub queries with UNION from different tables with the same structure... so this is great to know, BTW, is this a new feature of MSSQL 2K ? I don't recall being able to do this in MSSQL 7 or 6.5.
Anyway, the main question is, can I use the TOP command in a query that has UNION in it?? Meaning, there are two queries (or more) from two tables (or more) and I need to fetch the top 10 records by an ORDER BY clause from the combined results, when I try to add each sub query TOP 10 the results are not correct at all, when I try to add TOP 10 only to the first query hoping that the analyzer will refer to the whole query, it's selecting TOP 10 from the first query and combines it with all the records from the others...
So, can anyone help? I hope the problem is understood.
I am trying to join to different queries into one table ( I accomplished this)
Next I need to ADD or SUM the results of 2 rows to form a single row.
As you can see in the query below, I run 2 separate queries and use ' ' as a place holder for the UNION to work. I get duplicate rows, one with a value and the other with a '0'. I want to have a single row.
Any help is greatly appreciated!
(SELECT v_gs_supportedpackages.ProdID0 as 'Product Name', v_RA_System_SMSInstalledSites.SMS_Installed_Sites0 as 'Site', Count(ProdID0) as '# copies installed', '' as '# legitimate copies installed'
FROM v_R_System SYS, v_GS_Workstation_Status HWSCAN, v_gs_SupportedPackages inner join v_RA_System_SMSInstalledSites on v_RA_System_SMSInstalledSites.ResourceID = v_gs_SupportedPackages.ResourceID
WHERE SYS.ResourceId = HWSCAN.ResourceId AND SYS.ResourceId = v_gs_SupportedPackages.ResourceId AND v_gs_supportedpackages.ProdID0 = substring('MS Security Patch MS04-030,031,032,034,037,038',1,60)
(SELECT v_gs_supportedpackages.ProdID0 as 'Product Name', v_RA_System_SMSInstalledSites.SMS_Installed_Sites0 as 'Site', '' as '# copies installed', Count(ProdID0) as '# legitimate copies installed'
FROM v_R_System SYS, v_GS_Workstation_Status HWSCAN, v_gs_SupportedPackages inner join v_RA_System_SMSInstalledSites on v_RA_System_SMSInstalledSites.ResourceID = v_gs_SupportedPackages.ResourceID
WHERE SYS.ResourceId = HWSCAN.ResourceId AND SYS.ResourceId = v_gs_SupportedPackages.ResourceId AND v_gs_supportedpackages.ProdID0 = substring('MS Security Patch MS04-030,031,032,034,037,038',1,60) AND DateDiff(Day,HWSCAN.LastHWScan,GetDate()) <= '20'
By default, the UNION operator removes duplicate rows from the result set. If you use ALL, all rows are included in the results and duplicates are not removed.
Why is it assumed that one would want the duplicates removed by default? Isn't that what SELECT DISINCT is for?
I was wondering if there is a way I could write this query as one query using union instead of 3 different queries?
SELECT columna as column,count(ID) as Applications FROM tablea GROUP columna
SELECT columna as column2, count(ID) as Approved FROM tablesa where substring(APP_DATE,1,6) >'200304' and in 'Approved') GROUP BY columna
SELECT columna as column3, count(ID) as Booked, sum(AMT) as amt, sum(AMT)/count(ID) as lavg from tablea where substring(APP_DATE,1,6) >'200304' and STATUS in('book') group by columna
I am doing a UNION of 8 views(with 3 million rows in each view approx). I am sure that there is NO DUPLICATE data between the views. SO specifying a UNION ALL would be better than specifying a UNION in this case?
Thanks!
Prakash.P The secret to creativity is knowing how to hide your sources!
Hi All I want to do the following report and I want the 2nd select statement to appear at the bottom to display the totals
SELECT Item,B.Mar, B.Apr, B.May, B.Jun,B.Jul, B.Aug, B.Sep, B.Oct, B.Nov, B.Dec, B.Jan FROM fncWineSales(2007) AS A UNION SELECT 'Total',SUM(B.Mar), SUM(B.Apr), SUM(B.May), SUM(B.Jun),SUM(B.Jul), SUM(B.Aug), SUM(B.Sep), SUM(B.Oct), SUM(B.Nov), SUM(B.Dec), SUM(B.Jan) FROM dbo.fncWineSales (2007) AS B
Hello,Bear with me (not had much sleep last night), pls see following ddl,dml and comments for what is desired, I don't have a problem gettingdesired result(s), however, I'm wondering if there's another (better)solution than UNION operator in this case. TIA.-- DDLCREATE TABLE #TMP (col varchar(10));-- DMLinsert into #TMPvalues('A124');insert into #TMPvalues('A127');insert into #TMPvalues('A12728');insert into #TMPvalues('A17282');insert into #TMPvalues('BCD');insert into #TMPvalues('BCD');insert into #TMPvalues('CDSS');insert into #TMPvalues('DS');insert into #TMPvalues('YUUEI');-- goal: get one row with col data starting with 'A' and distict rowsfor the restselect top 1 colfrom #TMPwhere col LIKE 'A%'UNIONselect distinct colfrom #TMPwhere col NOT LIKE 'A%'
Hi,I can return results of a union easily enough, but I wish to sum the recordsfirst. Is this possible in a single SQL statement? Or do I have to useseparate ones to sum up after the union?Cheers,Chris
Of the two queries below, the first returns the desired result, the union of the select-except statments. Why doesn't the second query return the same result as the first? Is this a mistake/bug on Microsofts side?
When any changes have been made to the underlying table structure, the Union all reports error and does not automatically correct the error. I then have to delete it and recreate it. Is this a bug?
These are the errors that I am getting. The 2nd error only comes up when I try to put in "UNION". Any help would be very much appreciated. Thank you!
Msg 156, Level 15, State 1, Procedure searchUser, Line 79 Incorrect syntax near the keyword 'UNION'.
Msg 102, Level 15, State 1, Procedure searchUser, Line 100 Incorrect syntax near 'END'.
Code Snippet
set ANSI_NULLS ON set QUOTED_IDENTIFIER ON GO ALTER PROCEDURE [dbo].[searchUser] -- Add the parameters for the stored procedure here
@Username VARCHAR(25), @AppID INT, @ResID INT, @Access VARCHAR(5), @Region INT AS BEGIN
IF @Username IS NULL BEGIN
IF @Access IS NULL BEGIN PRINT 1 SELECT a.XID, a.AppID, a.ResID, b.AppDescription, c.ResDescription, d.Username, d.Custom, d.Region
FROM [Application] a INNER JOIN ApplicationName b ON a.AppID = b.AppID LEFT JOIN ResTable c ON a.ResID = c.ResID LEFT JOIN AppResCustom d ON a.XID = d.XID
FROM [Application] a INNER JOIN ApplicationName b ON a.AppID = b.AppID LEFT JOIN ResTable c ON a.ResID = c.ResID LEFT JOIN AppResCustom d ON a.XID = d.XID
WHERE a.AppID = @AppID AND a.ResID = @ResID AND d.Custom = @Access END
FROM [Application] a INNER JOIN ApplicationName b ON a.AppID = b.AppID LEFT JOIN ResTable c ON a.ResID = c.ResID LEFT JOIN AppResCustom d ON a.XID = d.XID
WHERE d.Username = @Username AND a.AppID = @AppID AND a.ResID = @ResID AND d.Custom = @Access
FROM [Application] a INNER JOIN ApplicationName b ON a.AppID = b.AppID LEFT JOIN ResTable c ON a.ResID = c.ResID LEFT JOIN AppResCustom d ON a.XID = d.XID
WHERE d.Username = @Username AND a.AppID = @AppID AND a.ResID = @ResID AND d.Custom = @Access AND d.Region = @Region
I want to know the top 5 patterns in sales, the report will like this:
Month Top1 Top2 Top3 Top4 Top5
2005-06 A1 A2 A3 A4 A5
2005-07 B1 B2 B3 B4 B5
2005-08 C1 C2 C3 C4 C5
--- --- ---
What I did is:
declare @StartDate and @EndDate, set @StartDate and @EndDate
Delete AAAA (AAAA is a table)
Begin
While (@EndDate<somedate)
Insert into AAAA(sales, Month, Pattern)
Select TOP 5 sales, Month,pattern from sometables order by sales DESC
increase @StartDate and @EndDate by a month
End
Select * from AAAA
It works fine. My question is: Can I get rid of table AAAA? Is there a better way that just use Top 5 combine with something say Union? ( I tried Union and failed )
The first comment, by 'zpeceno', exactly describes a problem that I have hit upon. However despite looking thro the list of issues resolved in SP1, I've found no mention of it. Any chance I could get some offical comment re. the bug described, when it was(/has it been?) officially acknowledged, and what service pack (if any) resolves it?
