Simple UDF Add Where clause to IF

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

Simple UDF Add Where clause to IF

Author

Topic

bdavis
Starting Member

12 Posts

Posted - 2006-07-10 : 14:55:26

Hello,
I'm trying to add a where clause to the return value. Right now, the code counts the number of times a certain relationship type is used. Once the count is done it returns the value, also if the count = 0, then the return value will be populated = 1. So to take this a step further, how would I add another where clause where sometimes I want the count to stay at = 0. For example, I'm trying to add a clause where for certain org_ogt_code='supplier' the return value will always be = 0. I tried adding a where clause to the IF statement, but i keep getting error code 207. Its saids it an invalid column name, but I have the table join. Any help would be greatly appreciated.
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
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
join co_organization on cst_key=org_cst_key
WHERE cxc_cst_key_1= @org_cst_key_ext
and cst_type='Organization'
and org_ogt_code !='supplier'
and (cxc_end_date is null or cxc_end_date>@todays_date)
and cxc_rlt_code = 'Multi-Golf Parent'
and cxc_delete_flag=0

if @ret_value = 0
select @ret_value = 1

return @ret_value
END

Srinika
Master Smack Fu Yak Hacker

1378 Posts

Posted - 2006-07-10 : 15:53:48

U may have

select @ret_value = count(*)
FROM
co_customer_x_customer (nolock)
join co_customer (nolock) on cxc_cst_key_2= cst_key
join co_organization on cst_key=org_cst_key
WHERE cxc_cst_key_1= @org_cst_key_ext
and cst_type='Organization'
and org_ogt_code !='supplier'
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 org_ogt_code='supplier'

if @ret_value = 0
select @ret_value = 1

Srinika

bdavis
Starting Member

12 Posts

Posted - 2006-07-11 : 09:36:54

That didnt work, how would I add a where clause in

if @ret_value = 0
select @ret_value = 1

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2006-07-11 : 09:45:03

quote:
Originally posted by bdavis

That didnt work, how would I add a where clause in

if @ret_value = 0
select @ret_value = 1

How about this...

if @ret_value = 0 and exists(select * from ... where org_ogt_code <> 'supplier')
select @ret_value = 1

Harsh Athalye
India.
"Nothing is Impossible"

bdavis
Starting Member

12 Posts

Posted - 2006-07-11 : 10:47:11

I thought that would work too, but I added two statements like this, but the last one over rules the first. Should I be using set in my logic then?

if @ret_value = 0 and exists(select * from co_customer_x_customer (nolock)
join co_customer (nolock) on cxc_cst_key_2= cst_key
join co_organization on cst_key=org_cst_key where org_ogt_code <> 'supplier')
select @ret_value = 1

if @ret_value = 1 and exists(select * from co_customer_x_customer (nolock)
join co_customer (nolock) on cxc_cst_key_2= cst_key
join co_organization on cst_key=org_cst_key where org_ogt_code = 'supplier')
select @ret_value = 0

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2006-07-11 : 11:09:37

quote:
Originally posted by bdavis

I thought that would work too, but I added two statements like this, but the last one over rules the first.

if @ret_value = 0 and exists(select * from co_customer_x_customer (nolock)
join co_customer (nolock) on cxc_cst_key_2= cst_key
join co_organization on cst_key=org_cst_key where org_ogt_code <> 'supplier')
select @ret_value = 1

if @ret_value = 1 and exists(select * from co_customer_x_customer (nolock)
join co_customer (nolock) on cxc_cst_key_2= cst_key
join co_organization on cst_key=org_cst_key where org_ogt_code = 'supplier')
select @ret_value = 0

try this...

if @ret_value <> 0 and exists(select * from co_customer_x_customer (nolock)
join co_customer (nolock) on cxc_cst_key_2= cst_key
join co_organization on cst_key=org_cst_key where org_ogt_code = 'supplier')
  select @ret_value = 0

BTW, how last if can override first one ...I think both of them are mutually exclusive.

Harsh Athalye
India.
"Nothing is Impossible"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources