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]??
In a SQL 2000 SP 1 installation I've a problem with a view.
When I run a select from this view with a where clause the view return me a result set, but if I insert a order by clause the result set is empty. I try to re-organize indexes and update statistics without results.
The server is on a client site so I can't update the SQL 2000 installation immediatly, I try to ask about the installation of the SP 3.
But in the meanwhile I need some information. Anyone see this problem before.
How can I write a query to return the results for a given survey for all members (including members who have not given responses) given the surveyID.
In the [SurveyQuestionMemberReponse] table I record survey results for any members who have answered the survey. However, if a member has not responsed to the survey they will not have a record in this table.
I want to return a list of members with their response to each question in the survey. If a member has not given a response I would like to indicate they have not responded to the survey and they should still appear in the list.
When I attempt to write a query to UNION the results of a query aimed at gathering all of the results in the [SurveyQuestionMemberReponse] to all of the people in the [Members] table I recieve an error when I include the questionText field in my result set.
The error indicates:
The text data type cannot be selected as DISTINCT because it is not comparable.
Can someone please point me in the right direction. I suspect I am going about this all wrong.
[NOTE] The 'surveyType' in the [Surveys] table indicates which subset of members a given Survey should be available to. For this example let's just assume that every survey should belong to all members.
I'm trying to summarize costs assigned to active jobs for a manufacturing business. I need to aggregate work in process (WIP) cost that resides in labor-transaction and part-transaction tables based on transaction types, and transaction dates. Some transactions increase the WIP cost of the job while others decrease WIP. The business needs to see how much $$ is tied up in each job as of a particular date -- the calculation is: ToDate (cost of materials and labor assigned to job) - ToInv (cost of materials returned to inventory) - ToSales (cost of materials sold).
I developed this query incrementally and, so far, the #ToDate, #ToInv, and #ToSales temp tables seem to be populating with the correct data. My thought was to combine these three tables with a UNION and then extract the grand totals and here's where I started getting the following error: ------------------------------------------ Incorrect syntax near the keyword 'UNION'. ------------------------------------------ The problem is with the UNIONs going into #myTotal.
I would appreciate any help with this. Also, please let me know if you can suggest a better design for this. Thanks!
--M&S To Date SELECT pt.jobnum, SUM(pt.extcost) AS Cost FROM parttran pt JOIN jobhead jh ON pt.jobnum=jh.jobnum WHERE trantype IN ( <valid trans types> ) AND jh.JobReleased = 1 AND pt.TranDate < '2007-9-30' GROUP BY pt.jobnum
UNION -- This one works ok.
--L&B To Date SELECT jh.JobNum, sum(l.LaborRate*l.LaborHrs) + sum(l.BurdenRate*l.BurdenHrs) AS Cost FROM LaborDtl l JOIN JobHead jh ON l.JobNum = jh.JobNum WHERE jh.JobReleased = 1 AND l.PayrollDate < '2007-9-30' GROUP BY jh.JobNum
SELECT pt.jobnum, SUM(pt.extcost) AS ToInv FROM parttran pt JOIN jobhead jh ON pt.jobnum=jh.jobnum WHERE trantype IN (<valid trans types>) AND jh.JobReleased = 1 AND pt.TranDate < '2007-9-30' GROUP BY pt.jobnum
SELECT pt.jobnum, SUM(pt.extcost) AS ToInv FROM parttran pt JOIN jobhead jh ON pt.jobnum=jh.jobnum WHERE trantype IN (<valid trans types>) AND jh.JobReleased = 1 AND pt.TranDate < '2007-9-30' GROUP BY pt.jobnum
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
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?
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?
