Syntax - 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 2000 Forums

Transact-SQL (2000)

Syntax

Author

Topic

mhg1063
Starting Member

27 Posts

Posted - 2004-09-09 : 11:17:21

I have syntax that runs outside of a function and returns the correct result. The function returns null which is the else portion of the condition. I can't seem to get the variable to return correctly.
The case statement works
ELECT
l.loan_id,
p.pool_name,
l.note_rate,
p.coupon_rate,
p.guarantee_fee,
p.servicing_fee,
p.max_excess_servicing,
p.max_fee_buy_up,
p.process_order,
(l.note_rate-p.coupon_rate -p.servicing_fee - p.guarantee_fee) AS SLACK,

CASE WHEN p.process_order IN ('bu&xs', 'nt&bd') AND (l.note_rate-p.coupon_rate -p.servicing_fee - p.guarantee_fee) >= p.max_fee_buy_up AND (l.note_rate-p.coupon_rate -p.servicing_fee - p.guarantee_fee) >= 0 THEN p.max_fee_buy_up
WHEN p.process_order IN ('bu&xs', 'nt&bd') AND (l.note_rate-p.coupon_rate -p.servicing_fee - p.guarantee_fee) < p.max_fee_buy_up AND (l.note_rate-p.coupon_rate -p.servicing_fee - p.guarantee_fee) >= 0 THEN (l.note_rate-p.coupon_rate -p.servicing_fee - p.guarantee_fee)
WHEN p.process_order IN ('xs&bu') AND (l.note_rate-p.coupon_rate -p.servicing_fee - p.guarantee_fee) >= p.max_excess_servicing AND (l.note_rate-p.coupon_rate -p.servicing_fee - p.guarantee_fee) >= 0 AND (l.note_rate-p.coupon_rate -p.servicing_fee - p.guarantee_fee) - p.max_excess_servicing - p.max_fee_buy_up >= 0 THEN p.max_fee_buy_up

WHEN p.process_order IN ('bd&xs') AND (l.note_rate-p.coupon_rate -p.servicing_fee - p.guarantee_fee) >= p.max_excess_servicing + p.max_fee_buy_up AND (l.note_rate-p.coupon_rate -p.servicing_fee - p.guarantee_fee) >= 0 THEN p.max_fee_buy_up
WHEN p.process_order IN ('bd&xs') AND (l.note_rate-p.coupon_rate -p.servicing_fee - p.guarantee_fee) < p.max_excess_servicing + p.max_fee_buy_up AND (l.note_rate-p.coupon_rate -p.servicing_fee - p.guarantee_fee) >= 0 AND (l.note_rate-p.coupon_rate -p.servicing_fee - p.guarantee_fee) - p.max_excess_servicing >= -1 * p.guarantee_fee THEN (l.note_rate-p.coupon_rate -p.servicing_fee - p.guarantee_fee) - p.max_excess_servicing
WHEN p.process_order IN ('bd&xs') AND (l.note_rate-p.coupon_rate -p.servicing_fee - p.guarantee_fee) < p.max_excess_servicing + p.max_fee_buy_up AND (l.note_rate-p.coupon_rate -p.servicing_fee - p.guarantee_fee) >= 0 AND (l.note_rate-p.coupon_rate -p.servicing_fee - p.guarantee_fee) - p.max_excess_servicing < -1 * p.guarantee_fee THEN -1 * p.guarantee_fee

WHEN p.process_order IN ('xs&bu') AND (l.note_rate-p.coupon_rate -p.servicing_fee - p.guarantee_fee) >= p.max_excess_servicing AND (l.note_rate-p.coupon_rate -p.servicing_fee - p.guarantee_fee) >= 0 AND (l.note_rate-p.coupon_rate -p.servicing_fee - p.guarantee_fee) - p.max_excess_servicing - p.max_fee_buy_up < 0 THEN (l.note_rate-p.coupon_rate -p.servicing_fee - p.guarantee_fee) - p.max_excess_servicing
WHEN p.process_order IN ('xs&bu') AND (l.note_rate-p.coupon_rate -p.servicing_fee - p.guarantee_fee) < p.max_excess_servicing AND (l.note_rate-p.coupon_rate -p.servicing_fee - p.guarantee_fee) >= 0 THEN 0
WHEN p.process_order IN ('bu&xs', 'nt&bd', 'xs&bu') AND (l.note_rate-p.coupon_rate -p.servicing_fee - p.guarantee_fee) < 0 AND (l.note_rate-p.coupon_rate -p.servicing_fee - p.guarantee_fee) >= -1 * p.guarantee_fee THEN (l.note_rate-p.coupon_rate -p.servicing_fee - p.guarantee_fee)
WHEN p.process_order IN ('bd&xs') AND (l.note_rate-p.coupon_rate -p.servicing_fee - p.guarantee_fee) < 0 AND (l.note_rate-p.coupon_rate -p.servicing_fee - p.guarantee_fee) >= -1 * p.guarantee_fee THEN -1 * p.guarantee_fee
WHEN p.process_order IN ('bu&xs', 'nt&bd', 'xs&bu', 'bd&xs') AND (l.note_rate-p.coupon_rate -p.servicing_fee - p.guarantee_fee) < 0 AND (l.note_rate-p.coupon_rate -p.servicing_fee - p.guarantee_fee) < -1 * p.guarantee_fee THEN -1 * p.guarantee_fee
ELSE NULL END as test,

dbo.sf_actual_svc_buyup( l.loan_id,'bubd')
THe function it does not :

CREATE FUNCTION sf_actual_svc_buyup( @loan_id varchar,
@return_type VARCHAR(6) /* 'bubd' or 'xs' */)

RETURNS NUMERIC(9,6)

BEGIN

/* local variables */
DECLARE @note_rate NUMERIC(9,6)
DECLARE @coupon_rate NUMERIC(9,6)
DECLARE @servicing_fee NUMERIC(9,6)
DECLARE @max_excess_servicing NUMERIC(9,6)
DECLARE @guarantee_fee NUMERIC(9,6)
DECLARE @max_fee_buy_up NUMERIC(9,6)
DECLARE @process_order VARCHAR(10)
DECLARE @actual_fee_buy_up NUMERIC(9,6)
DECLARE @actual_excess_servicing NUMERIC(9,6)
DECLARE @slack NUMERIC(9,6)
DECLARE @buyup_result NUMERIC(9,6)

/* population of local variables based on loan_id */
SELECT @note_rate = COALESCE(l.note_rate,0),
@coupon_rate = COALESCE(p.coupon_rate,0),
@servicing_fee= COALESCE(p.servicing_fee,0),
@guarantee_fee =COALESCE(p.guarantee_fee,0),
@max_excess_servicing = COALESCE(p.max_excess_servicing,0),
@max_fee_buy_up = COALESCE(p.max_fee_buy_up,0),
@process_order = p.process_order

FROM loan l
INNER JOIN pscat_pools p on l.pool_name = p.pool_name
WHERE l.loan_id = @loan_id

/* Calculations to determine buy up result */

SET @slack = @note_rate - @coupon_rate - @servicing_fee - @guarantee_fee

IF @return_type = 'bubd'
BEGIN

SET @buyup_result =

CASE WHEN @process_order IN ('bu&xs', 'nt&bd') AND @slack >= @max_fee_buy_up AND @slack >= 0 THEN @max_fee_buy_up
WHEN @process_order IN ('bu&xs', 'nt&bd') AND @slack < @max_fee_buy_up AND @slack >= 0 THEN @slack
WHEN @process_order IN ('xs&bu') AND @slack >= @max_excess_servicing AND @slack >= 0 AND @slack - @max_excess_servicing - @max_fee_buy_up >= 0 THEN @max_fee_buy_up

WHEN @process_order IN ('bd&xs') AND @slack >= @max_excess_servicing + @max_fee_buy_up AND @slack >= 0 THEN @max_fee_buy_up
WHEN @process_order IN ('bd&xs') AND @slack < @max_excess_servicing + @max_fee_buy_up AND @slack >= 0 AND @slack - @max_excess_servicing >= -1 * @guarantee_fee THEN @slack - @max_excess_servicing
WHEN @process_order IN ('bd&xs') AND @slack < @max_excess_servicing + @max_fee_buy_up AND @slack >= 0 AND @slack - @max_excess_servicing < -1 * @guarantee_fee THEN -1 * @guarantee_fee

WHEN @process_order IN ('xs&bu') AND @slack >= @max_excess_servicing AND @slack >= 0 AND @slack - @max_excess_servicing - @max_fee_buy_up < 0 THEN @slack - @max_excess_servicing
WHEN @process_order IN ('xs&bu') AND @slack < @max_excess_servicing AND @slack >= 0 THEN 0
WHEN @process_order IN ('bu&xs', 'nt&bd', 'xs&bu') AND @slack < 0 AND @slack >= -1 * @guarantee_fee THEN @slack
WHEN @process_order IN ('bd&xs') AND @slack < 0 AND @slack >= -1 * @guarantee_fee THEN -1 * @guarantee_fee
WHEN @process_order IN ('bu&xs', 'nt&bd', 'xs&bu', 'bd&xs') AND @slack < 0 AND @slack < -1 * @guarantee_fee THEN -1 * @guarantee_fee
ELSE NULL END
END
ELSE IF @return_type = 'xs'
BEGIN

SELECT @buyup_result =
CASE WHEN @process_order IN ('bu&xs', 'nt&bd') AND @slack >= @max_fee_buy_up AND @slack >= 0 THEN @slack - @max_fee_buy_up
WHEN @process_order IN ('bu&xs', 'nt&bd') AND @slack < @max_fee_buy_up AND @slack >= 0 THEN 0
WHEN @process_order IN ('xs&bu') AND @slack >= @max_excess_servicing AND @slack >= 0 AND @slack - @max_excess_servicing - @max_fee_buy_up >= 0 THEN @slack - @max_fee_buy_up

WHEN @process_order IN ( 'bd&xs') AND @slack >= @max_excess_servicing + @max_fee_buy_up AND @slack >= 0 THEN @slack - @max_fee_buy_up
WHEN @process_order IN ( 'bd&xs') AND @slack < @max_excess_servicing + @max_fee_buy_up AND @slack >= 0 AND @slack - @max_excess_servicing >= -1 * @guarantee_fee THEN @max_excess_servicing
WHEN @process_order IN ( 'bd&xs') AND @slack < @max_excess_servicing + @max_fee_buy_up AND @slack >= 0 AND @slack - @max_excess_servicing < -1 * @guarantee_fee THEN @slack + @max_excess_servicing - @guarantee_fee

WHEN @process_order IN ('xs&bu') AND @slack >= @max_excess_servicing AND @slack >= 0 AND @slack - @max_excess_servicing - @max_fee_buy_up < 0 THEN @max_excess_servicing
WHEN @process_order IN ('xs&bu') AND @slack < @max_excess_servicing AND @slack >= 0 THEN @slack
WHEN @process_order IN ('bu&xs', 'nt&bd', 'xs&bu') AND @slack < 0 AND @slack >= -1 * @guarantee_fee THEN 0
WHEN @process_order IN ('bd&xs') AND @slack < 0 AND @slack >= -1 * @guarantee_fee THEN @slack + @guarantee_fee
WHEN @process_order IN ('bu&xs', 'nt&bd', 'xs&bu', 'bd&xs') AND @slack < 0 AND @slack < -1 * @guarantee_fee THEN @guarantee_fee + @slack
ELSE NULL END
END
RETURN( @buyup_result)
END
Any suggestions

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2004-09-09 : 11:22:19

Would you mind doing a few things?
- identify variables and possible values
- quick explaination of how the calculation (in words not code)
- maybe include a few examples, and the desired result for those examples

At first glance it looks much more difficult than it should be...

Corey

mhg1063
Starting Member

27 Posts

Posted - 2004-09-09 : 11:26:57

Never Mind I found the problem. The case statement in the select is the example it shiws the fields and the calcs.

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-09-09 : 11:28:45

i agree with Corey.
but as you do

SET @buyup_result = ...

and after that

SELECT @buyup_result = ...

i'm guessing there is your problem. you set the @buyup_result right in the first and wrong in the second. but that's just a guess.

Go with the flow & have fun! Else fight the flow

mhg1063
Starting Member

27 Posts

Posted - 2004-09-09 : 17:20:24

Thtatwas not the problem you can do either to set the variable. The problem was in the create portion of the function I was defining a variable and did not give it the proper size. Actually I did not define a size so it used the default size of one. My input values were longer than varchar(1). Once I defined the proper length everything was fine.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-09-09 : 17:23:19

quote:
Originally posted by spirit1

i agree with Corey.
but as you do

SET @buyup_result = ...

and after that

SELECT @buyup_result = ...

i'm guessing there is your problem. you set the @buyup_result right in the first and wrong in the second. but that's just a guess.

Huh? You can use SELECT or SET. SELECT allows you to set more than one variable at a time.

So you can do this:

SET @Var1 = 1
SET @Var2 = 2

Or

SELECT @Var1 = 1
SELECT @Var2 = 2

Or

SELECT @Var1 = 1, @Var2 = 2

Tara

mk_garg20
Constraint Violating Yak Guru

343 Posts

Posted - 2004-09-09 : 20:59:32

For detailed explaination on SET and SELECT
http://vyaskn.tripod.com/differences_between_set_and_select.htm

mk_garg

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-09-10 : 09:35:03

i know the diff between set and select

what i meant was that he was using
set @buyup_result = ...
and then
select @buyup_result = ...

so maybe there was a chance that he wanted to return the first value he was setting with set insetad of the one
he was setting with select. but as he figured it out, it doesn't matter.

Go with the flow & have fun! Else fight the flow

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2004-09-10 : 09:54:28

Spirit - if you want to return a value you use:

Select @myValue
or
Select value = @myValue

couldn't resist

Corey

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-09-10 : 10:00:36