joining function - query - SQL Server Forums

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 2005 Forums

Transact-SQL (2005)

joining function - query

Author

Topic

ri16
Yak Posting Veteran

64 Posts

Posted - 2008-02-27 : 10:43:31

CREATE FUNCTION dbo.fn_copdmailinglist(@list_ varchar(60))
RETURNS @copdmailinglist TABLE
(
list_ varchar(60) ,
title_ varchar(255) ,
desc_ varchar(255),
message_id int ,
txt varchar(255) ,
cnt int ,
cnt_txt varchar(255)
)

--Returns a result set that lists all the copds
AS
BEGIN
WITH ListManager.dbo.[List Copd](list_ , title_ , message_id , txt , cnt , cnt_txt ) AS
(select @list_ , gmc.name_, osc.message_id , txt , cnt , cnt_txt from ListManager.dbo.[Open statisticscopd]('') osc
left outer join ListManager.dbo.get_mailingidcopd_('') gmc
on gmc.name_ = osc.title_
where list_ = @list_
)

-- copy the required columns to the result of the function
INSERT @copdmailinglist
SELECT list_ , title_ , message_id , txt , cnt , cnt_txt
FROM ListManager.dbo.[List Copd]
RETURN
END
GO

i m getting error that Incorrect syntax near the keyword 'WITH'.

can anyone tell me how to join functions in sql?

thanks.

jdaman
Constraint Violating Yak Guru

354 Posts

Posted - 2008-02-27 : 11:16:32

Just add a semicolon before WITH.


CREATE FUNCTION dbo.fn_copdmailinglist(@list_ varchar(60))
RETURNS @copdmailinglist TABLE
(
list_ varchar(60) ,
title_ varchar(255) ,
desc_ varchar(255),
message_id int ,
txt varchar(255) ,
cnt int ,
cnt_txt varchar(255)
)

--Returns a result set that lists all the copds
AS
BEGIN
;WITH ListManager.dbo.[List Copd](list_ , title_ , message_id , txt , cnt , cnt_txt ) AS
(select @list_ , gmc.name_, osc.message_id , txt , cnt , cnt_txt from ListManager.dbo.[Open statisticscopd]('') osc
left outer join ListManager.dbo.get_mailingidcopd_('') gmc
on gmc.name_ = osc.title_
where list_ = @list_
)

-- copy the required columns to the result of the function
INSERT @copdmailinglist
SELECT list_ , title_ , message_id , txt , cnt , cnt_txt
FROM ListManager.dbo.[List Copd]
RETURN
END
GO

ri16
Yak Posting Veteran

64 Posts

Posted - 2008-02-27 : 12:02:26

i m getting same incorrect syntax near ';' if i m adding it..

ri16
Yak Posting Veteran

64 Posts

Posted - 2008-02-27 : 12:03:07

i have functions like:

1st function [get_MailingIdCopd_] which is scalar valued function:

create function [dbo].[get_MailingIdCopd_] (@list_ varchar(60),
@Name_ varchar(60))
returns int as
begin
declare @mid int
if (select count(*)
from subsets_
where list_ = @list_
and name_ = replace(substring(@Name_,1,60),' ','_')
) > 1
set @mid = -999999
else set @mid =
(
select max(x.MessageID_) as Message_ID -- x.MessageId_ as [OutMail_.MessageID],
--x.*, y.*
from
( select *
from outmail_
) x,
(
select subsetid_ , name_ as title_
from subsets_
where list_ = @list_
and name_ = replace(substring(@Name_,1,60),' ','_')

) y
where x.subsetid_ = y.subsetid_
-- and x.title_ = y.title_
)
set @mid = isnull(@mid,0)
return(isnull(@mid,0))
end

/* select [dbo].[get_MailingIdCopd_] ('copd','copd_update_Jan2008%') as mailingidcopd

mailingidcopd
-------------
150430
*/

second function is: [Open_StatisticsCopd] which is table valued function:

create function [dbo].[Open_StatisticsCopd] (@Mailing_id int)
returns table as
return
(
select *
from ListManager.dbo.[Open statisticsCopd]
where messageid_ = ListManager.dbo.get_mailingidcopd_('copd',niaid_constituent.dbo.get_mailing_subject(@Mailing_id))
)

/* in this where messageid_ = 150430
so ListManager.dbo.get_mailingidcopd_('copd',niaid_constituent.dbo.get_mailing_subject(@Mailing_id) = 150430
*/

so when

select * from ListManager.dbo.[Open statisticsCopd]
where messageid_ = 150430

i m getting

indx list_ messageid_ txt cnt cnt_txt prcnt
----------- ------------------------------------------------------------ ----------- ---------------------------------- ----------- ---------------------- ---------------------------------------
1 copd 150430 Detected unique opens:209 209 Detected unique opens: 0.0
2 copd 150430 Detected total opens:840 840 Detected total opens: 0.0

(2 row(s) affected)

(note: in [get_mailing_subject] function-scalar valued function:

create function [dbo].[get_mailing_subject] (@mailing_id int)
returns varchar(127) as
begin
return
(
select rtrim(ltrim(mailing_subject)) from tblmailing where mailing_id = @mailing_id
)
end
)

so i got project to create new function in which i get all columns - indx,list_, message_id....
by joining this given two functions..

can anyone tell me how to join two functions in sql?

i m new in creating function...
thanks for ur help...

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-02-27 : 12:15:09

You can use table valued function in a join in same way as two tables

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources