question

sand143eep avatar image
sand143eep asked

logic required

hello all, i am new to development. i had a table where columns we have are idoc no,status,process date time. here the idoc number 1231 in the below table is having status error on 16th 20th and 23rd but it got success state on 25th so i need the idoc numbers which are having errors earlier and succeded finally so that i can delete idocno 1231 with error status. idoc no status process date time 1231 error 16/3/2013 1234 error 16/3/2013 1674 error 15/4/2013 1231 error 20/4/2013 1231 error 23/4/2013 1234 error 16/3/2013 1234 error 17/3/2013 1234 success 25/5/2013 1231 success 25/5/2013 delete from idocstatus where idocNO in( select idocNO, status, accessdatetime from (select idocNO, status, accessdatetime, row_number() over(partition by idocNO order by accessdatetime desc) as rn from idocstatus) as T where rn = 1 and status = 'error') i am using this query but i am getting the error : Msg 116, Level 16, State 1, Line 1 Only one expression can be specified in the select list when the subquery is not introduced with EXISTS
sql-server-2008sql-server-2005
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

sqlaj 1 avatar image
sqlaj 1 answered
How about something like this? Check and see if it returns the results expected then change it to a delete statement. SELECT t1.idoc, t1.status FROM [dbo].[testing] t1 Join dbo.testing t2 on t1.idoc = t2.idoc WHERE t1.status = 'error' AND t2.status = 'success'
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

sand143eep avatar image sand143eep commented ·
select statement worked but when i tried to delete with this: delete from idocstatus where idocno in( select t1.idocno,t1.status,t1.accessdatetime from dbo.idocstatus t1 join dbo.idocstatus t2 on t1.idocno = t2.idocno where t1.status = 'error' and t2.status = 'success') getting the error: Msg 116, Level 16, State 1, Line 1 Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.
0 Likes 0 ·
ruancra avatar image
ruancra answered
Select a.IdocNo ,a.ProcessDateTime ,a.Status into #Error from TableName a where a.Status = 'Error' and a.ProcessDateTime in (Select max(ProcessDateTime) from TableName b where a.IdocNo = b.IdocNo and b.Status = 'Error') Select a.IdocNo ,a.ProcessDateTime ,a.Status into #Succeed from TableName a where a.Status = 'Success' and a.ProcessDateTime in (Select max(ProcessDateTime) from TableName b where a.IdocNo = b.IdocNo and b.Status = 'Success') order by a.IdocNo Select a.* into #Delete from #Succeed a join #Error b on b.IdocNo = a.IdocNo DELETE FROM TableName WHERE Status = 'Error' and IdocNo in (SELECT IdocNo FROM #Delete)
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

ruancra avatar image
ruancra answered
This can also work: DELETE FROM YourTable WHERE IdocNo in (SELECT distinct IdocNo FROM Yourtable where Status = "SUCCESS") AND Status = "ERROR"
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.