Subquery Returned More Than 1 Value?
Apr 15, 2008
Hi
I have two tables and I need write a query that include subquery that return more than 1 value,Can you give me a idea how to write the query
The table with data is like this,one file maybe belong to many group.
Filename FilegroupID
file1 1
file2 2
file1 3
file1 4
file3 1
file2 5
I need write a query to get the result like this
file1 1,3,4
file2 2,5
file3 1
I try to write a query with subquery but I get error message
"Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression"
I use SQL Server 2005.but I need the subquery return data like "1,3,4".
Please help me.
Thanks
Mark
View 7 Replies
ADVERTISEMENT
Apr 26, 2008
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...
View 1 Replies
View Related
Jul 20, 2005
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
View 3 Replies
View Related
Jul 19, 2007
Hi guys,
A have a problem that I need understand.... I have 2 server with the same configuration...
SERVER01:
-----------------------------------------------------
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
********************************
SERVER02:
-----------------------------------------------------
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.
Any idea WHY?
SELECT dbo.opf_saldo_ctb_opc_flx.dt_saldo,
dbo.opf_saldo_ctb_opc_flx.cd_indice_opf,
dbo.opf_saldo_ctb_opc_flx.cd_classificacao,
dbo.opf_movimento_operacao.ds_tipo_transacao ds_tipo_transacao_movimento ,
dbo.opf_header_operacao.ds_tipo_transacao ds_tipo_transacao_header,
'SD' ds_status_operacao,
dbo.opf_header_operacao.ds_tipo_opcao ,
dbo.opf_header_operacao.id_empresa,
dbo.opf_saldo_ctb_opc_flx.ic_empresa_cliente,
0 vl_entrada_compra_ctro ,0 vl_entrada_compra_premio,
0 vl_entrada_venda_ctro , 0 vl_entrada_venda_premio,
0 vl_saida_compra_ctro, 0 vl_saida_compra_premio,
0 vl_saida_venda_ctro, 0 vl_saida_venda_premio,
0 vl_lucro , 0 vl_prejuizo, 0 vl_naoexec_contrato,
0 vl_naoexec_premio,
sum(dbo.opf_saldo_ctb_opc_flx.vl_aprop_ganho) vl_aprop_ganho,
sum(dbo.opf_saldo_ctb_opc_flx.vl_aprop_perda) vl_aprop_perda,
sum(dbo.opf_saldo_ctb_opc_flx.vl_rever_ganho) vl_rever_ganho,
sum(dbo.opf_saldo_ctb_opc_flx.vl_rever_perda) vl_rever_perda,
sum(dbo.opf_saldo_ctb_opc_flx.vl_irrf) vl_irrf
FROM dbo.opf_saldo_ctb_opc_flx,
dbo.opf_header_operacao ,
dbo.opf_movimento_operacao
WHERE dbo.opf_saldo_ctb_opc_flx.dt_saldo = '6-29-2007 0:0:0.000'
and ( dbo.opf_header_operacao.no_contrato = dbo.opf_saldo_ctb_opc_flx.no_contrato )
and ( dbo.opf_header_operacao.no_contrato = dbo.opf_movimento_operacao.no_contrato )
and ( dbo.opf_movimento_operacao.dt_pregao = (select (o.dt_pregao) from dbo.opf_movimento_operacao o
where o.no_contrato = dbo.opf_movimento_operacao.no_contrato and o.dt_pregao <='6-28-2007 0:0:0.000' ) )
and (dbo.opf_saldo_ctb_opc_flx.ic_tipo_saldo = 'S')
group by dbo.opf_saldo_ctb_opc_flx.dt_saldo,
dbo.opf_saldo_ctb_opc_flx.cd_indice_opf,
dbo.opf_saldo_ctb_opc_flx.cd_classificacao,
dbo.opf_movimento_operacao.ds_tipo_transacao,
dbo.opf_header_operacao.ds_tipo_transacao ,
ds_status_operacao,
dbo.opf_header_operacao.ds_tipo_opcao ,
dbo.opf_header_operacao.id_empresa,
dbo.opf_saldo_ctb_opc_flx.ic_empresa_cliente
Thanks
Nilton Pinheiro
View 9 Replies
View Related
Mar 6, 2008
I am getting an error as
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.
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
WHERE EmployeeDetails.Empid=1
PLEASE HELP.................
View 1 Replies
View Related
May 14, 2008
Hi,
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')
Thanks
Jacquues
View 4 Replies
View Related
Mar 9, 2006
can anyone tell me why i get the following error when i run the below query? its a simple query. i dont understand why its throwing an error. Thanks in advance.
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.
update purchaseorders set orderstatusID = 4 where purchaseorderid in (
select distinct postprocesspurchaseorderID from wishlistitems where postprocesspurchaseorderID is not null
)
View 1 Replies
View Related
Dec 7, 2011
I am getting the below error while trying to run sql query
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 in the same below query if i put the parameter in upto 10 then its working fine......if the parameter is 11 or above then the query is giving the above error.
SELECT substring(sc.container_id,0,14) container_id,
imc.item_class,
sh.ship_to, sh.shipment_id,
cust.ADDRESS1,
sh.carrier,
[code]...
View 3 Replies
View Related
Mar 25, 2015
i am trying to run Select query to get "application name" and "role" for each user in the DB problem is that each user has more than one App that he is useing and for each App he can have more than one role
My code is:
Code:
select top 100
username,
(Select b.name
from application b
inner join userroleapplication c
on b.applicationID = c.applicationID ) Application,
(select name from role d, userroleapplication gr
where gr.roleID = d.roleID and gr.userID = a.userID) role
from users a
where username in
(
'username'
)
i know where is the problem but i cant figure how to fix this
What i need as a output
Username | Application | Role
"user1" | "App1" | "Role1"
"user1" | "App1" | "Role2"
"user1" | "App2" | "Role1"
"user1" | "App3" | "Role1"
"user2" | "App1" | "Role1"
"user2" | "App3" | "Role1"
etc.
View 2 Replies
View Related
Dec 18, 2013
I have a simple update query that looks like this:
Update Table1 set midpointDate = (select dateadd(day,(datediff(day,startDate,endDate)/2),starteDate) from Table1) where Table1.RowID = Table1.RowID
I am receiving 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."Yes, it is all contained in the same table.
View 5 Replies
View Related
Jan 26, 2008
Okay,
I want to fill a table in one database with some info from an identical table from another database. When I run this query, I get 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.
The statement has been terminated."
But the point of the subquery was to in fact return more than one value.
View 2 Replies
View Related
Aug 28, 2007
Okay, this is what I have as my store procedure:
DECLARE @parentPage varchar(250)
-- Insert statements for procedure here
SET @parentPage = (SELECT [frntPage] FROM [OLissue], [Outlook] WHERE ([Outlook].[issueID] = @OLissueID AND [Outlook].[issueID] = [OLissue].[issueID] AND [OLissue].[frntPage] IS NOT NULL AND [Outlook].[parent] IS NOT NULL))
IF @parentPage IS NOT NULL
IF exists (SELECT [Outlook].[ID], [Outlook].[title], [Outlook].[name], [Outlook].[issueID] FROM [OLissue], [Outlook] WHERE ([Outlook].[issueID] = @OLissueID AND [Outlook].[issueID] = [OLissue].[issueID]))
SELECT [ID], [title], [name], [issueID] FROM [Outlook] WHERE ([issueID] = @OLissueID)
ELSE
SELECT DISTINCT NULL ID, 'No parent page yet' title FROM [Outlook]
And I kept getting this error when testing in SQL Management Studio.
Msg 512, Level 16, State 1, Procedure parentPage, Line 19
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
View 1 Replies
View Related
Apr 9, 2008
I'm having problem Inserting data from 1table into another table. I'm getting the error:
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
I get the reason why I'm having this error message but I don't know how to fix code... Help please? Thanks
Here's an example of what I'm trying to do...
table1 has:
ID | Name | countryID
1 | Joe | 101
2 | Ben | 102
3 | Tom | 102
Now I wrote this code to insert some of the data from table 1 into table2...
insert into Table2(CompName, ID) values('Information',(select ID from table1 where countryid=102))
Please Help! Thanks...
View 5 Replies
View Related
Dec 24, 2007
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!
View 4 Replies
View Related
Sep 9, 2004
Hi i am a newbie in sql so any help would be appreciated. i have a serverlog table that initially had one server and different functions types, my sql query got a percentage of the execution times for the different functions and all worked well, however as soon as i put more servers in to the table i got 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."
Here is my SQL, the problem is with the sub queries returning more than one server type... Hope somebody can help and thanks in advance..
SELECT server, [function], prelimquery * 100 AS [%OverallProcessingTime]
FROM (SELECT server, [function], subquerya /
(SELECT subqueryb
FROM (SELECT totalallreqexec *
(SELECT AVG([exec_time])
FROM llserverlogs) AS subqueryb
FROM (SELECT COUNT(*) AS totalallreqexec
FROM llserverlogs) DERIVEDTBL) DERIVEDTBL) AS prelimquery
FROM (SELECT server, [function], NoRequestExecutions *
(SELECT avgexectime
FROM (SELECT DISTINCT server, AVG(exec_time) AS avgexectime
FROM llserverlogs
GROUP BY server) DERIVEDTBL) AS subquerya
FROM (SELECT DISTINCT server, [function], COUNT(*) AS NoRequestExecutions
FROM llserverlogs
GROUP BY server, [function]) DERIVEDTBL) DERIVEDTBL) DERIVEDTBL
View 2 Replies
View Related
Aug 6, 2004
when I run the query
UPDATE dbhal.dbo.tblUser
SET vchrFirstName = FirstName,
vchrLastName = LastName,
vchrLogin = LoginName,
bitActive = Active,
vchrEmailAddress = EmailAddress
FROM TempEmployee, tbluser where EmployeeID = intEmp
and vchrcompid = 'sam'
and there is trigger
CREATE TRIGGER UPDATE_tblUser
ON tblUser
FOR UPDATE AS
DECLARE
@CompanyId as varchar(20),
@intUid as int
SET @CompanyId =(Select vchrCompID from deleted)
Set @intUid = (Select intUid from deleted)
IF @CompanyId = 'sam'
BEGIN
UPDATE [dbPortal].[dbo].tblUser
SET
intEmp = (Select intEmp from inserted),
vchrCompID = (Select vchrCompID from inserted),
vchrPwd = (Select vchrPwd from inserted),
vchrLogin = (Select vchrLogin from inserted),
vchrFirstName = (Select vchrFirstName from inserted) ,
vchrLastName = (Select vchrLastName from inserted),
vchrEmailAddress = (Select vchrEmailAddress from inserted),
bitActive = (Select bitActive from inserted),
intPWDAttempt = (Select intPWDAttempt from inserted),
vchrCreatedWho = (Select vchrCreatedWho from inserted),
dtmCreatedDate = (Select dtmCreatedDate from inserted),
vchrModifiedWho = (Select vchrModifiedWho from inserted),
dtmModifiedDate = (Select dtmModifiedDate from inserted)
WHERE
intUID = @intUid
END
I get the error.
What can I do to get this to run?
View 9 Replies
View Related
Nov 14, 2006
I'm getting the message
"Subquery returned more than 1 value." etc when running a query on a
linked server through OpenQuery. This returns around 6 values
(some of them are fairly long varchar's).
When running another query the same way which returns 2 values,
it works fine.
The problem query also works fine when run locally. It's calling a User-Defined Functions which returns a table, but the second query which works fine also does this.
Where can this subquery be ? Inside the UDF (shouldn't matter, should it?) ?
View 6 Replies
View Related
Jan 23, 2013
Am getting error:- Subquery returned more than one value,that is not permitted when sub query follows
=,<,>.................................Below is my query
declare @WorkOrder as nvarchar(10)
Set @WorkOrder =( Select X.PrjCode from OPRJ X Where X.PrjCode='[%1]')
SELECT L.Project ,l.U_PINo as [Indent No], M.DocNum AS 'GRN No', M.DocDate as 'Date',
M.CardName as 'Vendor Name',L.ItemCode,l.Dscription,l.Quantity,l.unitMsr as'UOM',l.Price,l.DiscPrcnt ,L.LineTotal,
isnull((SELECT (TaxSum) FROM PDN4 where statype=-90 and DocEntry=M.DocEntry and L.LineNum =PDN4.LineNum),0) +
[Code] .....
View 1 Replies
View Related
Jun 5, 2006
Hi group
I get the above error in my SP when the tblHolidayDates has more than one record. Here is my SP:
begin
if datediff(day,getdate()-10 ,(Select HolidayDate from tblHolidayDates))= 3 AND
DATEPART(dw,(select holidaydate from tblHolidayDates)) = 1 -- Monday Date
SELECT su.SuspensionID, su.ConsentNo, si.SiteID,
si.NameNo + ' ' + si.Address1 AS SiteAddress, si.SiteDesc,
si.SuspensionStart, si.SuspensionEnd, si.Address1, si.BaysAttached,
rd.RefValue AS NoticeDays, si.MergeID
FROM Suspension su
INNER JOIN SiteData si ON su.SuspensionID = si.SuspensionID
INNER JOIN RefData rd ON si.NotReq = rd.RefCode
WHERE su.Status = 'ACT'
and si.Archived = 'N'
and (datediff(day, getdate()-10 , si.SuspensionStart) = rd.RefValue
OR (datediff(day, (Select holidaydate from tblHolidayDates), si.SuspensionStart)= rd.RefValue + 2)
or datediff(day,(Select holidaydate from tblHolidayDates), si.SuspensionStart)=rd.RefValue + 1)
end
Any pointers much appreciated.
View 11 Replies
View Related
Jun 26, 2007
I have some code that calls a stored procedure on SQL Server 2005 using the Microsoft JDBC driver 1.1. The code normally works however, every once in a while an exception is thrown:
Code Snippet
com.microsoft.sqlserver.jdbc.SQLServerException: Subquery returned more than 1 value. This is not permitted when the subquery follows =, !, <,<=, >, >=, or when the subquery is used as an expression.
Generally, this has been resolved by restarting SQL Server 2005, but why is it showing up to being with?
Stored Procedure:
Code Snippet
ALTER Procedure [dbo].[addRecord]
@userID int,
@itemID int,
@info varchar(50),
@comment varchar(50),
@output int output
AS
Declare @dateSubmitted datetime
set @dateSubmitted = getDate();
--Insert the new record. THIS TABLE has an ID identity Primary Key
--column that auto-increments.
insert into RecordTable
(UserPerson, Information, DateSubmitted)
values (@userID, @info, @dateSubmitted);
---Get the ID Assigned in the record table. The Item table
---has a Foreign key on this column.
Declare @assignedID int
set @assignedID = (select ID from RecordTable where DateSubmitted = @dateSubmitted)
/**Set the output parameter.*/
set @output = @assignedID;
--Now update the Item Table.
insert into Item
(ID, RecordID, Comment)
values (@item,@assignedID, @comment);
View 8 Replies
View Related
Jun 7, 2001
Any help would be appreciated.
Thanks.
Steve
The error below is encounter when a mass update is attempted on our employee table.
Server: Msg 512, Level 16, State 1, Procedure emp_lock_id, Line 8
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.
The Trigger code is below
CREATE TRIGGER emp_lock_id ON dbo.EMPLOYEE
FOR UPDATE
AS
DECLARE @NEXT_NUM DECIMAL(17, 12)
DECLARE @NEW_NUM DECIMAL(17, 9)
DECLARE @OLD_LOCK_ID DECIMAL
DECLARE @NEW_LOCK_ID DECIMAL
SET @OLD_LOCK_ID = (SELECT EMP_LOCK_ID FROM DELETED)
SET @NEW_LOCK_ID = (SELECT EMP_LOCK_ID FROM INSERTED)
IF @OLD_LOCK_ID <> @NEW_LOCK_ID
BEGIN
RAISERROR('Record Modified by different user.', 16, 1)
ROLLBACK TRANSACTION
END
ELSE
BEGIN
SET @NEXT_NUM = (convert(decimal(17,12), getdate()))
SET @NEW_NUM = @NEXT_NUM * 1000
UPDATE EMPLOYEE SET EMP_LOCK_ID = (@NEW_NUM - floor(@NEW_NUM)) * 1000000000
FROM EMPLOYEE, INSERTED
WHERE EMPLOYEE.EMP_EMPLOYEE_ID = INSERTED.EMP_EMPLOYEE_ID
END
View 6 Replies
View Related
Sep 27, 2004
Hi guys,
I am just trying to do a simple update statement and am getting this error
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.
My statment is like this:
Code:
update [Clients]
SET [OPT-OUT] = 0 WHERE [OPT-OUT] is null
I have used this against another table with success, but get the error against that one. Anyone have any idea?
View 4 Replies
View Related
Oct 3, 2007
I am struggling with syntax for an update statement.
Goal: If a RelationID has invoice history then HasHistory=1
UPDATE [Tbl_Relations]
SET [HasHistory]= 1
FROM [Tbl_Relations] r INNER JOIN
(SELECT RelationID
FROM vw_Invoice_history
GROUP BY RelationID
) inv ON r.RelationID= inv.RelationID
Result: Subquery returned more than 1 value
What am I doing wrong?
Thanks in advance,
Frans
View 9 Replies
View Related
Feb 22, 2008
Hi,
I am using .NET sqlBulkCopy to insert several rows at once into my sql table. Yesterday I created a simple trigger on this table to take the primary key of the inserted row and place into another table. Fairly straightforward I thought, but I keep getting this error when inserting 2 or more rows (works fine with one row):
"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."
To test this, I changed the sqlBulkCopy into individual INSERT statements in a for each loop and then it works perfectly, so I know that it's definitely the sqlBulkCopy causing the problem, not the trigger itself. Here is my trigger code anyway:
alter trigger tg_InsertClaim on AuditItemfor insertas
declare @AuditItemID as intset @AuditItemID = (select i.AuditItemID from inserted i inner join dbo.AuditItem a ON i.AuditItemID = a.AuditItemID)
insert into Claim (AuditItemID) VALUES (@AuditItemID)
Thanks!
View 2 Replies
View Related
Aug 27, 2015
I am attempting to Insert multiple rows in a table by passing DataTable to a Stored Proc. My code is the same as the examples on the net, however when I execute the code I receive the Subquery returned more than 1 value error.
I first created up a User-Defined Table Type
My stored proc is as follows:
ALTER PROCEDURE [KCC].[Insert_MaterialJobMtl]
@MtlTblMaterialType READONLY
AS
BEGIN
Set NOCOUNT ON;
[code]....
View 9 Replies
View Related
Sep 20, 2007
Please try the following, see that trigger is getting invoked, even if there is no row inserted into trigger table.
drop TABLE X
GO
CREATE TABLE X (
x1 bigint NOT NULL ,
x2 nvarchar(40)
)
GO
drop TABLE Y
CREATE TABLE Y (
y1 bigint NOT NULL ,
y2 nvarchar(40)
)
GO
CREATE TRIGGER trg1
ON X
FOR INSERT AS
BEGIN
DECLARE
@prfirststatus INTEGER,
@newcontextid NCHAR
SELECT @newcontextid = x2,
@prfirststatus = x1
FROM inserted
PRINT 'See the trigger getting invoked without even a single ' +
' row being inserted in table X and values passed to this ' +
' triggers are inserted.x2 = ' + @newcontextid
insert into Y values (@prfirststatus,@newcontextid)
END
GO
insert into X SELECT y1, 'x' from Y where y2 = 'DOESNTEXIST'
View 2 Replies
View Related
Mar 25, 2008
Hi,
I would like to know how i can handle multiple columns returned by a subquery via IN clause in case of sql server 2005. I can do that in oracle by using the following statement:
DELETE FROM TEST1
WHERE (ID, ID1) NOT IN (SELECT ID,ID1 FROM TEST2);
Thanks and Regards
Salil
View 9 Replies
View Related
Apr 9, 2007
I have two tables one is riders: riderid, firstname, lastname, addressid
The other is address: addressid, address1, city, state...and foolishly riderid
Originally, I saved the riderid as a FK in the address table, not the addressid in the ridertable. Not the worst thing, but now I want to use the address table for more than just riders. So, I should be storing the addressid in the riders table, not the riderd in the address. The address ID in the riders table is all NULLS. I thought that I could do a quick UPDATE to fix this...
update riders SET address =
( SELECT addressid FROM address WHERE address.riderID = riders.riderid )
But this returns the error:
Msg 512, Level 16, State 1, Line 1
Subquery returned more than 1 value. This is not permitted .... and more...
I can see where the query rerturns more than 1 row, how do you tie that to individual riders?
View 2 Replies
View Related
Jun 20, 2006
I have a strange problem. I have some code that executes a sql query. If I run the query in SQL server query analyzer, I get a set of data returned for me as expected. This is the query listed on lines 3 and 4. I just manually type it into query analyzer.
Yet when I run the same query in my code, the result set is slightly different because it is missing some data. I am confused as to what is going on here. Basically to examine the sql result set returned, I write it out to an XML file. (See line 16).
Why the data returned is different, I have no idea. Also writing it out to an XML file is the only way I can look at the data. Otherwise looking at it in the debugger is impossible, with the hundreds of tree nodes returned.
If someone is able to help me figure this out, I would appreciate it.
1. public DataSet GetMarketList(string region, string marketRegion)2. {3. string sql = @"SELECT a.RealEstMarket FROM MarketMap a, RegionMap b " + 4."WHERE a.RegionCode = b.RegionCode"; 5. DataSet dsMarketList = new DataSet();6. SqlConnection sqlConn = new SqlConnection(intranetConnStr); 7. SqlCommand cmd = new SqlCommand(sql,sqlConn);8. sqlConn.Open();9. SqlDataAdapter adapter = new SqlDataAdapter(cmd); 10. try11. {12. adapter.Fill(dsMarketList);
13. String bling = adapter.SelectCommand.CommandText;//BRG 14. dsMarketList.DataSetName="RegionMarket"; 15. dsMarketList.Tables[0].TableName = "MarketList"; 16. dsMarketList.WriteXml(Server.MapPath ("myXMLFile.xml" )); // The data written to 17. myXMLFile.xml is not the same data that is returned when I run the query on line 3&4 18. // from the SQL query 19. } 20. catch(Exception e) 21. { 22. // Handle the exception (Code not shown)
View 2 Replies
View Related
Jul 6, 2014
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.
View 6 Replies
View Related
Jan 9, 2007
Stored Procedure:CREATE PROCEDURE [dbo].[GetBanner] @BannerPage nvarchar(50), @MagazineID intASBEGIN SET NOCOUNT ON; DECLARE @BannerID int SELECT TOP 1 @BannerID = BannerID FROM Banners WHERE BannerPage=@BannerPage AND MagazineID = @MagazineID AND StartDate <= GetDate() AND EndDate >=GetDate() ORDER BY Views SELECT BannerID, BannerFileName, AltText, URL, Views FROM Banners WHERE BannerID = @BannerID UPDATE Banners SET Views = Views +1 WHERE BannerID = @BannerID UPDATE BannerStats SET Views = Views + 1 WHERE BannerID = @BannerIDEND OnSelected method: protected void dsBanner_Selected(object sender, SqlDataSourceStatusEventArgs e) { int RecordCount = e.AffectedRows; Response.Write(RecordCount); } This ALWAYS gives me 0 - despite the fact that the correct banner is pulled from the database, and updated etc. Also, when I execute the procedure (for valid parameter values) in SSMS, I get "1 row(s) Affected". Can anyone tell me what I need to change to get the proper value? Thanks
View 6 Replies
View Related
Sep 27, 2007
I have the following sql:SELECT courseModuleCode FROM courses WHERE courseCode like '%' + @prefixText + '%'UNION
SELECT courseName FROM courses WHERE courseName like '%' + @prefixText + '%'
UNIONSELECT TeacherName FROM courses WHERE TeacherName like '%' + @prefixText + '%' it returns one column, i want to name tha column as "words"how do i do it?thanks
View 3 Replies
View Related
Sep 10, 2004
I have a table that COULD be linked to 3 other tables, but if one of the link fields is zero I get nothing back at all. Here is my query:
SELECTEvents.*, Agencies.A_NAME AS A_NAME,
(Families.F_FNAME + ' ' + Families.F_LNAME) AS F_NAME,
(Candidates.C_FNAME + ' ' + Candidates.C_LNAME) AS C_NAME
FROM Events
INNER JOIN Agencies ON Events.E_AGENCY_ID = Agencies.A_ID
INNER JOIN Families ON Events.E_FAMILY_ID = Families.F_ID
INNER JOIN Candidates ON Events.E_CANDIDATE_ID = Candidates.C_ID
WHEREE_ID = 89
Even if all the inner join id's are zero, I would at least like to get back the event information.
Could anyone help me out here, or do I have to do 4 queries?
Thanks in advance.
View 2 Replies
View Related
