return value help..

Question

Post reply

return value help..

Bryan Davis-355734

SSC Veteran

Points: 209
More actions
August 8, 2006 at 2:35 pm

#103214

I've been working on this udf off and on for several weeks when I have a chance and I'm still having problems modifying this function. Everything works great except for one exception I need to add to the code below. First of all the code counts the number of times a certain relationship type is used. Once the count is done it returns the count value. If the count value is 0 then it returnes 1 as the value. However, if @ogt_code = 'Supplier' or Allied or Hotel/Conference Facility or NGCOA Internal Vendor or USGA Program the return value will always = 0. What I'm trying to do is if the Org Type is = 'Golf Course' and it has an actual initial return count of greater than or equal 1 then I need to add 1 to the overall count. So basically, what I need to do is to add 1 to the final count when the initial count of the @ret_value is greater than or equal 1. If the inital count = 0, which in the code it is stated as @ ret_value=0 then the @ret_value becomes 1.
Try to explain better:
If you are an Org Type of Golf Course and you have no other courses you represent then you only represent 1 course. In the code I've written, if you dont have any other courses you represent (which the count(*) uses the cxc_rlt_code = 'Multi-Golf Parent' in the count) then your initial count = 0, but according to my code when the @ret_value = 0
select @ret_value = 1
So this way, golf courses with no related organizations always represent themselves with the course count of 1. But I also need to represent golf courses that return a count of 1 (according to initial Count) so I can properly account for the number of courses its related too and its own self.
I hope this helps
Thanks
Bryan
___________________________________________________

CREATE FUNCTION [dbo].[ngcoa_get_multi_golf_count] (
@org_cst_key_ext uniqueidentifier,
@todays_date av_date)
RETURNS int AS
BEGIN
declare @ret_value int
declare @ogt_code nvarchar(30)
select @ret_value = 0
select @ret_value = count(*)
FROM
co_customer_x_customer (nolock)
join co_customer (nolock) on cxc_cst_key_2= cst_key
WHERE cxc_cst_key_1= @org_cst_key_ext
and cst_type='Organization'
and (cxc_end_date is null or cxc_end_date>@todays_date)
and cxc_rlt_code = 'Multi-Golf Parent'
and cxc_delete_flag=0
and cst_delete_flag=0
if @ret_value = 0
select @ret_value = 1
select @ogt_code = org_ogt_code
from co_organization (nolock)
where org_cst_key = @org_cst_key_ext
if @ogt_code = 'Supplier'
   select @ret_value = 0
if @ogt_code = 'Allied'
    select @ret_value = 0
if @ogt_code = 'Hotel/Conference Facility'
    select @ret_value = 0
if @ogt_code = 'NGCOA Internal Vendor'
    select @ret_value = 0
if @ogt_code = 'USGA Program'
    select @ret_value = 0
--if (@ogt_code = 'Golf Course' and @ret_value >1) --I added this to try to fix
    --select (@ret_value + 1) --I added this to try to fix

return @ret_value
return
END

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 1

Shouldn't the select in the if statement look like this:

select @ret_value = @ret_vale + 1

I would prefer to use a set statement where you are using select to set @ret_value:

set @ret_value = @ret_vale + 1

hth,

Lynn

Bryan Davis-355734 SSC Veteran Points: 209 More actions · Answer 2

Lynne thanks for the reply that helped alot. But I still need to know how to add one to the count when the initial count > 1. For instance, when the count starts and if the count = 0 then it returns 1 automatically. So The code says

if @ret_value = 0

select @ret_value = 1

The modification I just made will make all ogt_code = 'Golf Course' = 2, because the return is always set to at least zero. Is what I'm trying to do possible? Below I copied what I wrote before. Maybe that will clarify things. Even if you cant figure it out, you've been alot of help.

Thanks

Try to explain better:

If you are an Org Type of Golf Course and you have no other courses you represent then you only represent 1 course. In the code I've written, if you dont have any other courses you represent (which the count(*) uses the cxc_rlt_code = 'Multi-Golf Parent' in the count) then your initial count = 0, but according to my code when the @ret_value = 0

select @ret_value = 1

So this way, golf courses with no related organizations always represent themselves with the course count of 1. But I also need to represent golf courses that return a count of 1 (according to initial Count) so I can properly account for the number of courses its related too and its own self.

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 3

You have it at the end of your procedure, it just isn't coded right.

You have:

--if (@ogt_code = 'Golf Course' and @ret_value >1) --I added this to try to fix

--select (@ret_value + 1) --I added this to try to fix

try:

if (@ogt_code = 'Golf Course' and @ret_value > 1) set @ret_value = @ret_value + 1

hth,

Lynn