Tuesday, August 6, 2013

[T-SQL] Subquery returned more than 1 value error message

[T-SQL] Subquery returned more than 1 value error message

Subquery returned more than 1 value error message

Posted: 05 Aug 2013 10:33 PM PDT

HiI'm receiving the following message:Msg 512, Level 16, State 1, Line 4Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expressionSo I'm guessing that in one of the sub-queries, I'm returning more than 1 row of data. Problem is that after dissecting this for hours, I can't find where. I can't see where I can replacing an '=' with 'IN', 'MAX', 'Top' etc. might be appropriate either.Strangely when I run the code against another schema in the same database it works fine.Help please!The code is:[code="sql"]declare @Locality varchar(max)set @Locality = 'Locality Yate'SELECT T.Locality,GS2.CodeDescription AS 'Specialty',Status,Count(T.ClientID) AS 'Number' FROM (SELECT REF.ClientID,REF.ReferralNumber,SpecialtyReferredTo,ServiceTeam,ISNULL((SELECT GS.CodeDescription FROM dbo.vwSGReferrals As REF2LEFT OUTER JOIN SchemaSG.GenServiceTeam AS GST ON GST.CodeDescription=REF2.ServiceTeamLEFT OUTER JOIN SchemaSG.AmsSpecialtyTeams AS AST ON GST.Code=AST.TeamLEFT OUTER JOIN SchemaSG.GenSpecialty AS GS ON AST.Specialty=GS.CodeWHERE REF.ClientID=Ref2.ClientID AND REF.ReferralNumber=REF2.ReferralNumberAND DischargeDateTime IS NULLAND AST.Specialty Like '%LOC%'),'No Locality') AS 'Locality' ,CASE WHEN (SELECT Max(Apps2.ContactID)FROM ABI_RiO.dbo.vwSGReferrals Refs2LEFT JOIN ABI_RiO.dbo.vwSGAppointmentsPD Apps2 ON Refs2.ClientID = Apps2. ClientID AND Refs2.ReferralNumber = Apps2.ReferralNumberLEFT OUTER JOIN ABI_RiO.SchemaSG.AmsOutcome AS AOUT ON AOUT.Code=APPs2.OutcomeWHERE Refs2.ClientID = Ref.ClientIDAND Refs2.ReferralNumber = Ref.ReferralNumberAND NationalCode=5) IS NOT NULL Then 'Active'ELSE 'Waiting'END As StatusFROM dbo.vwSGReferrals As REFWHERE DischargeDateTime IS NULL)TLEFT OUTER JOIN SchemaSG.GenSpecialty AS GS2 ON T.SpecialtyReferredTo=GS2.CodeWHERE (ISNULL(Locality,'NULL') IN (SELECT * FROM fnSplitList(@Locality, ','))) GROUP BY T.Locality,T.SpecialtyReferredTo,GS2.CodeDescription,Status;[/code]CheersTim

select DISTINCT cost too high?

Posted: 05 Aug 2013 10:50 PM PDT

Hi,Pl. suggested me, how to resolve this issuse? In select statment sub-tree DISTINCT cost is 79%. as per atttached actual exec.plan. Is it possible wirtting query alternative ways?[code="sql"]UPDATE CSV_Details_MainFile SET Mark_Rev_No = (CASE WHEN (SELECT COUNT(mark_rev_no) as mark_rev_no FROM CSV_Details_MainFile H1 WHERE H1.GA_Drg_NO = CSV_Details_MainFile.GA_Drg_NO AND H1.Mark_No = CSV_Details_MainFile.Mark_No) != 0 THEN (SELECT ISNULL(mark_rev_no,0) AS mark_rev_no FROM CSV_Details_MainFile H1 WHERE H1.GA_Drg_NO = CSV_Details_MainFile.GA_Drg_NO and H1.Rev_NO = CSV_Details_MainFile.Rev_NO AND H1.Mark_No = CSV_Details_MainFile.Mark_No and DeleteFlag='1' GROUP BY H1.GA_Drg_NO,H1.Mark_No ,H1.mark_rev_no) ELSE '1' END) WHERE CSV_Details_MainFile.GA_Drg_NO='C63-GPE105-499-005' AND Rev_NO = '1' AND CSV_Details_MainFile.Mark_No in (SELECT DISTINCT Mark_No FROM CSV_Details_MainFile WHERE GA_Drg_NO = 'C63-GPE105-499-005' AND Rev_NO ='1' and DeleteFlag is null) [/code]Thanksananda

Want to re-write a query to not use a cursor...

Posted: 06 Aug 2013 12:28 AM PDT

I came up with a query to give me an idea when the various SQL logins on my servers might expire / require a password change or if the account has been locked out (Devs trying to test stuff as the account, and using an incorrect password...)Now, I know there's going to be some howling, but I wound up using a cursor to accomplish this. I know for something like this, that's a run it once in a while, a cursor isn't really such a bad thing, but I'd like to see what would be done differently to get it to work without the cursor.So, the basics:The cursor grabs non-disabled, SQL type logins from sys.server_principals, then uses LOGINPROPERTY to collect the info I want.The code:[code="sql"]declare @user varchar(50)create table #userinfo ( Username varchar(50), badpasswordcount int, badpasswordtime datetime, dayuntilexpire datetime, lockouttime datetime, islocked int, passwordlastsettime datetime, likelynextchange datetime )declare usr_cursor cursor for select name from sys.server_principals where type = 'S' and is_disabled = 0 order by nameopen usr_cursorfetch next from usr_cursor into @userwhile @@FETCH_STATUS = 0begin insert into #userinfo select @user as 'User name', convert(int, LOGINPROPERTY(@user, 'BadPasswordCount')), convert(datetime, LOGINPROPERTY(@user, 'BadPasswordTime')), convert(datetime, LOGINPROPERTY(@user, 'DaysUntilExpiration')), convert(datetime, LOGINPROPERTY(@user, 'LockoutTime')), convert(int, LOGINPROPERTY('loginname', 'IsLocked')), convert(datetime, LOGINPROPERTY(@user, 'PasswordLastSetTime')), DATEADD(DD, 90, (convert(datetime, LOGINPROPERTY(@user, 'PasswordLastSetTime')))) fetch next from usr_cursor into @user endclose usr_cursordeallocate usr_cursorselect * from #userinfodrop table #userinfo;[/code]This is NOT a homework-type thing, this is me looking to expand my skills / knowledge, and looking for some help.Thanks all,Jason

SQL Query help

Posted: 05 Aug 2013 10:52 PM PDT

[code="sql"]CREATE TABLE [dbo].[dp]( [nr] [char](12) NOT NULL, [type] [char](12) NOT NULL, [DT] [int] NOT NULL,)INSERT INTO [dbo].[dp] VALUES ('1','OF',49),('2','OF',49),('3','OF',49),('4','CF',49),('5','CF',49),('6','CF',49),('7','CF',49),('1','AV',11),('2','HK',11),('3','HK',11),('1','AV',67),('2','HK',67),('1','AV',18),('2','SA',18),('1','AV',80),('2','AV',80)CREATE TABLE [dbo].[ln]( [nr] [char](12) NOT NULL, [type] [char](12) NOT NULL, [DT] [int] NOT NULL,)INSERT INTO [dbo].[ln] VALUES ('1','IL',70),('2','IL',70),('1','IL',69),('2','IL',69),('1','MI',89),('2','MI',89),('3','MI',89),('4','MI',89)[/code][b][i]I want a query that will only retrieve all DT that have only one type of nr[/i][/b]i.e, the outcome should be like below and the results should be union as the two table have two different functions with some minor difference in the structure[code="sql"]from the dp table the result expected should be'1','AV',80'2','AV',80from the ln table the result expected should be'1','IL',70'2','IL',70'1','IL',69'2','IL',69'1','MI',89'2','MI',89'3','MI',89'4','MI',89[/code]Thank youCarnalito

Query Challenge

Posted: 05 Aug 2013 08:21 PM PDT

The time interval on the table increments to approximate 20~25 sec , need to group based on the time ,the iterations are 2 0r 3 in the table in each 20 sec Table data DateS Value2013-08-06 08:01:01.430 82013-08-06 08:01:20.430 22013-08-06 08:01:40.240 22013-08-06 08:05:41.810 12013-08-06 08:06:20.217 12013-08-06 08:10:21.750 12013-08-06 08:10:40.657 52013-08-06 08:11:02.217 12013-08-06 08:15:02.177 12013-08-06 08:15:20.413 22013-08-06 08:15:40.180 12013-08-06 08:19:42.117 12013-08-06 08:20:00.307 22013-08-06 08:24:21.493 52013-08-06 08:24:40.637 22013-08-06 08:25:00.353 22013-08-06 08:29:01.930 22013-08-06 08:29:20.277 12013-08-06 08:29:40.400 12013-08-06 08:33:42.830 12013-08-06 08:34:00.320 12013-08-06 08:34:20.477 1Expected DateS Value GroupNumber2013-08-06 08:01:01.430 8 12013-08-06 08:01:20.430 2 12013-08-06 08:01:40.240 2 12013-08-06 08:05:41.810 1 22013-08-06 08:06:20.217 1 22013-08-06 08:10:21.750 1 32013-08-06 08:10:40.657 5 32013-08-06 08:11:02.217 1 32013-08-06 08:15:02.177 1 42013-08-06 08:15:20.413 2 42013-08-06 08:15:40.180 1 42013-08-06 08:19:42.117 1 52013-08-06 08:20:00.307 2 52013-08-06 08:24:21.493 5 62013-08-06 08:24:40.637 2 62013-08-06 08:25:00.353 2 62013-08-06 08:29:01.930 2 72013-08-06 08:29:20.277 1 72013-08-06 08:29:40.400 1 72013-08-06 08:33:42.830 1 82013-08-06 08:34:00.320 1 82013-08-06 08:34:20.477 1 8 Final Expected from Group--Need to take average of Value and Max of dates on each GROUP DateS Value GroupNumber2013-08-06 08:01:01.430 8 12013-08-06 08:01:20.430 2 12013-08-06 08:01:40.240 2 12013-08-06 08:05:41.810 1 22013-08-06 08:06:20.217 1 2Final result MaxDateS AvgValue GroupNumber2013-08-06 08:01:40.240 3 1 2013-08-06 08:06:20.217 1 2[code="sql"]CREATE TABLE #LOGTABLE ( DateS DATETIME ,Value INT )INSERT INTO #LOGTABLE ( DateS ,Value ) SELECT '2013-08-06 08:01:01.430',8UNION ALL SELECT '2013-08-06 08:01:20.430',2UNION ALL SELECT '2013-08-06 08:01:40.240',2UNION ALL SELECT '2013-08-06 08:05:41.810',1UNION ALL SELECT '2013-08-06 08:06:00.763',2UNION ALL SELECT '2013-08-06 08:06:20.217',1UNION ALL SELECT '2013-08-06 08:10:21.750',1UNION ALL SELECT '2013-08-06 08:10:40.657',5UNION ALL SELECT '2013-08-06 08:11:02.217',1UNION ALL SELECT '2013-08-06 08:15:02.177',1UNION ALL SELECT '2013-08-06 08:15:20.413',2UNION ALL SELECT '2013-08-06 08:15:40.180',1UNION ALL SELECT '2013-08-06 08:19:42.117',1UNION ALL SELECT '2013-08-06 08:20:00.307',2UNION ALL SELECT '2013-08-06 08:20:21.133',1UNION ALL SELECT '2013-08-06 08:24:21.493',5UNION ALL SELECT '2013-08-06 08:24:40.637',2UNION ALL SELECT '2013-08-06 08:25:00.353',2UNION ALL SELECT '2013-08-06 08:29:01.930',2UNION ALL SELECT '2013-08-06 08:29:20.277',1UNION ALL SELECT '2013-08-06 08:29:40.400',1UNION ALL SELECT '2013-08-06 08:33:42.830',1UNION ALL SELECT '2013-08-06 08:34:00.320',1UNION ALL SELECT '2013-08-06 08:34:20.477',1[/code]

RECONFIGURE inside trigger

Posted: 05 Aug 2013 02:44 AM PDT

Hello SQL fansI am working on one project and got to the point where I have a problem and need your help.I have a after insert trigger and in it I want to execute Ole Automation Procedures.The problem is, that sql doesn't allow RECONFIGURE statement within a trigger.All I want is to execute this lines of code before procedure executen EXEC sp_configure 'show advanced options', 1 RECONFIGURE EXEC sp_configure 'Ole Automation Procedures', 1 RECONFIGUREand disabling it after my stored procedure.Here is my code[code="sql"]create TRIGGER tr_ORS_CostDrvAfterInsert ON _tmp1AFTER INSERTAS BEGIN SET NOCOUNT ON; declare @URI varchar(2000) , @methodName varchar(50), @requestBody varchar(8000) = '', @SoapAction varchar(255), @UserName nvarchar(100), @Password nvarchar(100), @responseText varchar(8000); set @URI = 'http://MyURI.php'; set @methodName = 'GET'; set @requestBody = ''; set @SoapAction = 'Method'; set @UserName = ''; set @Password = ''; EXEC sp_configure 'show advanced options', 1 RECONFIGURE EXEC sp_configure 'Ole Automation Procedures', 1 RECONFIGURE exec [dbo].[sp_ORS_MS_Request] @URI, @methodName, @requestBody, @SoapAction, @UserName, @Password, @responseText output; EXEC sp_configure 'Ole Automation Procedures', 0 RECONFIGURE EXEC sp_configure 'show advanced options', 0 RECONFIGURE END[/code]

How to select range of consecutive events

Posted: 05 Aug 2013 06:15 AM PDT

This is my first cry for help, so be gentle with me. :)I've been searching the forum for a clue but I'm still having trouble figuring out how to produce the query results I need. and boy does it hurt my pride to say that... :)The data is fairly simple, a Station ID and a Start & End time. (along with a bunch of other data that needs to be added or dissected.)I have a list of events for every day and I need to identify blocks of two or more consecutive events and return the first start time and the last end time. This would also be the MIN(start) and MAX(End) of those group of consecutive events.I can identify the first, last and "middle" records by doing a join back to the table linking the start time of one record to the end time of the other, but I can't figure out how to group one "group" of consecutive events, as there could be multiple groups in a day.There are some events which are "singular", meaning that they have no adjoining records, and the group could be 2 or 20 consecutive events, and could span midnight if a show runs over.Here is what the sample data would look like and the output I'm trying to accomplish below that.I can turn all of this into a make table & insert statements if you want data to play with.I appreciate the help.Thanks,TadStation Start EndWKBW 7/5/13 11:00 AM 7/5/13 11:30 AMWKBW 7/5/13 12:00 PM 7/5/13 12:30 PMWKBW 7/5/13 12:30 PM 7/5/13 1:00 PMWKBW 7/5/13 1:00 PM 7/5/13 1:30 PMWKBW 7/5/13 1:30 PM 7/5/13 2:00 PMWKBW 7/9/13 11:30 AM 7/9/13 12:00 PMWKBW 7/9/13 12:00 PM 7/9/13 12:30 PMWKBW 7/12/13 12:30 PM 7/12/13 1:00 PMWKBW 7/12/13 1:00 PM 7/12/13 1:30 PMWKBW 7/12/13 1:30 PM 7/12/13 2:00 PMWKBW 7/17/13 12:00 PM 7/17/13 1:00 PMWKBW 7/18/13 1:30 PM 7/18/13 2:00 PMWKBW 7/19/13 12:30 PM 7/19/13 1:00 PMWKBW 7/19/13 1:00 PM 7/19/13 1:30 PMWKBW 7/19/13 1:30 PM 7/19/13 2:00 PMWKBW 7/19/13 3:00 PM 7/19/13 4:00 PM KFUN 7/22/13 4:30 AM 7/22/13 7:00 AMKFUN 7/22/13 7:00 AM 7/22/13 9:00 AMKFUN 7/22/13 12:00 PM 7/22/13 12:30 PMKFUN 7/22/13 5:00 PM 7/22/13 6:30 PMKFUN 7/23/13 4:30 AM 7/23/13 7:00 AMKFUN 7/23/13 7:00 AM 7/23/13 9:00 AMKFUN 7/23/13 9:30 AM 7/23/13 10:00 AMKFUN 7/23/13 10:00 AM 7/23/13 10:47 AMKFUN 7/23/13 10:53 AM 7/23/13 11:18 AMKFUN 7/23/13 11:18 AM 7/23/13 11:54 AMKFUN 7/23/13 12:00 PM 7/23/13 12:30 PMKFUN 7/23/13 5:00 PM 7/23/13 6:30 PMKFUN 7/23/13 9:00 PM 7/23/13 10:30 PMKFUN 7/24/13 4:30 AM 7/24/13 7:00 AMKFUN 7/24/13 7:00 AM 7/24/13 9:00 AMKFUN 7/24/13 9:00 PM 7/24/13 10:30 PMKFUN 7/25/13 4:30 AM 7/25/13 7:00 AMKFUN 7/25/13 7:00 AM 7/25/13 9:00 AM OUTPUT KFUN 7/22/13 4:30 AM 7/22/13 9:00 AMKFUN 7/23/13 4:30 AM 7/23/13 9:00 AMKFUN 7/23/13 9:30 AM 7/23/13 10:47 AMKFUN 7/23/13 10:53 AM 7/23/13 11:54 AMKFUN 7/24/13 4:30 AM 7/24/13 9:00 AMKFUN 7/25/13 4:30 AM 7/25/13 9:00 AMWKBW 7/5/13 12:00 PM 7/5/13 2:00 PMWKBW 7/9/13 11:30 AM 7/9/13 12:30 PMWKBW 7/12/13 12:30 PM 7/12/13 2:00 PMWKBW 7/19/13 12:30 PM 7/19/13 2:00 PM

How to improve the performance of this query?

Posted: 05 Aug 2013 06:20 PM PDT

The current result when take from INFORMATION_SCHEMA.COLUMNSTABLE COLUMNTable1 Column1Table1 Column2Table1 Column3Table2 Column1Table2 Column2Table2 Column3Expected resultTABLE COLUMNSTable1 Column1,Column2,Column3Table2 Column1,Column2,Column3I have tried 2 methods Method 1SELECT c1.Table_name, STUFF((Select ',' + c2.COLUMN_NAME AS [text()] from INFORMATION_SCHEMA.COLUMNS c2 where c1.TABLE_NAME=c2.TABLE_NAME and c1.column_name=c2.column_name --Order by c2.TABLE_NAME for xml path ( '' )), 1, 1,'' ) as "Column_names" from INFORMATION_SCHEMA.COLUMNS c1group by Table_nameWhich throws the error Msg 8120, Level 16, State 1, Line 4Column 'INFORMATION_SCHEMA.COLUMNS.COLUMN_NAME' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.But adding column_name to the group by clause does not give the desired resultMethod 2;WITH CTE (SRNO,Table_name,column_name)AS(SELECT ROW_NUMBER() OVER (PARTITION BY Table_name ORDER BY Table_name,column_name) AS SRNO,Table_name,CAST(column_name AS VARCHAR(MAX)) FROM INFORMATION_SCHEMA.COLUMNS),CTE1(SRNO,Table_name,column_name)AS(SELECT * FROM CTE WHERE SRNO=1UNION ALLSELECT CTE.SRNO AS SRNO, CTE.Table_name,CAST (CTE1.column_name + ',' + CTE.column_name AS VARCHAR(MAX)) FROM CTE INNER JOIN CTE1ON CTE.Table_name=CTE1.Table_name AND CTE.SRNO=CTE1.SRNO+1)SELECT Table_name, MAX(column_name) AS CSV FROM CTE1 GROUP BY CTE1.Table_nameoption (maxrecursion 100)But this takes a very long time.Kindly help me get the desired result in the most optimum way

SELECT ... INTO NewTable without nulls

Posted: 05 Aug 2013 05:05 AM PDT

I am creating a table by using theSELECT column1, column2INTO NewTableFROM OldTablemethod. Is there a way of creating a table in this manner and saying if the columns should be null or not null? Currently I am just using an alter statement to change a column from null to NOT NULL, but was wondering if there is a way to eliminate this step. Relatedly, can anyone think of a way for me to stop getting obsessed with ways of cutting out 1-2 lines of code and wasting all this time figuring out things I've already found solutions for? :crazy:Thank you,Amy

Query Help - Sum by Month

Posted: 05 Aug 2013 01:48 AM PDT

I have a table of values by date. I have a requirement to group and sum them by month (to use to plot on a chart). I'm not really sure how to go about grouping by a range. That is if I have:01/03/2012 $30001/12/2012 $25002/05/2012 $20002/07/2012 $30002/15/2012 $400I need it grouped as two rows01/2012 $55002/2012 $900Thanks for any help.Sean
Posted by at

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)

Search This Blog