SQL 2012 :: Only One Expression Can Be Specified In Select List When Subquery Not Introduced With EXISTS
Apr 28, 2015
I am getting error [[Msg 116, Level 16, State 1, Line 7 .Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.]] for the below script.
Hi, When i try to save my stored procedure.. i am getting the above error and this is my sproc 1 INSERT INTO Statement..ClientSources 2 ( 3 ClientId, 4 ClientSourceId, 5 SourceName 6 ) 7 Select Distinct 8 @ClientId, 9 SOURCE_NUM, 10 (Select CASE s.SOURCE_NUMWhen 1 Then SRC1NAME 11 WHEN 2 Then SRC2NAME 12 WHEN 3 THEN SRC3NAME 13 WHEN 4 THEN SRC4NAME 14 WHEN 5 THEN SRC5NAME 15 WHEN 6 THEN SRC6NAME 16 WHEN 7 THEN SRC7NAME 17 WHEN 8 THEN SRC8NAME 18 WHEN 9 THEN SRC9NAME 19 WHEN 10 THEN SRC10NAME 20 WHEN 11 THEN SRC11NAME 21 WHEN 12 THEN SRC12NAME 22 WHEN 13 THEN SRC13NAME 23 WHEN 14 THEN SRC14NAME 24 WHEN 15 THEN SRC15NAME 25 END 26 FROM 27 PlanDBF p 28 Where 29 p.PLAN_NUM = s.PLAN_NUM 30 ) as SourceName 31 FROM 32 SourceDBF s 33 Where 34 SOURCE_NUM NOT IN ( 35 SELECT DISTINCT 36 ClientSourceId 37 --SourceName 38 FROM 39 Statement..ClientSources 40 Where 41 ClientId = @ClientId 42 )
I am getting the error in Line number 35 .. the inserts works fine... and if use * instead of the field name or use more than 1 field name i get this error Only one expression can be specified in the select list when the subquery is not introduced with EXISTS. Any help will be appreciated. Regards Karen
When I try to save this stored procedure in VS.NET to SQL Server 2000 database, I keep getting the following error and have no idea how to correct it: "ADO error: Only one expression can be specified in the select list when the subquery is not introduced with EXISTS"
ALTER PROCEDURE dbo.sProcRevenueForTerminalByAcocuntManager ( @fromDate datetime, @toDate datetime )
AS SET NOCOUNT ON RETURN ( SELECT TOP 100 PERCENT T2.TerminalCode AS GroupName1, T3.TerminalName AS GroupName2, T2.AccountManagerName AS DetailName, SUM(T1.InvoiceDEDTotalAmount) AS DetailRevenue FROM dbo.TBLINVOICEDED T1 INNER JOIN dbo.TBLCUSTOMERS T2 ON T1.CustomerId = T2.CustomerId INNER JOIN dbo.TBLTERMINALDATA T3 ON T2.TerminalCode = T3.TerminalCode WHERE (T1.InvoiceDEDDate >= @fromDate) AND (T1.InvoiceDEDDate <= @toDate) GROUP BY T2.AccountManagerName, T2.TerminalCode, T3.TerminalName HAVING (NOT (SUM(T1.InvoiceDEDTotalAmount) IS NULL)) ORDER BY T2.TerminalCode, T2.AccountManagerName )
I am trying to determine the existence of at least one row in my Detail table using EXISTS in my SELECT list from my Main table.SELECT M.ID,EXISTS(SELECT 1 FROM Detail D WHERE D.ID = M.ID) as HasDataFROM Main MCan this be done this way?I was hoping that using EXISTS would find a row and move on thus increasing performance.
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
DOC_NO // REV_NO // FILE_NAME ABC123 // A // abc123.pdf ABC123 // B // abc123_2.docx ABC124 // A // abc124.xlsx ABC124 // A // - ABC125 // A // abc125.docx ABC125 // C // abc125.jpg ABC125 // C // abc125.docx ABC125 // C // - ABC126 // 0 // - ABC127 // A1 // abc127.xlsx ABC127 // A1 // abc127.pdf
I'm looking to select all rows where the DOC_NO and REV_NO appear only once.(i.e. the combination of the two values together, not any distinct value in a column)
I have written the sub query to filter the correct results;
SELECT DOC_NO, REV_NO FROM [MYTABLE] GROUP BY DOC_NO, REV_NO HAVING COUNT(*) =1
I now need to strip out the records which have no file (represented as "-" in the FILE_NAME field) and select the other fields (same table - for example, lets just say "ADD1", "ADD2" and "ADD3")
I was looking to put together a query like;
SELECT DOC_NO, REV_NO, FILE_NAME, ADD1, ADD2, ADD3 FROM [MYTABLE] WHERE FILE_NAME NOT LIKE '-' AND DOC_NO IN (SELECT DOC_NO, REV_NO FROM [MYTABLE] GROUP BY DOC_NO, REV_NO HAVING COUNT(*) =1)
But of course, DOC_NO alone being in the subquery select is not sufficient, as (ABC125 /A) is a unique combination, but (ABC125 /C) is not, but these results would be pulled in.
I also cannot simply add an additional "AND" clause on its own to make sure the REV_NO value appears in the subquery, because it is highly repetitive and would have to specifically match the DOC_NO)
What is the easiest way of ensuring that I only pull in the records where both the DOC_NO and REV_NO (combination) are unique, or is there a better way of putting this select together altogether?
I have a table with dates and values and other columns. In a proc i need to get the result as Month and the values for all the months whether or not the data exists for the month.
The Similar table would be-
create table testing( DepDate datetime, val int) insert into testing values ('2014-01-10 00:00:00.000', 1) insert into testing values ('2014-05-19 00:00:00.000', 10) insert into testing values ('2014-08-15 00:00:00.000', 20) insert into testing values ('2014-11-20 00:00:00.000', 30)
Hi, I have 2 tables:tblStations StationIDStationtblStationUser RecordIDUserNameStationI'm trying to come up with a dataset that contains thetblStations.StationEXCEPT for where that Station exists in tblStationUser where theUserName = @varUserName.I've tried this but get 0 rows (I should get about 40):SELECT tblStations.StationFROM tblStationsWHERE NOT EXISTS(SELECT tblStationUser.Station FROM tblStationUser WHEREtblStationUser.UserName=@varUserName)ORDER BY StationI tried the subquery separately which returns the correct number ofrows.Any clues as to where I'm going wrong?Thanks!Kathy
Im having a slight problem deleting/ updating some specific rows out of my table
when i worked out the select statment i got the correct return on data however when i place the same logic into a delete or update it seems to apply the logic to everything any help would be much appreciated :)
the select query: select distinct id, max(timeexecuted) from table1 group by id
the delete query: delete table1 where exists (select distinct id, max(timeexecuted) from table1 group by id)
Usually it is better to include the columns in the index that are in where clause, select list and join.I am thinking that the columns in the selected list is better to keep as index columns and the columns that are in the where clause is better to keep in key columns.Where do we use join column is it better to create as main key column or included column.
I have a specific variation on the standard 'Column Invalid' question: I have this query that works fine:
SELECT vd.Question , csq.Q# , csq.Q_Sort , csq.Q_SubSort , AVG(CAST(vd.Response AS FLOAT)) AS AvgC , vd.RType
[Code] ....
When I add this second average column like this:
SELECT vd.Question , csq.Q# , csq.Q_Sort , csq.Q_SubSort , AVG(CAST(vd.Response AS FLOAT)) AS AvgC ,
[Code] ....
I get the error: Column 'dbo.vwData.Response' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
Clearly things are in the right place before the change, so I can only assume that the OVER clause is my problem. Is this just not possible?
When I execute the below queries it works perfectly where as my expectation is, it should break.
Select * from ChildDepartment C where C.ParentId IN (Select Id from TestDepartment where DeptId = 1) In TestDepartment table, I do not have ID column. However the select in sub query works as ID column exists in ChildDepartment. If I do change the query to something below then definately it will break - Select * from ChildDepartment C where C.ParentId IN (Select D.Id from TestDepartment D where D.DeptId = 1)
Shouldn't the default behavior be otherwise? It should throw error if column doesnt exists in sub query table and force me to define the correct source table or alias name.
create table TestDepartment ( DeptId int identity(1,1) primary key, name varchar(50) ) create table ChildDepartment ( Id int identity(1,1) primary key,
Hello any MS SQL experts out there! please help if you can. i'm trying to run a subquery within a query to keep myself from having to loop over the original query on display and then run additional queries to get the further info. here's the setup. i have two tables:
persons table column: name (varchar) column: vehicleids (varchar)
vehicles table column: id (int pk) column: vehiclename (varchar)
- The persons table is a list of peoples' names and what kind of vehicle/s they own. - The persons.vehicleids field is a comma-delimited list of one or more integers which correspond to the vehicles.id field. - The vehicles table contains a list of vehicles like car, bicycle, motorcycle, etc, distinguished by the vehicles.id field.
The result i want returned by the query is:
NAME - VEHICLES Joe Somebody - car,bicycle Sheila Johnson - van,pogostick,motorcycle John Nobody - skateboard,car
The query i'm trying to run to get this result is:
Code Snippet
SELECT pe.name, ( SELECT ve.vehiclename FROM vehicles ve WHERE CAST(ve.id AS VARCHAR) IN (pe.vehicleids) ) AS vehicles FROM persons pe ORDER BY pe.name
It returns the persons names in the first column, but only returns vehicle names in the second column when there's a single id in the persons.vehicleids field. if there's more than one integer in the field, it returns an empty string.
Can anyone tell me what I'm doing wrong? I do have the option of table restructuring if its necessary, but I'm not looking for a stored procedure solution or a temp table solution. Any takers? I would be in the kharmic debt of anyone providing a workable avenue.
So at the moment, I don't have a function by the name CONCATENATE. What I like to do is to list all those different values that go with a single CASE_ID to appear as a a comma separate list. You might have a better way of doing without even writing a function
I have a select list of fields that I need to select to get the results I need, however, I would like to insert only a chosen few of these fields into a table. I am getting the error, "The select list for the INSERT statement contains more items than the insert list. The number of SELECT values must match the number of INSERT columns." How can I do this?
Insert Query: insert into tsi_payments (PPID, PTICKETNUM, PLINENUM, PAMOUNT, PPATPAY, PDEPOSITDATE, PENTRYDATE, PHCPCCODE) SELECT DISTINCT tri_IDENT.IDA AS PPID, tri_Ldg_Tran.CLM_ID AS PTicketNum, tri_ClaimChg.Line_No AS PLineNum, tri_Ldg_Tran.Tran_Amount AS PAmount,
CASE WHEN tln_PaymentTypeMappings.PTMMarsPaymentTypeCode = 'PATPMT' THEN tri_ldg_tran.tran_amount * tln_PaymentTypeMappings.PTMMultiplier ELSE 0 END AS PPatPay,
tri_Ldg_Tran.Create_Date AS PDepositDate, tri_Ldg_Tran.Tran_Date AS PEntryDate, tri_ClaimChg.Hsp_Code AS PHCPCCode, tri_Ldg_Tran.Adj_Type, tri_Ldg_Tran.PRS_ID, tri_Ldg_Tran.Create_Time, tri_Ldg_Tran.Adj_Group, tri_Ldg_Tran.Payer_ID, tri_Ldg_Tran.TRN_ID, tri_ClaimChg.Primary_Claim, tri_IDENT.Version FROM [AO2AO2].MARS_SYS.DBO.tln_PaymentTypeMappings tln_PaymentTypeMappings RIGHT OUTER JOIN qs_new_pmt_type ON tln_PaymentTypeMappings.PTMClientPaymentDesc = qs_new_pmt_type.New_Pmt_Type RIGHT OUTER JOIN tri_Ldg_Tran RIGHT OUTER JOIN tri_IDENT LEFT OUTER JOIN tri_ClaimChg ON tri_IDENT.Pat_Id1 = tri_ClaimChg.Pat_ID1 ON tri_Ldg_Tran.PRS_ID = tri_ClaimChg.PRS_ID AND tri_Ldg_Tran.Chg_TRN_ID = tri_ClaimChg.Chg_TRN_ID AND tri_Ldg_Tran.Pat_ID1 = tri_IDENT.Pat_Id1 LEFT OUTER JOIN tri_Payer ON tri_Ldg_Tran.Payer_ID = tri_Payer.Payer_ID ON qs_new_pmt_type.Pay_Type = tri_Ldg_Tran.Pay_Type AND qs_new_pmt_type.Tran_Type = tri_Ldg_Tran.Tran_Type WHERE (tln_PaymentTypeMappings.PTMMarsPaymentTypeCode <> N'Chg') AND (tln_PaymentTypeMappings.PTMClientCode = 'SR') AND (tri_ClaimChg.Primary_Claim = 1) AND (tri_IDENT.Version = 0)
I have two tables. Customers and Orders. Customers table contains customer id, and customer name columns. Orders table contain order id,product id,customer id. So orders table contains products bought for each order by a customer.
I want to write a query to retrieve all order details (products for each order and customer id), where product with id 5 is bought. Can I write this sql without using a subquery.
Hi.I'm new in SqlServer programing.Is it possible to do something like this ( It is common construction in oracle )Select X from(select a+1 as X from tab1)without creating view containig subquery ?thx. MD
I have a table that has the following fields:tblECHECKID (autonumber identity column)PTIDBatchnumPageDataPointDPValueThere are many other tables on which I run a script that tests to makesure the data meets certain entry requirements (I don't want to putrestrictions on the forms because the data entry personel have toenter what is on the survey).Anything that fails is copied to the tblECHECK table. All the datacopied into the table are identified by a batchnumer (batchnum).I want to write a script that will look at all the records in thetblECHECK table, and delete any duplicates (the batch numbers are theonly fields that would be different).I have tried the following with no success (it returns 0 records):Select *From tblECHECKWhere batchnum ='batch8' and not exists (select * from tblECHECK wherebatchnum = 'batch7')Any help would be appreciated.
IF( NOT( EXISTS( SELECT 1 FROM updatetable WHERE ScriptName = 'MY_Scripts' ))) RAISERROR('script_dateupdates is required prior to running this script.', 30,1);
This is the Exists statment on the top. I was trying to find out what does it read. Can someone reat that for me.
Also why Not is before the brackets why it is not like If NOT EXISTS (Select....... ) acutally this is my concern to find out.
I have recently upgraded our v1.1 drivers for our software. What I found when running some 2 Tier Thick client testing is that its looking for the SDK/JRE's jsse.jar and jce.jar (for ssl and encryption respectively). Now I know this came about because of the new features added to the drivers. Its just that I cannot find any information on it anywhere. Just wondering if there is a way around having to bloat my classpath more by added these new dependencies on as well. It doesn happen for Servlet or 3 Tier as the container has all that set up on its CP already. Thanks in advance for any help/suggestions.
At present I have a logic implemented like below code example where I can get a combination of columns cid and ani in 'config' table to which I have to map columns cid and ani present in my 'current' table.
Now, a new column pid is added to both 'config' and 'current' tables, like
alter table config add pid integer alter table current add pid integer
Now I can have data in a combination of cid and ani (where pid can be NULL) or cid and pid (where ani can be NULL) or a row where all cid, ani and pid can have values. In this scenario, how should I make the changes in the current implementation given below?
create table current (cid integer, ani integer, resetdate datetime, threshold integer) create table config (cid integer, ani integer, resetdate datetime, threshold integer ) SELECT cid, ani from ( SELECT A.cid, A.ani, CASE WHEN C.cid is null then B.resetdate ELSE C.resetdate END AS resetdate,
select t1.a, (select t2.b from t2 where t1.c = t2.c) b from t1 I need to write that kind of sql to return me single value a and multiple values b on each of lines, like a b ---------------------------- tom small,big,hugh But if I execute that sql, I would get error like 'select Subquery returned more than 1 value'. Please help me find a solution, thanks!
1) Getting this error: Msg 156, Level 15, State 1, Procedure SP_ImportHotels_Step4, Line 10 Incorrect syntax near the keyword 'in'. Msg 102, Level 15, State 1, Procedure SP_ImportHotels_Step4, Line 10 Incorrect syntax near ','. Msg 512, Level 16, State 1, Procedure SP_ImportHotels_Step4, Line 7 Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression. The statement has been terminated. 2) I also coild not use subquery as myname... It did not allow to specify "as" for some reason... Here is my procedure.
alter procedure SP_ImportHotels_Step4 As BEGIN INSERT INTO classifieds_Photos ( AdId, IsMainPreview, DateCreated, createdOn, createdBy, modifiedOn, modifiedBy, URL, ThumbnailURL ) SELECT classifieds_Ads.Id, [i.ID] in (Select Min(Images.ID) from Images group by HotelID), GetDate() AS Expr2, GetDate() AS Expr3, 'admin' AS Expr4, GetDate() AS Expr5, GetDate() AS Expr6, i.URL, i.ThumbnailURLFROM (classifieds_Ads INNER JOIN classifieds_Address ON classifieds_Ads.LocationId = classifieds_Address.addressID) INNER JOIN Images as i ON classifieds_Address.tempIONHotelID = i.HotelID; END go execute SP_ImportHotels_Step4
hi all how are you today i am not good because i can't set this variable in this query please read this thanks
create table aaa1 (id_no int, name varchar(20)) go create table aaa2 (id_no int, name varchar(20)) go
insert into aaa1 values (1,'rahmi') insert into aaa1 values (2,'atilganer') insert into aaa1 values (3,'hasan')
insert into aaa2 values (4,'rahmi') insert into aaa2 values (5,'atilganer') insert into aaa2 values (6,'hasan')
/* declaring any numeric variable*/ declare @id_no_var int /* and set variable to max table name's (aaa2 table in this example) id_ no column
note :insqlhelp and insqlhelp2 is an alias for tablename query (recommended from sql help */
set @id_no_var = (select max(id_no) from (select max(name) insqlhelp from sysobjects where name like 'aa%') insqlhelp2 )
this query return:
Server: Msg 207, Level 16, State 3, Line 3 Invalid column name 'id_no'.
this error returned because max(id_no) column is absent
if you are run select * from (select max(name) insqlhelp from sysobjects where name like 'aa%') insqlhelp2
this query return that
insqlhelp --------------- aaa2
(1 row(s) affected)
this mean that my table name query returned table name but i cant use this name in any other query (i think because of daclaring alias "insqlhelp, insqlhelp2")
other way is
set to any other text variable to table name, and concetanate to query, and execute with exec
table1 member_name legacy_id team_name ----------------------------------------- Bill 1234 nationals Bill 1234 nationals Tom 3456 nationals Tom 3456 orioles
I wish I could restructure the data or normalize it but this is unfortunately what I have to deal with.
I need a query that returns the team name and the number of times it appears in the table excluding duplicates for each person. I have duplicates all over the place in this tables. Bill could have nationals listed a couple hundred times.
My query should return
team_name count ----------------- nationals 2 - because it occurs for bill, and tom orioles 1 - because it occurs for tom
If I do something like:
select distinct(team_name), count(team_name) from table1 group by team_name
I get back:
team_name count ------------------- nationals 3 - because it occurs for bill twice, and tom once orioles 1 - because it occurs for tom once
I've tried something like:
select team_name, count(team_name) from table1 where legacy_id in ( select distinct legacy_id from table1 )
I get a syntax error. Regardless, I'm not sure this will give me what I need.
I've tried over a dozen variations of select distinct, joins, etc but with no luck.
Any of you sql gurus know how to solve this problem? I've been banging my head against it for a couple days and boy does my head hurt.
SELECT Field1, (SELECT Field2 FROM Table2 WHERE Key=1) AS Field2 FROM Table1 SELECT Field1, (SELECT Count(Field2) FROM Table2 ) AS Field2 FROM Table1
But when I try execute it with SQL Server Everywhere it says "Token in error = SELECT".
Is there some kind of limitations to do this with SQL Everywhere? SQL Everywhere seems to be nice compared with Access and JET but for my project it's useless if I can't use subquerys.
So I declared this table data type , cool.. It works
CREATE TYPE BP_Data_XXX as table ( XXX_VALUE_NUMERIC numeric(19,2), bp_type VARCHAR(4), Dt datetime, ID int IDENTITY(1,1), MBP numeric(19,2), CONCEPT_ID VARCHAR(10) )
Now my question is later how do we check whether this type exists. The following does not work.
if( object_id('BP_Data_XXX') IS NOT NULL ) print 'IT is there '
I have a Select statement that was working just fine: string sSqlCmd = "INSERT INTO SiloKeywords (Silo_ID, Keyword, UserName) SELECT Silo_ID,'" + Keyword + "', '" + strUsername + "' FROM SiloNames WHERE Silo_Name = '" + Uri + "'"; the PM now wants to capture the internal User, which I have done but I am having real problems with the syntax if the internal User exists:
string sSqlCmd = "INSERT INTO SiloKeywords (Silo_ID, Keyword, UserName, IsInternal) SELECT Silo_ID,'" + Keyword + "', '" + strUserName + "', if EXISTS(SELECT * FROM InternalUsersList WHERE Alias = '" + arrResult[1] + "') 1 ELSE 0 FROM SiloNames WHERE Silo_Name = '" + Uri + "'"; I am completely lost here.
I tried this:IF Exists (Select 1 from TESTA..POP10100 where PONumber=@PONumber) and(Select 1 from TESTA..POP30300 where POPType<>2 andPOPRCTNM=@ReceiptNo)BEGINENDIt says Incorrect Syntax error? I tried to cover the whole thing withan extra parantheses. It wont work eitherWhat am i doing wrongThanks, Girish