August 3, 2010 at 6:39 am
Hi all,
I have written a function that returns a table with one column. i need to use the result set of the function (that is the value of the column in my function table.) in my query.
my function is like:
create function abc
returns tab1 table(col1 int)
begin
end
my query should be something like
select colA, colB,
sum(case when tbl.col1 = function.tab1.col1 then 1 else 0 end) as Count
'"
"
"
can we do like this?
August 3, 2010 at 7:35 am
Pretty hard to guess what you are trying to do but maybe you can declare a table variable with the same structure as your function.
declare @MyTable....
insert @MyTable
select * from dbo.abc('parameters here')
Then you can join to it. Pretty vague answer but there is little to go on.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
August 3, 2010 at 11:32 pm
Hi,
Thanks for the reply.
I need to check who is using the latest version of the application.
so, in the function i am returning the version name and i will be using it in my query to fetch other related data.
here is my function:
ALTER FUNCTION [dbo].[GetLatestVersion](@WeekMinTime datetime)
RETURNS @LatestVersion table
( v_id int , version varchar(max) )
--StartDate datetime,
--EndDate datetime
AS
BEGIN
insert into @LatestVersion
select top 1 version.id ,version.version
from telemetry join version
on (telemetry.version_id = version.id)
where version like '%.rl-%.%.%'
and timeUploaded > @WeekMinTime and timeUploaded <= DATEADD(DAY,7,@WeekMinTime)
and (version.version like '%.rl-%.%.[0-9][0-9]' or version.version like '%.rl-%.%.[0-9][0-9][0-9]')
group by version.id, version.version
having COUNT(*) > 100
order by convert(int, substring(version.version,1,1))
,convert(int,substring(version.version,6,1))
,convert(int, substring(version.version,8,1))
,convert(int, substring(version.version,10,5)) desc
RETURN
END
and my query is :
select MIN(DATEADD(wk,datediff(wk,0,timeuploaded),0))as weekdate,
SUM(case when version_id = dbo.[GetLatestVersion] (here i must use the weekdate)
then 1 else 0 end) as LatestVersion (If this xondition is not true then they are using Old Version)
from telemetry
group by DATEPART(WW,timeUploaded), DATEPART(yy,timeUploaded)
Can you help me in fine tuning the query?
August 3, 2010 at 11:41 pm
I think you are looking for a CROSS APPLY
select MIN(DATEADD(wk,datediff(wk,0,timeuploaded),0))as weekdate,
SUM(case when version_id = LV.Version -- Changed here
then 1 else 0 end) as LatestVersion (If this xondition is not true then they are using Old Version)
from telemetry T
CROSS APPLY dbo.[GetLatestVersion] (MIN(DATEADD(wk,datediff(wk,0,timeuploaded),0))) LV -- Changed here
group by DATEPART(WW,timeUploaded), DATEPART(yy,timeUploaded)
See if the above is helpfull
How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/
August 3, 2010 at 11:59 pm
hi kingston,
Thanks for the reply. I tried this but i am getting this error:
"Aggregates on the right side of an APPLY cannot reference columns from the left side."
August 4, 2010 at 12:05 am
Try changing it with a sub query like this
SELECTweekdate,
case when version_id = LV.Version then 1 else 0 end as LatestVersion
FROM(
select MIN(DATEADD(wk,datediff(wk,0,timeuploaded),0))as weekdate
from telemetry
group by DATEPART(WW,timeUploaded), DATEPART(yy,timeUploaded)
) T
CROSS APPLYdbo.[GetLatestVersion] ( weekdate ) LV
How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply