Summing tables in a UDF
From: Andrew (anonymous_at_discussions.microsoft.com)
Date: 08/08/04
- Next message: Suler Abou: "Single INSERT statement creates Duplicate Entries!"
- Previous message: Erland Sommarskog: "Re: query against blob objects"
- Next in thread: Uri Dimant: "Re: Summing tables in a UDF"
- Reply: Uri Dimant: "Re: Summing tables in a UDF"
- Reply: --CELKO--: "Re: Summing tables in a UDF"
- Messages sorted by: [ date ] [ thread ]
Date: Sun, 8 Aug 2004 16:31:59 -0700
I am having a problem translating a custom function
developed in Sybase into a SQL Server UDF.
I keep getting a 208 error - 'invalid object' when I try
to run call the function in Query Analyzer even though it
compiles without errors in the Enterprize Create Function
module.
The original function used a select statement so I'm
thinking I have to use an inline table function and not a
scalar one? I was having the same issue with a scalar
function and could only get it to work once I granted
execute permissions on the function. No such property is
available for the inline table function however.
Here is the abbreviated version of the original function
I'm trying to recreate.
--------------------------- Sybase version
ALTER function gpcb.frentrev_bdgt(in evt_id integer)
returns integer
begin
declare rentrev integer;
select SUM(if er101_acct_order_dtl.er101_db_cr_flag
= 'D' then
er101_acct_order_dtl.er101_ext_chrg
else-er101_acct_order_dtl.er101_ext_chrg
endif) into rentrev from
ev200_event_master,er101_acct_order_dtl where
ev200_event_master.ev200_org_code =
er101_acct_order_dtl.er101_org_code and
ev200_event_master.ev200_evt_id =
er101_acct_order_dtl.er101_evt_id and
ev200_event_master.ev200_org_code = '02' and
ev200_event_master.ev200_evt_id = evt_id and
er101_acct_order_dtl.er101_phase = 'R' and
er101_acct_order_dtl.er101_dept = 'SALES' and
er101_acct_order_dtl.er101_res_class <= '7' and
er101_acct_order_dtl.er101_new_res_type <> '1GENRT' and
er101_acct_order_dtl.er101_new_res_type <> '3F&BRT' and
er101_acct_order_dtl.er101_new_res_type <> '3OTHRT' and
er101_acct_order_dtl.er101_res_code <> 'A500' and
er101_acct_order_dtl.er101_res_code <> 'A400'
group by ev200_event_master.ev200_evt_id;
if rentrev is null then set rentrev=0
end if;
return(rentrev)
end
-----------------------------------------------------
What UDF type do I need to recreate this in SQL Server?
The following code compiles correctly but I get the this
erroro when I try to run it in Query Analyzer...
Server: Msg 208, Level 16, State 1, Line 1
Invalid object name 'dbo.frentrevtest'.
---------------------- SQL Server version ------------
CREATE FUNCTION dbo.frentrevtest
(@EvtID Integer)
RETURNS @Result Table
(OrdTot Integer)
AS
BEGIN
INSERT INTO @Result
(OrdTot)
Select Sum(
er101_acct_order_dtl.er101_ext_chrg ) AS rentTot
From ev200_event_master, er101_acct_order_dtl
Where ev200_event_master.ev200_org_code =
er101_acct_order_dtl.er101_org_code AND
ev200_event_master.ev200_evt_id =
er101_acct_order_dtl.er101_evt_id AND
ev200_event_master.ev200_org_code = '02'
AND
ev200_event_master.ev200_evt_id = @EvtID
AND
er101_acct_order_dtl.er101_phase = '1' AND
er101_acct_order_dtl.er101_dept = 'SALES'
AND
er101_acct_order_dtl.er101_res_class
<= '7' AND
er101_acct_order_dtl.er101_new_res_type
<> '1GENRT' AND
er101_acct_order_dtl.er101_new_res_type
<> '3F&BRT' AND
er101_acct_order_dtl.er101_new_res_type
<> '3OTHRT' AND
er101_acct_order_dtl.er101_res_code
<> 'A500' AND
er101_acct_order_dtl.er101_res_code
<> 'A400'
RETURN
END
------------------------------------
I just want to be able to do a select/sum for a specific
condition on a table join. Why won't this work?
Any help would be greatly appreciated.
thx
- Next message: Suler Abou: "Single INSERT statement creates Duplicate Entries!"
- Previous message: Erland Sommarskog: "Re: query against blob objects"
- Next in thread: Uri Dimant: "Re: Summing tables in a UDF"
- Reply: Uri Dimant: "Re: Summing tables in a UDF"
- Reply: --CELKO--: "Re: Summing tables in a UDF"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
