Error 116 Only One Expression
Contents |
here for a quick overview of the site Help Center Detailed answers to any questions you might have Meta Discuss the workings and policies of this site About Us Learn more about Stack Overflow the company only one expression can be specified in the select list when the subquery is not introduced Business Learn more about hiring developers or posting ads with us Stack Overflow Questions Jobs exists query Documentation Tags Users Badges Ask Question x Dismiss Join the Stack Overflow Community Stack Overflow is a community of 4.7 million programmers, just subquery is not introduced with exists like you, helping each other. Join them; it only takes a minute: Sign up Only one expression can be specified in the select list when the subquery is not introduced with EXISTS up vote 80 down vote
Subquery Exists
favorite 4 My query is as follows, and contains a subquery within it: select count(distinct dNum) from myDB.dbo.AQ where A_ID in (SELECT DISTINCT TOP (0.1) PERCENT A_ID, COUNT(DISTINCT dNum) AS ud FROM myDB.dbo.AQ WHERE M > 1 and B = 0 GROUP BY A_ID ORDER BY ud DESC) The error I am receiving is ... Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.` When subquery returned more than 1 value. this is not permitted when the subquery follows I run the sub-query alone, it returns just fine, so I am assuming there is some issue with the main query? sql sql-server tsql share|improve this question edited Oct 29 '13 at 12:45 SteveC 4,025135097 asked Dec 14 '09 at 23:39 rockit 90541427 add a comment| 3 Answers 3 active oldest votes up vote 109 down vote accepted You can't return two (or multiple) columns in your subquery to do the comparison in the WHERE A_ID IN (subquery) clause - which column is it supposed to compare A_ID to? Your subquery must only return the one column needed for the comparison to the column on the other side of the IN. So the query needs to be of the form: SELECT * From ThisTable WHERE ThisColumn IN (SELECT ThatColumn FROM ThatTable) You also want to add sorting so you can select just from the top rows, but you don't need to return the COUNT as a column in order to do your sort; sorting in the ORDER clause is independent of the columns returned by the query. Try something like this: select count(distinct dNum) from myDB.dbo.AQ where A_ID in (SELECT DISTINCT TOP (0.1) PERCENT A_ID FROM myDB.dbo.AQ WHERE M > 1 and B = 0 GROUP BY A_ID ORDER BY COUNT(DISTINCT dNum) DESC) share|improve this answer edited Aug 27 '15 at
here for a quick overview of the site Help Center Detailed answers to any questions you might have Meta Discuss the workings and policies of this site About Us Learn more about Stack Overflow the company Business Learn more about hiring exists sql server developers or posting ads with us Stack Overflow Questions Jobs Documentation Tags Users Badges Ask Question
An Expression Of Non Boolean Type Specified In A Context Where A Condition Is Expected Near
x Dismiss Join the Stack Overflow Community Stack Overflow is a community of 4.7 million programmers, just like you, helping each other. Join them;
Cannot Perform An Aggregate Function On An Expression Containing An Aggregate Or A Subquery.
it only takes a minute: Sign up How to fix “Only one expression can be specified in the select list when the subquery is not introduced with EXISTS” error? up vote 10 down vote favorite 2 I'm trying to http://stackoverflow.com/questions/1904314/only-one-expression-can-be-specified-in-the-select-list-when-the-subquery-is-not run the following query on MS SQL 2012 Express: Select ( Select Id, Salt, Password, BannedEndDate from Users where username = '" + LoginModel.Username + "' ), ( Select Count(*) From LoginFails where username = '" + LoginModel.Username + "' And IP = '" + Request.ServerVariables["REMOTE_ADDR"] + "')" ); But I get the following error: Only one expression can be specified in the select list when the subquery is not introduced with EXISTS. How can I solve this problem? http://stackoverflow.com/questions/17403475/how-to-fix-only-one-expression-can-be-specified-in-the-select-list-when-the-sub sql sql-server tsql share|improve this question edited Jul 1 '13 at 11:34 Danny Beckett 10k145394 asked Jul 1 '13 at 11:32 Jamie 6212827 9 You are selecting multiple columns in your first sub-query. But each sub query can only return one column. –Tim Schmelter Jul 1 '13 at 11:36 1 You should also read up on SQL injection. You are using a request variable directly in a dynamically created SQL - Very bad coding style. –a_horse_with_no_name Jul 1 '13 at 11:40 i am using parameters for every query that has user input, the username can only contain numbers and letters. This is being checked by the model. (I'm using asp.net mvc). Thanks for the tip though! –Jamie Jul 1 '13 at 11:47 add a comment| 2 Answers 2 active oldest votes up vote 6 down vote accepted Try this: Select Id, Salt, Password, BannedEndDate, (Select Count(*) From LoginFails Where username = '" + LoginModel.Username + "' And IP = '" + Request.ServerVariables["REMOTE_ADDR"] + "') From Users Where username = '" + LoginModel.Username + "' And I recommend you strongly to use parameters in your query to avoid security risks with sql injection attacks! Hope that helps! share|improve this answer edited Dec 21 '15 at 3:25 Aran Mulholland 13.2k1898183 answered Jul 1 '13 at 11:39 mortb 2,2682622 This works perfectly, thank you very much and thanks for the tip! –Jamie
the select list when the subquery is not introduced with EXISTS. Severity level:16. Description:This error message appears, when a subquery that is not introduced with EXISTS contains more that one expression http://www.sql-server-performance.com/2007/error116/ in the SELECT list. Consequences:The T-SQL statement can be parsed, but causes the error at runtime. Resolution:Error of the Severity Level 15 are generated by the user and can be fixed by the SQL Server user. You need to remove all but one expression from the SELECT list. Versions:All versions of SQL Server. Example(s):USE NorthwindSELECT t1.* FROM dbo.Orders t1WHERE t1.OrderDate = (SELECT MAX(OrderDate), CustomerID FROM dbo.Orders WHERE t1.CustomerID = is not CustomerID GROUP BY CustomerID) Remarks:In the above example we try to retrieve the most current order from each customer. The correlation between the subquery and the outer SELECT is created via t1.CustomerID = CustomerID. The error is raised, because we entered two expressions in the SELECT list of the subquery (MAX(OrderDate), CustomerID). To turn the query into a syntactically correct one, we need to modify the query to:USE NorthwindSELECT t1.* FROM only one expression dbo.Orders t1WHERE t1.OrderDate = (SELECT MAX(OrderDate) FROM dbo.Orders WHERE t1.CustomerID = CustomerID) ASK A QUESTION Tweet Array Errors No comments yet... Be the first to leave a reply! Leave a Reply Click here to cancel reply. Popular Latest Tags Setting up Transactional Replication in SQL Server 2008 R2. November 30, -0001 Backing Up a SQL Server Database Directly Onto a Remote Server November 30, -0001 Recovering a SQL Server Database from Suspect Mode November 30, -0001 SQL Server T-SQL Tuning - NOT IN and NOT Exists November 30, -0001 SQL Server T-SQL Tuning - Temp Tables, Table Variables and Union November 30, -0001 SQL Server Logical Reads – What do they really tell us? October 4, 2016 Physical Join Operators in SQL Server - Hash Operator September 21, 2016 Physical Join Operators in SQL Server - Merge Operator August 25, 2016 Techniques to Monitor SQL Server memory usage August 12, 2016 Recovering a SQL Server Database from Suspect Mode August 7, 2016 Forum posts... 2008 R2 2012 Agent AlwaysOn Analysis Services Backup Clustering Data Mining deadlock Denormalization Disaster Recovery Email Entity Framework Excel Geo high availability Identity Identity Column index Jobs Log Shipping mail Monitoring Oracle Query Query Plan range Replication Reporting Services Report Manager Restore Security Spatial Dat