Select query results based on condition

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Select query results based on condition

Author

Topic

cplusplus
Aged Yak Warrior

567 Posts

Posted - 2011-10-05 : 12:24:54

I have the following, i am trying to use this within a UDF function,
I noticed while creating UDf the delete is not getting accepted within udf, is there a way to present those rows in a select that way i can insert to a temptable variable within udf.

when i execute this with, i am getting 6 rows as result. Is it possible to get the same 6 rows using select instead of delete.

I am trying to use select instead of delete getting error messages.

Declare @Sample table (taskid int, Pct int, PctCompleteDate datetime, QtrNum int, MonNum Int, YearNum int)
insert @Sample
select 168, 10,'24-Jun-2011', 2, 6, 2011 union all
select 168, 10,'30-Jun-2011', 2, 6, 2011 union all
select 168, 20,'25-Jul-2011', 3, 7, 2011 union all
select 168, 29,'26-Sep-2011', 3, 8, 2011 union all
select 169, 10,'24-Jun-2011', 2, 6, 2011 union all
select 169, 29,'26-Aug-2011', 3, 8, 2011 union all
select 169, 20,'25-Jul-2011', 3, 7, 2011 union all
select 170, 20,'25-Jul-2011', 3, 7, 2011 union all
select 170, 10,'24-Jun-2011', 2, 6, 2011 union all
select 170, 29,'26-Aug-2011', 3, 8, 2011

delete s from (
select taskid
,Pctcompletedate
,QtrNum
,YearNum
,rn = row_number() over (partition by taskid,QtrNum,yearnum
order by Pctcompletedate desc)
from @sample
) d
join @sample s
on s.yearnum = d.yearnum
and s.QtrNum = d.QtrNum
and s.Pctcompletedate = d.Pctcompletedate
and s.taskid = d.taskid
where d.rn > 1

select * from @sample

thank you very much for the helpful info.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-05 : 12:32:39

why you need join? isnt below enough?


delete d from (
select rn = row_number() over (partition by taskid,QtrNum,yearnum 
order by Pctcompletedate desc)
from @sample
) d
where rn>1

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

cplusplus
Aged Yak Warrior

567 Posts

Posted - 2011-10-05 : 12:39:28

Visakh, It worked. but can i use the same condition with select instead of delete.

and get same 6 rows as result, i am having problem within the UDF when i use the delete getting error message saying side effect invalid use of delete.

if i can get the same 6 rows using select then i can insert all 6 rows to a table variable within udf.

-------------


Declare @Sample table (taskid int, Pct int, PctCompleteDate datetime, QtrNum int, MonNum Int, YearNum int)
insert @Sample
select 168, 10,'24-Jun-2011', 2, 6, 2011 union all
select 168, 10,'30-Jun-2011', 2, 6, 2011 union all
select 168, 20,'25-Jul-2011', 3, 7, 2011 union all
select 168, 29,'26-Sep-2011', 3, 8, 2011 union all
select 169, 10,'24-Jun-2011', 2, 6, 2011 union all
select 169, 29,'26-Aug-2011', 3, 8, 2011 union all
select 169, 20,'25-Jul-2011', 3, 7, 2011 union all
select 170, 20,'25-Jul-2011', 3, 7, 2011 union all
select 170, 10,'24-Jun-2011', 2, 6, 2011 union all
select 170, 29,'26-Aug-2011', 3, 8, 2011

delete d from (
select rn = row_number() over (partition by taskid,QtrNum,yearnum 
order by Pctcompletedate desc)
from @sample
) d
where rn>1

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-05 : 12:43:42

you cant use delete in udf. you should be doing it in procedure instead

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

cplusplus
Aged Yak Warrior

567 Posts

Posted - 2011-10-05 : 13:03:49

Visakh,
From my Sp i am calling this UDF for every row, to get earned value.

i tried to call external Sp via UDf , but still am having issues with insert statements to external tables. again having the same issue
"Invalid use of a side-effecting operator 'INSERT' within a function."

if there a way to use the instead of delete concept have the rows show with select.

delete d from (
select rn = row_number() over (partition by taskid,QtrNum,yearnum
order by Pctcompletedate desc)
from @sample
) d
where rn>1

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-05 : 13:06:13

you cant perform any DML operation inside UDF. You should be using procedure for all these.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources