The 10 tables (Table 1, 2,..10) are meant for holding data regarding the compliance for a particular attribute. For example, all assets have to follow a naming convention, if they don't follow, they are recorded in Table 1. The same way, if they have any unacceptable os, they are recorded in table 2; and so on. There are 10 such attributes which are monitored in those respective 10 tables.
The 11th table is a Master Table that has all the hosts which is the master inventory table.
How do I write a query to get a list of all the hosts that belong to any of those 10 tables which could be appearing in 1 or more of those 10 table(s).
All,I've seen several posts regarding using UNION or UNION ALL to mashtogether two or more resultsets into a single result set, but can'tseem to find enough info here to help me answer my particularquestion.I have a stored procedure that gets the column names in a particularformat (i.e. "chassis_id"|"chassis_description"|"modify_date") as wellas actual data for a given table (in a quote-separated, pipe-delimitedmanner i.e. "1"|"description for the chassis"|"2004-09-08").I'd like to get both of these resultsets and mash them together. Thisworks, but when I need to order the second resultset (i.e. select *from chassis order by chassis_id), SQL Server returns an errorcomplaining about the chassis_id column name (invalid column name'chassis_id') in the Order By clause.From what I can tell, I'm using the UNION and Order By in correctly,but I'm not sure exactly what's wrong with it. If I take out the OrderBy, everything works great. Although I would like to be able to ordermy second resultset (in the same sproc) if possible.The actual queries I'm running are actually quite long, but here's onethat's a bit shorter to help illustrate:SELECT '"app_group_id"|"app_group_name"|"create_date"|"create_by"|"modify_date"|"modify_by"'UNION ALLSELECT + ISNULL(CONVERT(varchar,app_group_id), '') + '|'+ SUBSTRING(RTRIM(LTRIM(CONVERT(varchar, 1))), 1,0)+ '"' + ISNULL(CONVERT(varchar(1000), +REPLACE(CONVERT(nvarchar(4000),app_group_name), '"', '""')), '') +'"|'+ SUBSTRING(RTRIM(LTRIM(CONVERT(varchar, 2))), 1,0)FROM app_grouporder by app_group_idThank for any help on this./bc
I have the following UNION ALL statement that is my attempt to gather data for the past 5 weekdays (adding a "dummy" row for today's data).
I want the final output to end up in descending order, so for today, I would want today first, then Tuesday, then Monday, then Friday, then Thursday (provided there is data for each sequential day - if not, you get the idea, I want to select back to get the latest 5 days, most recent to oldest).
This select fails, because it doesn't like the ORDER BY in the subqueryselect CASE WHEN DATENAME(dw, GETDATE()) = 'Monday' THEN 'MON' WHEN DATENAME(dw, GETDATE()) = 'Tuesday' THEN 'TUES' WHEN DATENAME(dw, GETDATE()) = 'Wednesday' THEN 'WED' WHEN DATENAME(dw, GETDATE()) = 'Thursday' THEN 'THUR' WHEN DATENAME(dw, GETDATE()) = 'Friday' THEN 'FRI' END AS Dow, 'N/A' AS Freight UNION ALL (select top 4 CASE WHEN DATENAME(dw, [OrderDate]) = 'Monday' THEN 'MON' WHEN DATENAME(dw, [OrderDate]) = 'Tuesday' THEN 'TUES' WHEN DATENAME(dw, [OrderDate]) = 'Wednesday' THEN 'WED' WHEN DATENAME(dw, [OrderDate]) = 'Thursday' THEN 'THUR' WHEN DATENAME(dw, [OrderDate]) = 'Friday' THEN 'FRI' END as DOW, CAST(CONVERT(int, (Freight * 100)) as VARCHAR(10)) as Freight from Northwind.dbo.orders where employeeid = 9 order by [OrderDate] desc )
I know you can't use an ORDER BY in a subquery, UNLESS the subquery also uses a TOP n (which this one does)...but does anyone know why this isn't liking my code?
I got the select to work the way I want it to by doing the following (really UGLY) code...SELECT U.DOW, U.Freight FROM ((select GETDATE() as [OrderDate], CASE WHEN DATENAME(dw, GETDATE()) = 'Monday' THEN 'MON' WHEN DATENAME(dw, GETDATE()) = 'Tuesday' THEN 'TUES' WHEN DATENAME(dw, GETDATE()) = 'Wednesday' THEN 'WED' WHEN DATENAME(dw, GETDATE()) = 'Thursday' THEN 'THUR' WHEN DATENAME(dw, GETDATE()) = 'Friday' THEN 'FRI' END AS Dow, 'N/A' AS Freight ) UNION ALL (select h.OrderDate as [OrderDate], h.DOW, h.Freight FROM (select top 4 [OrderDate] as [OrderDate], CASE WHEN DATENAME(dw, [OrderDate]) = 'Monday' THEN 'MON' WHEN DATENAME(dw, [OrderDate]) = 'Tuesday' THEN 'TUES' WHEN DATENAME(dw, [OrderDate]) = 'Wednesday' THEN 'WED' WHEN DATENAME(dw, [OrderDate]) = 'Thursday' THEN 'THUR' WHEN DATENAME(dw, [OrderDate]) = 'Friday' THEN 'FRI' END as DOW, CAST(CONVERT(int, (Freight * 100)) as VARCHAR(10)) as Freight from Northwind.dbo.orders where employeeid = 9 order by [OrderDate] desc ) H)) U order by OrderDate descbut am still confounded about why my original sub-select is rejected with such impunity.
My confusion seems likely related to understanding the set theory or basic concepts of the building of the select/Union rather than the way I am using the ORDER BY syntax, but I just can't seem to explain it to myself.
hello friends.. I am newbie for sql server...I having a problem when executing this procedure .... ALTER PROCEDURE [dbo].[spgetvalues] @Uid intASBEGIN SET NOCOUNT ON; select DATEPART(year, c.fy)as fy, (select contribeamount from wh_contribute where and contribename like 'Retire-Plan B-1% JRF' ) as survivorship, (select contribeamount from wh_contribute where and contribename like 'Gross Earnings' and ) as ytdgross, (select contribeamount from wh_contribute where and contribename like 'Retire-Plan B-1.5% JRP') as totalcontrib, from wh_contribute c where c.uid=@Uid Order by fy Asc .....what is the wrong here?? " Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression."please reply asap...
I am getting 2 resultsets depending on conditon, In the secondconditon i am getting the above error could anyone help me..........CREATE proc sp_count_AllNewsPapers@CustomerId intasdeclare @NewsId intset @NewsId = (select NewsDelId from NewsDelivery whereCustomerId=@CustomerId )if not exists(select CustomerId from NewsDelivery whereNewsPapersId=@NewsId)beginselect count( NewsPapersId) from NewsPapersendif exists(select CustomerId from NewsDelivery whereNewsPapersId=@NewsId)beginselect count(NewsDelId) from NewsDelivery whereCustomerid=@CustomeridendGO
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
while running the following query.
SELECT DISTINCT EmployeeDetails.FirstName+' '+EmployeeDetails.LastName AS EmpName,
LUP_FIX_DeptDetails.DeptName AS CurrentDepartment,
LUP_FIX_DesigDetails.DesigName AS CurrentDesignation,
LUP_FIX_ProjectDetails.ProjectName AS CurrentProject,
ManagerName=(SELECT E.FirstName+' '+E.LastName
FROM EmployeeDetails E
INNER JOIN LUP_EmpProject
ON E.Empid=LUP_EmpProject.Empid
INNER JOIN LUP_FIX_ProjectDetails
ON LUP_EmpProject.Projectid = LUP_FIX_ProjectDetails.Projectid
WHERE LUP_FIX_ProjectDetails.Managerid = E.Empid)
FROM EmployeeDetails
INNER JOIN LUP_EmpDepartment
ON EmployeeDetails.Empid=LUP_EmpDepartment.Empid
INNER JOIN LUP_FIX_DeptDetails
ON LUP_EmpDepartment.Deptid=LUP_FIX_DeptDetails.Deptid
AND LUP_EmpDepartment.Date=(SELECT TOP 1 LUP_EmpDepartment.Date
FROM LUP_EmpDepartment
WHERE EmployeeDetails.Empid=LUP_EmpDepartment.Empid
ORDER BY LUP_EmpDepartment.Date DESC)
INNER JOIN LUP_EmpDesignation
ON EmployeeDetails.Empid=LUP_EmpDesignation.Empid
INNER JOIN LUP_FIX_DesigDetails
ON LUP_EmpDesignation.Desigid=LUP_FIX_DesigDetails.Desigid
AND LUP_EmpDesignation.Date=(SELECT TOP 1 LUP_EmpDesignation.Date
FROM LUP_EmpDesignation
WHERE EmployeeDetails.Empid=LUP_EmpDesignation.Empid
ORDER BY LUP_EmpDesignation.Date DESC)
INNER JOIN LUP_EmpProject
ON EmployeeDetails.Empid=LUP_EmpProject.Empid
AND LUP_EmpProject.StartDate=(SELECT TOP 1 LUP_EmpProject.StartDate
FROM LUP_EmpProject
WHERE EmployeeDetails.Empid=LUP_EmpProject.Empid
ORDER BY LUP_EmpProject.StartDate DESC)
INNER JOIN LUP_FIX_ProjectDetails
ON LUP_EmpProject.Projectid=LUP_FIX_ProjectDetails.Projectid
I've running the below query for months ans suddenly today started getting the following error :"Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression."
Any ideas as to why??
SELECT t0.DocNum, t0.Status, t0.ItemCode, t0.Warehouse, t0.OriginNum, t0.U_SOLineNo, ORDR.NumAtCard, ORDR.CardCode, OITM_1.U_Cultivar, RDR1.U_Variety, (SELECT OITM.U_Variety FROM OWOR INNER JOIN WOR1 ON OWOR.DocEntry = WOR1.DocEntry INNER JOIN OITM INNER JOIN OITB ON OITM.ItmsGrpCod = OITB.ItmsGrpCod ON WOR1.ItemCode = OITM.ItemCode WHERE (OITB.ItmsGrpNam = 'Basic Fruit') AND (OWOR.DocNum = t0.DocNum)) AS Expr1, OITM_1.U_Organisation, OITM_1.U_Commodity, OITM_1.U_Pack, OITM_1.U_Grade, RDR1.U_SizeCount, OITM_1.U_InvCode, OITM_1.U_Brand, OITM_1.U_PalleBase, OITM_1.U_Crt_Pallet, OITM_1.U_LabelType, RDR1.U_DEPOT, OITM_1.U_PLU, RDR1.U_Trgt_Mrkt, RDR1.U_Wrap_Type, ORDR.U_SCCode FROM OWOR AS t0 INNER JOIN ORDR ON t0.OriginNum = ORDR.DocNum INNER JOIN RDR1 ON ORDR.DocEntry = RDR1.DocEntry AND t0.U_SOLineNo - 1 = RDR1.LineNum INNER JOIN OITM AS OITM_1 ON t0.ItemCode = OITM_1.ItemCode WHERE (t0.Status <> 'L')
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]
Microsoft SQL Server 2000 - 8.00.2191 (Intel IA-64)
Mar 27 2006 11:51:52
Copyright (c) 1988-2003 Microsoft Corporation
Enterprise Edition (64-bit) on Windows NT 5.2 (Build 3790: Service Pack 1)
sp_dboption 'BB_XXXXX'
The following options are set:
-----------------------------------
trunc. log on chkpt.
auto create statistics
auto update statistics
OK, the problem is that if a run the below query in server01, i get error 512:
Msg 512, Level 16, State 1, Line 1
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
But, if run the same query in the server02, the query work fine -.
I know that I can use IN, EXISTS, TOP, etc ... but I need understand this behavior.
I am trying to add the results of both of these queries together:
The purpose of the first query is to find the number of nulls in the TimeZone column.
Query 1:
SELECT COUNT(*) - COUNT (TimeZone) FROM tablename
The purpose of the second query is to find results in the AAST, AST, etc timezones.
Query 2:
SELECT COUNT (TimeZone) FROM tablename WHERE TimeZone NOT IN ('EST', 'MST', 'PST', 'CST')
Note: both queries produce a whole number with no decimals. Ran individually both queries produce accurate results. However, what I would like is one query which produced a single INT by adding both results together. For example, if Query 1 results to 5 and query 2 results to 10, I would like to see a single result of 15 as the output.
What I came up with (from research) is:
SELECT ((SELECT COUNT(*) - COUNT (TimeZone) FROM tablename) + (SELECT COUNT (TimeZone) FROM tablename WHERE TimeZone NOT IN ('EST', 'MST', 'PST', 'CST'))
I get a msq 102, level 15, state 1 error.
I also tried
SELECT ((SELECT COUNT(*) - COUNT (TimeZone) FROM tablename) + (SELECT COUNT (TimeZone) FROM tablename WHERE TimeZone NOT IN ('EST', 'MST', 'PST', 'CST')) as IVR_HI_n_AK_results
but I still get an error. For the exact details see:
[URL]
NOTE: the table in query 1 and query 2 are the same table. I am using T-SQL in SQL Server Management Studio 2008.
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.
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?
