UDF - Stuck on simple logic - 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

General SQL Server Forums

New to SQL Server Programming

UDF - Stuck on simple logic

Author

Topic

bdavis
Starting Member

12 Posts

Posted - 2006-07-25 : 14:59:13

I've been working on this off and on for several weeks and I'm to a point in which I'm stuck again. I'm trying to figure out the code in which if the count is greater than 1 for the ogt_code 'Golf Course' then add 1 to the final count.

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

return @ret_value
END

Srinika
Master Smack Fu Yak Hacker

1378 Posts

Posted - 2006-07-25 : 16:15:36

Ur Question and code doesn't match!!

eg. Where is ogt_code = 'Golf Course' in ur code ?

Also there r lot of things, in the code, which are not mentioned in the question.
And it is not clear what u were trying to do!!
U want some results to view? or need to change data ?

Srinika

bdavis
Starting Member

12 Posts

Posted - 2006-07-25 : 17:51:17

Right now, 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 count of greater than or equal 1 then I need to add 1 to the count. The reason why golf Course is not listed in the code is b/c its an organization type in which I want more than ZERO to be returned. So when the initial count is ZERO then the return value becomes 1.
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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources