Need Help with a UDF - 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

SQL Server Development (2000)

Need Help with a UDF

Author

Topic

dowens
Yak Posting Veteran

82 Posts

Posted - 2008-10-21 : 08:56:49

SQL 2000
I just do not know how to begin this one.

table member
Primary Member Relationship number
134 134 P (primary) = 01
134 890 S (spouse) = 02
134 765 D (dependent 1) = 03
134 98 D (dependent 2) = 04

the first dependent number must begin at 3 and is incremented by one for each new dependent

needs to return a number, the primary can have n number of dependents.

LoztInSpace
Aged Yak Warrior

940 Posts

Posted - 2008-10-21 : 09:20:01

it is not really clear what you need to return. why must it begin at 3? maybe use an identity and a type code where the code represents primary, spouse or dependent

dowens
Yak Posting Veteran

82 Posts

Posted - 2008-10-21 : 09:26:53

if a primary has three children (dependents)
each child needs to be assigned a number starting at 3
dependent child 1 = 03
dependent child 2 = 04
dependent child 3 = 05

the return should be the assigned number depending which child is passed through first.

LoztInSpace
Aged Yak Warrior

940 Posts

Posted - 2008-10-21 : 09:42:47

nope. still not getting it. why start at 3? what is so special about that? What happens when multile simultaneous updates occur? Can you elaborate on your overall situation?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-21 : 09:47:07

Also what if you've more than one primary? for each primary you will start over with 1 again?

dowens
Yak Posting Veteran

82 Posts

Posted - 2008-10-21 : 09:52:18

the primary member is idendifed as 1
the spouse is always idenified as 2
a dependent child will be idenfied as a 3
the next dependent child idenfied as a 4

I'm creating a extract and have to idenfiy each family group as above.
the spec indicates 2=spouse, 3=child 1, 4 =child 2 (I have the dob if that helps)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-21 : 10:05:18

quote:
Originally posted by dowens

the primary member is idendifed as 1
the spouse is always idenified as 2
a dependent child will be idenfied as a 3
the next dependent child idenfied as a 4

I'm creating a extract and have to idenfiy each family group as above.
the spec indicates 2=spouse, 3=child 1, 4 =child 2 (I have the dob if that helps)

understand how to post a question regarding database to get quick solution

http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

LoztInSpace
Aged Yak Warrior

940 Posts

Posted - 2008-10-21 : 10:07:20

maybe derive the number as you select it. You will need to do something like

select member.*,(select 1+count(*) from member b where a.primary=b.primary and a.memer>b.member
) from member a

sql server 2005 makes this easier using row_number and the like

dowens
Yak Posting Veteran

82 Posts

Posted - 2008-10-21 : 10:12:56

Okay I'll geive that a try

dowens
Yak Posting Veteran

82 Posts

Posted - 2008-10-21 : 10:23:09

That worked!!!!
My results are
742510 Savanah 6
742510 Shyana 5
742510 Randal 4
742510 Noah 3

dowens
Yak Posting Veteran

82 Posts

Posted - 2008-10-21 : 10:39:48

Okay here is my UDF, how do I return the number?

create Function dw_f_GetMemCnt(@Primary char(9),@dependent char(9),@Mcnt int)
returns int
as
Begin
select b.primary,
(select 3+ count(*)
from dw_v_member a
where a.primary=b.primary and a.dependent > b.dependent)
from dw_v_member b
where b.primary = @primary and b.dependent = @dependent and
b.relationshipcode = 'd' and b.dependent is not null
group by b.primary,b.dependent,b.relationshipcode
order by b.primary,b.dependent

return ????
End

hanbingl
Aged Yak Warrior

652 Posts

Posted - 2008-10-21 : 10:53:27

[code]create Function dw_f_GetMemCnt(@Primary char(9),@dependent char(9),@Mcnt int)
returns int
as
Begin
declare @cnt int

select b.primary, @cnt=
(select 3+ count(*)
from dw_v_member a
where a.primary=b.primary and a.dependent > b.dependent)
from dw_v_member b
where b.primary = @primary and b.dependent = @dependent and
b.relationshipcode = 'd' and b.dependent is not null
group by b.primary,b.dependent,b.relationshipcode
order by b.primary,b.dependent

return @cnt
End[/code]

dowens
Yak Posting Veteran

82 Posts

Posted - 2008-10-21 : 11:00:39

Receiveing this error:
A SELECT statement that assigns a value to a variable must not be combined with data-retrieval operations.

Looks like it's the order by statement.

hanbingl
Aged Yak Warrior

652 Posts

Posted - 2008-10-21 : 11:03:54

you'll need another var to store b.primary:

declare @cnt int, @prk int

select @prk=b.primary, @cnt=....

dowens
Yak Posting Veteran

82 Posts

Posted - 2008-10-21 : 11:18:28

Okay, working on it...

hanbingl
Aged Yak Warrior

652 Posts

Posted - 2008-10-21 : 11:39:19

or just drop that b.primary

hanbingl
Aged Yak Warrior

652 Posts

Posted - 2008-10-21 : 11:40:07

[code]
alter Function dw_f_GetMemCnt(@Primary char(9),@dependent char(9))
returns int
as
Begin

declare @cnt int

select @cnt=
(select 3+ count(*)
from tab1 a
where a.[primary]=b.[primary] and a.dependent > b.dependent)
from tab1 b
where b.[primary] = @primary and b.dependent = @dependent and
b.relationshipcode = 'd' and b.dependent is not null
group by b.[primary],b.dependent,b.relationshipcode
order by b.[primary],b.dependent

return @cnt
end[/code]

dowens
Yak Posting Veteran

82 Posts

Posted - 2008-10-21 : 11:59:11

Your right I do not need b.primary.

Okay, there seems to be a problem when there is only one dependent.
Can I add a condition such as if count = 1 then 3 ??
P 16051 01
P 17279 01
S 17279 02
D 17279 03
D 17279 04
P 23470 01
P 25229 01
D 25229 04 This one should be 3 or 03
P 26222 01

dowens
Yak Posting Veteran

82 Posts

Posted - 2008-10-21 : 13:26:32

I got it....

Thanks so much for everyone's help.......

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources