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
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 RelatedI 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 RelatedI am writing a stored procedure that needs a access individual entries in a column with multiple entries delimited by a comma(yeah i know, not 1st NF) . Like this:
Key
NotANormalizedCol
1
1324, 5124, 5435,5467
2
423, 23, 5345
3
52334, 53443, 1224
4
12, 4, 1243,66
is there a function that returns a substring given a delimiter character? the only substring returning function that i found are the LEFT and RIGHT that returns fixed length substring.
I am pretty new to this, so I apologize if this is a trivial questions
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
I need to return a single column (Name.company) but I want the data returned to be viewed in multiple columns.
First is that possible? Second if so how?
My data looks like this:
University X
University X - 1
University X - 2
University X - 3
College X
College X - 1
College X - 2
College X - 3
Cmty College X
Cmty College X - 1
Cmty College X - 2
etc.
How can I get it to return only:
University X
College X
Cmty College X
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.................
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
Right now I have to do something like this and it is time consuming every time I have to query a specific table...
SELECT lots_of_columns
FROM table
WHERE (column5 = '1' OR column6 = '1' OR column7 = '1' OR column8 = '1' OR column9 = '1' OR column10 = '1' OR column11 = '1' OR column12 = '1')
AND other_query_critiera_here
Typing out the OR statement gets long, time consuming and prone to errors because that first where line with all the ORs can sometimes have 20+ ORs in it. As some insight, the columns are text columns, sometimes they have data, sometimes they are NULL. Sometimes they have the same data (i.e., column5 and column6 and column12 could both have '1' as values).
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
)
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]...
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.
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
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.
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.
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.
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...
We are displaying the report in our reporting application but we do not want to display errors from SSRS to the user. We want to handle the errors and display a user friendly message.
How can that be done?. We are making URL access to the report server.
Thanks.
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!
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
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?
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?) ?
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] .....
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.
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);
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
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?
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
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!
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]....
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'
Hey, I'm referencing a database about a fictitious IT company, and I'm trying to answer the following statement:
Determine who has logged the most hours. Create a SELECT statement to return their name and the max hours they have worked. NOTE: This is a tougher one but looking into using a Subquery and use the HAVING statement.
I've written this so far, and although it does give me the right answer (there is only one employee in the DB who has worked on projects) it is not entirely correct. Basically there is no subquery, and it doesn't really show who has worked the max hours:
SELECT
EMPLOYEE_NAME 'Employee Name', SUM(HOURS_LOGGED) 'Maximum Hours Logged'
FROM
PROJECT_EMPLOYEE PE
INNER JOIN
PROJECT_HOURS PH ON PH.EMPLOYEE_ID = PE.EMPLOYEE_ID
INNER JOIN
PROJECT P ON P.PROJECT_ID = PH.PROJECT_ID
GROUP BY
PE.EMPLOYEE_NAME
Here is the DB if needed:
CREATE TABLE project_employee (
employee_id INT,
employee_name VARCHAR(40),
employee_hire_date DATETIME,
employee_termination_date DATETIME,
employee_billing_rate INT,
CONSTRAINT project_employee_pk
PRIMARY KEY (employee_id)
);
CREATE TABLE project (
project_id INT,
project_name VARCHAR(40),
project_budget INT,
CONSTRAINT project_pk
PRIMARY KEY (project_id)
);
CREATE TABLE project_hours (
project_id INT,
employee_id INT,
time_log_date DATETIME,
hours_logged INT,
dollars_charged INT,
CONSTRAINT project_hours_pk
PRIMARY KEY (project_id, employee_id, time_log_date),
CONSTRAINT proj_hours_fkto_projectemployee
FOREIGN KEY (employee_id) REFERENCES project_employee,
CONSTRAINT proj_hours_fkto_project
FOREIGN KEY (project_id) REFERENCES project
);
INSERT INTO project
(project_id, project_name, project_budget)
VALUES (1001, 'Corporate Web Site',1912000);
INSERT INTO project
(project_id, project_name, project_budget)
VALUES (1002, 'Year 2000 Fixes',999998000);
INSERT INTO project
(project_id, project_name, project_budget)
VALUES (1003, 'Accounting System Implementation',897000);
INSERT INTO project
(project_id, project_name, project_budget)
VALUES (1004, 'Data Warehouse Maintenance',294000);
INSERT INTO project
(project_id, project_name, project_budget)
VALUES (1005, 'TCP/IP Implementation',415000);
INSERT INTO project_employee
(employee_id, employee_name, employee_hire_date,
employee_termination_date, employee_billing_rate)
VALUES (101, 'Jonathan Gennick','15-Nov-1961',null,169);
INSERT INTO project_employee
(employee_id, employee_name, employee_hire_date,
employee_termination_date, employee_billing_rate)
VALUES (102, 'Jenny Gennick','16-Sep-1964','5-May-1998',135);
INSERT INTO project_employee
(employee_id, employee_name, employee_hire_date,
employee_termination_date, employee_billing_rate)
VALUES (104, 'Jeff Gennick','29-Dec-1987','1-Apr-1998',99);
INSERT INTO project_employee
(employee_id, employee_name, employee_hire_date,
employee_termination_date, employee_billing_rate)
VALUES (105, 'Horace Walker','15-Jun-1998',null,121);
INSERT INTO project_employee
(employee_id, employee_name, employee_hire_date,
employee_termination_date, employee_billing_rate)
VALUES (107, 'Bohdan Khmelnytsky', '2-Jan-1998',null,45);
INSERT INTO project_employee
(employee_id, employee_name, employee_hire_date,
employee_termination_date, employee_billing_rate)
VALUES (108, 'Pavlo Chubynsky','1-Mar-1994','15-Nov-1998',220);
INSERT INTO project_employee
(employee_id, employee_name, employee_hire_date,
employee_termination_date, employee_billing_rate)
VALUES (110, 'Ivan Mazepa', '4-Apr-1998','30-Sep-1998',84);
INSERT INTO project_employee
(employee_id, employee_name, employee_hire_date,
employee_termination_date, employee_billing_rate)
VALUES (111, 'Taras Shevchenko', '23-Aug-1976',null,100);
INSERT INTO project_employee
(employee_id, employee_name, employee_hire_date,
employee_termination_date, employee_billing_rate)
VALUES (112, 'Hermon Goche', '15-Nov-1961','4-Apr-1998',70);
INSERT INTO project_employee
(employee_id, employee_name, employee_hire_date,
employee_termination_date, employee_billing_rate)
VALUES (113, 'Jacob Marley', '3-Mar-1998','31-Oct-1998',300);
INSERT INTO project_hours (project_id, employee_id, time_log_date, hours_logged, dollars_charged)
VALUES (1001,101,'01-JAN-1998',1.00,169.00);
INSERT INTO project_hours (project_id, employee_id, time_log_date, hours_logged, dollars_charged)
VALUES (1003,101,'01-JAN-1998',3.00,507.00);
INSERT INTO project_hours (project_id, employee_id, time_log_date, hours_logged, dollars_charged)
VALUES (1005,101,'01-JAN-1998',5.00,845.00);
INSERT INTO project_hours (project_id, employee_id, time_log_date, hours_logged, dollars_charged)
VALUES (1002,101,'01-FEB-1998',7.00,1183.00);
INSERT INTO project_hours (project_id, employee_id, time_log_date, hours_logged, dollars_charged)
VALUES (1004,101,'01-FEB-1998',1.00,169.00);
INSERT INTO project_hours (project_id, employee_id, time_log_date, hours_logged, dollars_charged)
VALUES (1001,101,'01-MAR-1998',3.00,507.00);
INSERT INTO project_hours (project_id, employee_id, time_log_date, hours_logged, dollars_charged)
VALUES (1003,101,'01-MAR-1998',5.00,845.00);
INSERT INTO project_hours (project_id, employee_id, time_log_date, hours_logged, dollars_charged)
VALUES (1005,101,'01-MAR-1998',7.00,1183.00);
INSERT INTO project_hours (project_id, employee_id, time_log_date, hours_logged, dollars_charged)
VALUES (1002,101,'01-APR-1998',1.00,169.00);
INSERT INTO project_hours (project_id, employee_id, time_log_date, hours_logged, dollars_charged)
VALUES (1004,101,'01-APR-1998',3.00,507.00);
INSERT INTO project_hours (project_id, employee_id, time_log_date, hours_logged, dollars_charged)
VALUES (1001,101,'01-MAY-1998',5.00,845.00);
INSERT INTO project_hours (project_id, employee_id, time_log_date, hours_logged, dollars_charged)
VALUES (1003,101,'01-MAY-1998',7.00,1183.00);
INSERT INTO project_hours (project_id, employee_id, time_log_date, hours_logged, dollars_charged)
VALUES (1005,101,'01-MAY-1998',1.00,169.00);
INSERT INTO project_hours (project_id, employee_id, time_log_date, hours_logged, dollars_charged)
VALUES (1002,101,'01-JUN-1998',3.00,507.00);
INSERT INTO project_hours (project_id, employee_id, time_log_date, hours_logged, dollars_charged)
VALUES (1004,101,'01-JUN-1998',5.00,845.00);
INSERT INTO project_hours (project_id, employee_id, time_log_date, hours_logged, dollars_charged)
VALUES (1001,101,'01-JUL-1998',7.00,1183.00);
INSERT INTO project_hours (project_id, employee_id, time_log_date, hours_logged, dollars_charged)
VALUES (1003,101,'01-JUL-1998',1.00,169.00);
INSERT INTO project_hours (project_id, employee_id, time_log_date, hours_logged, dollars_charged)
VALUES (1005,101,'01-JUL-1998',3.00,507.00);
INSERT INTO project_hours (project_id, employee_id, time_log_date, hours_logged, dollars_charged)
VALUES (1002,101,'01-AUG-1998',5.00,845.00);
INSERT INTO project_hours (project_id, employee_id, time_log_date, hours_logged, dollars_charged)
VALUES (1004,101,'01-AUG-1998',7.00,1183.00);
INSERT INTO project_hours (project_id, employee_id, time_log_date, hours_logged, dollars_charged)
VALUES (1001,101,'01-SEP-1998',1.00,169.00);
INSERT INTO project_hours (project_id, employee_id, time_log_date, hours_logged, dollars_charged)
VALUES (1003,101,'01-SEP-1998',3.00,507.00);
INSERT INTO project_hours (project_id, employee_id, time_log_date, hours_logged, dollars_charged)
VALUES (1005,101,'01-SEP-1998',5.00,845.00);
INSERT INTO project_hours (project_id, employee_id, time_log_date, hours_logged, dollars_charged)
VALUES (1002,101,'01-OCT-1998',7.00,1183.00);
INSERT INTO project_hours (project_id, employee_id, time_log_date, hours_logged, dollars_charged)
VALUES (1004,101,'01-OCT-1998',1.00,169.00);
INSERT INTO project_hours (project_id, employee_id, time_log_date, hours_logged, dollars_charged)
VALUES (1001,101,'01-NOV-1998',3.00,507.00);
INSERT INTO project_hours (project_id, employee_id, time_log_date, hours_logged, dollars_charged)
VALUES (1003,101,'01-NOV-1998',5.00,845.00);
INSERT INTO project_hours (project_id, employee_id, time_log_date, hours_logged, dollars_charged)
VALUES (1005,101,'01-NOV-1998',7.00,1183.00);
INSERT INTO project_hours (project_id, employee_id, time_log_date, hours_logged, dollars_charged)
VALUES (1002,101,'01-DEC-1998',1.00,169.00);
INSERT INTO project_hours (project_id, employee_id, time_log_date, hours_logged, dollars_charged)
VALUES (1004,101,'01-DEC-1998',3.00,507.00);
Hi,
Being ignorant I just tried to write a query with an IN clause that looked like this:
and cv.criteriaId in (select valueId from #locations)
SQL Server accepted this as valid and ran the query, returning no results.
However if I rewrite the query manually with the thirteen or so numbers that that query produces, like this:
and cv.valueID in (1001, 7600, 7601, 7602, 7603, 7604, ..etc..)
It runs fine and produces what I need.
How can I write my subquery so that the whole query produces the value set I'm expecting?
Cheers,
Matt