Sybase NNTP forums - End Of Life (EOL)

The NNTP forums from Sybase - forums.sybase.com - are now closed.

All new questions should be directed to the appropriate forum at the SAP Community Network (SCN).

Individual products have links to the respective forums on SCN, or you can go to SCN and search for your product in the search box (upper right corner) to find your specific developer center.

Stored procedure error primary key is not unique

20 posts in General Discussion Last posting was on 2004-03-05 18:21:20.0Z
Newbegin Posted on 2004-03-04 18:01:08.0Z
Sender: 4911.40476efb.1804289383@sybase.com
From: Newbegin
Newsgroups: ianywhere.public.general
Subject: Stored procedure error primary key is not unique
X-Mailer: WebNews to Mail Gateway v1.1s
Message-ID: <40476f12.4913.846930886@sybase.com>
X-Original-NNTP-Posting-Host: 10.22.241.42
X-Original-Trace: 4 Mar 2004 10:01:54 -0800, 10.22.241.42
Lines: 74
X-Original-NNTP-Posting-Host: forums-2-dub.sybase.com
X-Original-Trace: 4 Mar 2004 09:59:43 -0800, forums-2-dub.sybase.com
NNTP-Posting-Host: forums-master.sybase.com
X-Original-NNTP-Posting-Host: forums-master.sybase.com
Date: 4 Mar 2004 10:01:08 -0800
X-Trace: forums-1-dub 1078423268 10.22.108.75 (4 Mar 2004 10:01:08 -0800)
X-Original-Trace: 4 Mar 2004 10:01:08 -0800, forums-master.sybase.com
X-Authenticated-User: ngsysop
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub ianywhere.public.general:2702
Article PK: 6180

We are using a cursor to loop through all the person_id and
enter value in the emp_leave_balance table for which it does
not have data. when we run the stored procedure for a
single person the procedure works fine but when we include
multiple person it does not work.
We have ASA V9
with the patch up date of 9.0.1

This same stored procedure is working on seven.

The following is the stored procedure

ALTER PROCEDURE "resource_owner"."emp_leave_balance_sp"()
begin
declare @payper_yr_id id;
declare @payper_seq seq;
declare @personid id;
declare err_notfound exception for sqlstate value '02000';
declare ThePerson dynamic scroll cursor for select
person_id from person;
select pay_per_yr_id,pay_per_seq into
@payper_yr_id,@payper_seq from pay_per where
start_dt <= TODAY(*) and end_dt >= TODAY(*);
open ThePerson;
PersonLoop: loop
fetch next ThePerson into @personid;
if sqlstate = err_notfound then
leave PersonLoop
end if;
if @personid is not null then
insert into emp_leave_balance(
person_id,task_id,pay_per_yr_id,pay_per_seq,leave_accrual_rate
,

leave_freq_cd,adjustment,used_hour,pay_per_balance,final_balance)
select
a.person_id,a.task_id,a.pay_per_yr_id,a.pay_per_seq,a.leave_accrual_rate
,
a.leave_freq_cd,a.adjustment,a.used_hour,
(select b.pay_per_balance from
v_emp_pref_leave_payper_balance as b where
a.person_id = b.person_id and
a.task_id = b.task_id and
a.pay_per_yr_id = b.pay_per_yr_id and
a.pay_per_seq = b.pay_per_seq) as
pay_per_balance,
(select f.final_balance from
v_emp_pref_leave_balance_final as f where
a.person_id = f.person_id and
a.task_id = f.task_id and
a.pay_per_yr_id = f.pay_per_yr_id and
a.pay_per_seq = f.pay_per_seq) as final_balance
from
v_emp_leave_rate_payper_adj as a where
a.person_id = @personid and
a.pay_per_yr_id = @payper_yr_id and
a.pay_per_seq = @payper_seq and
not a.task_id = any(select b1.task_id from
emp_leave_balance as b1 where
b1.person_id = @personid and
b1.pay_per_yr_id = @payper_yr_id and
b1.pay_per_seq = @payper_seq)
end if
end loop PersonLoop;
close ThePerson;
if sqlcode <> 0 then
rollback work
else
commit work
end if
end

Thanks
Newbegin


Robert Waywell Posted on 2004-03-04 18:33:09.0Z
From: "Robert Waywell" <nospam_rwaywell@ianywhere.com>
Newsgroups: ianywhere.public.general
References: <40476f12.4913.846930886@sybase.com>
Subject: Re: Stored procedure error primary key is not unique
Lines: 102
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2600.0000
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2600.0000
X-Original-NNTP-Posting-Host: 172.31.141.7
Message-ID: <40477693$1@forums-2-dub>
X-Original-Trace: 4 Mar 2004 10:33:55 -0800, 172.31.141.7
X-Original-NNTP-Posting-Host: forums-2-dub.sybase.com
X-Original-Trace: 4 Mar 2004 10:31:44 -0800, forums-2-dub.sybase.com
NNTP-Posting-Host: forums-master.sybase.com
X-Original-NNTP-Posting-Host: forums-master.sybase.com
Date: 4 Mar 2004 10:33:09 -0800
X-Trace: forums-1-dub 1078425189 10.22.108.75 (4 Mar 2004 10:33:09 -0800)
X-Original-Trace: 4 Mar 2004 10:33:09 -0800, forums-master.sybase.com
X-Authenticated-User: ngsysop
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub ianywhere.public.general:2703
Article PK: 6176

At the time you get the error, is it accurate?

--
-----------------------------------------------
Robert Waywell
Sybase Adaptive Server Anywhere Developer - Version 8
Sybase Certified Professional

Sybase's iAnywhere Solutions

Please respond ONLY to newsgroup

EBF's and Patches: http://downloads.sybase.com
choose SQL Anywhere Studio >> change 'time frame' to all

To Submit Bug Reports:
http://case-express.sybase.com/cx/cx.stm?starturl=casemessage.ssc?CASETYPE=B
ug

SQL Anywhere Studio Supported Platforms and Support Status
http://my.sybase.com/detail?id=1002288

Whitepapers, TechDocs, and bug fixes are all available through the iAnywhere
Developer Community at www.ianywhere.com/developer

<Newbegin> wrote in message news:40476f12.4913.846930886@sybase.com...
> We are using a cursor to loop through all the person_id and
> enter value in the emp_leave_balance table for which it does
> not have data. when we run the stored procedure for a
> single person the procedure works fine but when we include
> multiple person it does not work.
> We have ASA V9
> with the patch up date of 9.0.1
>
> This same stored procedure is working on seven.
>
> The following is the stored procedure
>
> ALTER PROCEDURE "resource_owner"."emp_leave_balance_sp"()
> begin
> declare @payper_yr_id id;
> declare @payper_seq seq;
> declare @personid id;
> declare err_notfound exception for sqlstate value '02000';
> declare ThePerson dynamic scroll cursor for select
> person_id from person;
> select pay_per_yr_id,pay_per_seq into
> @payper_yr_id,@payper_seq from pay_per where
> start_dt <= TODAY(*) and end_dt >= TODAY(*);
> open ThePerson;
> PersonLoop: loop
> fetch next ThePerson into @personid;
> if sqlstate = err_notfound then
> leave PersonLoop
> end if;
> if @personid is not null then
> insert into emp_leave_balance(
> person_id,task_id,pay_per_yr_id,pay_per_seq,leave_accrual_rate
> ,
>
> leave_freq_cd,adjustment,used_hour,pay_per_balance,final_balance)
> select
> a.person_id,a.task_id,a.pay_per_yr_id,a.pay_per_seq,a.leave_accrual_rate
> ,
> a.leave_freq_cd,a.adjustment,a.used_hour,
> (select b.pay_per_balance from
> v_emp_pref_leave_payper_balance as b where
> a.person_id = b.person_id and
> a.task_id = b.task_id and
> a.pay_per_yr_id = b.pay_per_yr_id and
> a.pay_per_seq = b.pay_per_seq) as
> pay_per_balance,
> (select f.final_balance from
> v_emp_pref_leave_balance_final as f where
> a.person_id = f.person_id and
> a.task_id = f.task_id and
> a.pay_per_yr_id = f.pay_per_yr_id and
> a.pay_per_seq = f.pay_per_seq) as final_balance
> from
> v_emp_leave_rate_payper_adj as a where
> a.person_id = @personid and
> a.pay_per_yr_id = @payper_yr_id and
> a.pay_per_seq = @payper_seq and
> not a.task_id = any(select b1.task_id from
> emp_leave_balance as b1 where
> b1.person_id = @personid and
> b1.pay_per_yr_id = @payper_yr_id and
> b1.pay_per_seq = @payper_seq)
> end if
> end loop PersonLoop;
> close ThePerson;
> if sqlcode <> 0 then
> rollback work
> else
> commit work
> end if
> end
>
> Thanks
> Newbegin


Newbegin Posted on 2004-03-04 19:27:41.0Z
Sender: 5dda.404782ce.1804289383@sybase.com
From: Newbegin
Newsgroups: ianywhere.public.general
Subject: Re: Stored procedure error primary key is not unique
X-Mailer: WebNews to Mail Gateway v1.1s
Message-ID: <4047832d.5de0.846930886@sybase.com>
References: <40476f12.4913.846930886@sybase.com><40477693$1@forums-2-dub>
NNTP-Posting-Host: 10.22.241.41
X-Original-NNTP-Posting-Host: 10.22.241.41
Date: 4 Mar 2004 11:27:41 -0800
X-Trace: forums-1-dub 1078428461 10.22.241.41 (4 Mar 2004 11:27:41 -0800)
X-Original-Trace: 4 Mar 2004 11:27:41 -0800, 10.22.241.41
Lines: 105
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub ianywhere.public.general:2704
Article PK: 6178

Yes the error is accurate

> At the time you get the error, is it accurate?
>
> --
> -----------------------------------------------
> Robert Waywell
> Sybase Adaptive Server Anywhere Developer - Version 8
> Sybase Certified Professional
>
> Sybase's iAnywhere Solutions
>
> Please respond ONLY to newsgroup
>
> EBF's and Patches: http://downloads.sybase.com
> choose SQL Anywhere Studio >> change 'time frame' to all
>
> To Submit Bug Reports:
>
http://case-express.sybase.com/cx/cx.stm?starturl=casemessage.ssc?CASETYPE=B
> ug
>
> SQL Anywhere Studio Supported Platforms and Support Status
> http://my.sybase.com/detail?id=1002288
>
> Whitepapers, TechDocs, and bug fixes are all available
> through the iAnywhere Developer Community at
> www.ianywhere.com/developer
>
> <Newbegin> wrote in message
> > news:40476f12.4913.846930886@sybase.com... We are using
> > a cursor to loop through all the person_id and enter
> > value in the emp_leave_balance table for which it does
> > not have data. when we run the stored procedure for a
> > single person the procedure works fine but when we
> > include multiple person it does not work. We have ASA V9
> > with the patch up date of 9.0.1
> >
> > This same stored procedure is working on seven.
> >
> > The following is the stored procedure
> >
> > ALTER PROCEDURE
> > "resource_owner"."emp_leave_balance_sp"() begin
> > declare @payper_yr_id id;
> > declare @payper_seq seq;
> > declare @personid id;
> > declare err_notfound exception for sqlstate value
> > '02000'; declare ThePerson dynamic scroll cursor for
> > select person_id from person;
> > select pay_per_yr_id,pay_per_seq into
> > @payper_yr_id,@payper_seq from pay_per where
> > start_dt <= TODAY(*) and end_dt >= TODAY(*);
> > open ThePerson;
> > PersonLoop: loop
> > fetch next ThePerson into @personid;
> > if sqlstate = err_notfound then
> > leave PersonLoop
> > end if;
> > if @personid is not null then
> > insert into emp_leave_balance(
> > person_id,task_id,pay_per_yr_id,pay_per_seq
> > ,leave_accrual_rate ,
> >
> > leave_freq_cd,adjustment,used_hour,pay_per_balance
> > ,final_balance) select
> > a.person_id,a.task_id,a.pay_per_yr_id,a.pay_per_seq
> > ,a.leave_accrual_rate ,
> > a.leave_freq_cd,a.adjustment,a.used_hour,
> > (select b.pay_per_balance from
> > v_emp_pref_leave_payper_balance as b where
> > a.person_id = b.person_id and
> > a.task_id = b.task_id and
> > a.pay_per_yr_id = b.pay_per_yr_id and
> > a.pay_per_seq = b.pay_per_seq) as
> > pay_per_balance,
> > (select f.final_balance from
> > v_emp_pref_leave_balance_final as f where
> > a.person_id = f.person_id and
> > a.task_id = f.task_id and
> > a.pay_per_yr_id = f.pay_per_yr_id and
> > a.pay_per_seq = f.pay_per_seq) as
> > final_balance from
> > v_emp_leave_rate_payper_adj as a where
> > a.person_id = @personid and
> > a.pay_per_yr_id = @payper_yr_id and
> > a.pay_per_seq = @payper_seq and
> > not a.task_id = any(select b1.task_id from
> > emp_leave_balance as b1 where
> > b1.person_id = @personid and
> > b1.pay_per_yr_id = @payper_yr_id and
> > b1.pay_per_seq = @payper_seq)
> > end if
> > end loop PersonLoop;
> > close ThePerson;
> > if sqlcode <> 0 then
> > rollback work
> > else
> > commit work
> > end if
> > end
> >
> > Thanks
> > Newbegin
>
>


Reg Domaratzki Posted on 2004-03-04 20:05:27.0Z
From: "Reg Domaratzki" <Spam_bad_rdomarat@ianywhere.com>
Newsgroups: ianywhere.public.general
References: <40476f12.4913.846930886@sybase.com><40477693$1@forums-2-dub> <4047832d.5de0.846930886@sybase.com>
Subject: Re: Stored procedure error primary key is not unique
Lines: 123
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2800.1158
X-MIMEOLE: Produced By Microsoft MimeOLE V6.00.2800.1165
NNTP-Posting-Host: rdomarat-pc.sybase.com
X-Original-NNTP-Posting-Host: rdomarat-pc.sybase.com
Message-ID: <40478c07$1@forums-1-dub>
Date: 4 Mar 2004 12:05:27 -0800
X-Trace: forums-1-dub 1078430727 172.31.143.163 (4 Mar 2004 12:05:27 -0800)
X-Original-Trace: 4 Mar 2004 12:05:27 -0800, rdomarat-pc.sybase.com
X-Authenticated-User: techsupp
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub ianywhere.public.general:2705
Article PK: 6175

I'm not quite sure what the problem is then. If the engine reports a
primary key violation when you try to insert a duplicate primary key, that's
a good thing.

--
Reg Domaratzki, Sybase iAnywhere Solutions
Sybase Certified Professional - Sybase ASA Developer Version 8
Please reply only to the newsgroup

iAnywhere Developer Community : http://www.ianywhere.com/developer
ASA Patches and EBFs : http://downloads.sybase.com/swx/sdmain.stm
-> Choose SQL Anywhere Studio
-> Set "Platform Preview" and "Time Frame" to ALL

<Newbegin> wrote in message news:4047832d.5de0.846930886@sybase.com...
> Yes the error is accurate
> > At the time you get the error, is it accurate?
> >
> > --
> > -----------------------------------------------
> > Robert Waywell
> > Sybase Adaptive Server Anywhere Developer - Version 8
> > Sybase Certified Professional
> >
> > Sybase's iAnywhere Solutions
> >
> > Please respond ONLY to newsgroup
> >
> > EBF's and Patches: http://downloads.sybase.com
> > choose SQL Anywhere Studio >> change 'time frame' to all
> >
> > To Submit Bug Reports:
> >
>
http://case-express.sybase.com/cx/cx.stm?starturl=casemessage.ssc?CASETYPE=B
> > ug
> >
> > SQL Anywhere Studio Supported Platforms and Support Status
> > http://my.sybase.com/detail?id=1002288
> >
> > Whitepapers, TechDocs, and bug fixes are all available
> > through the iAnywhere Developer Community at
> > www.ianywhere.com/developer
> >
> > <Newbegin> wrote in message
> > > news:40476f12.4913.846930886@sybase.com... We are using
> > > a cursor to loop through all the person_id and enter
> > > value in the emp_leave_balance table for which it does
> > > not have data. when we run the stored procedure for a
> > > single person the procedure works fine but when we
> > > include multiple person it does not work. We have ASA V9
> > > with the patch up date of 9.0.1
> > >
> > > This same stored procedure is working on seven.
> > >
> > > The following is the stored procedure
> > >
> > > ALTER PROCEDURE
> > > "resource_owner"."emp_leave_balance_sp"() begin
> > > declare @payper_yr_id id;
> > > declare @payper_seq seq;
> > > declare @personid id;
> > > declare err_notfound exception for sqlstate value
> > > '02000'; declare ThePerson dynamic scroll cursor for
> > > select person_id from person;
> > > select pay_per_yr_id,pay_per_seq into
> > > @payper_yr_id,@payper_seq from pay_per where
> > > start_dt <= TODAY(*) and end_dt >= TODAY(*);
> > > open ThePerson;
> > > PersonLoop: loop
> > > fetch next ThePerson into @personid;
> > > if sqlstate = err_notfound then
> > > leave PersonLoop
> > > end if;
> > > if @personid is not null then
> > > insert into emp_leave_balance(
> > > person_id,task_id,pay_per_yr_id,pay_per_seq
> > > ,leave_accrual_rate ,
> > >
> > > leave_freq_cd,adjustment,used_hour,pay_per_balance
> > > ,final_balance) select
> > > a.person_id,a.task_id,a.pay_per_yr_id,a.pay_per_seq
> > > ,a.leave_accrual_rate ,
> > > a.leave_freq_cd,a.adjustment,a.used_hour,
> > > (select b.pay_per_balance from
> > > v_emp_pref_leave_payper_balance as b where
> > > a.person_id = b.person_id and
> > > a.task_id = b.task_id and
> > > a.pay_per_yr_id = b.pay_per_yr_id and
> > > a.pay_per_seq = b.pay_per_seq) as
> > > pay_per_balance,
> > > (select f.final_balance from
> > > v_emp_pref_leave_balance_final as f where
> > > a.person_id = f.person_id and
> > > a.task_id = f.task_id and
> > > a.pay_per_yr_id = f.pay_per_yr_id and
> > > a.pay_per_seq = f.pay_per_seq) as
> > > final_balance from
> > > v_emp_leave_rate_payper_adj as a where
> > > a.person_id = @personid and
> > > a.pay_per_yr_id = @payper_yr_id and
> > > a.pay_per_seq = @payper_seq and
> > > not a.task_id = any(select b1.task_id from
> > > emp_leave_balance as b1 where
> > > b1.person_id = @personid and
> > > b1.pay_per_yr_id = @payper_yr_id and
> > > b1.pay_per_seq = @payper_seq)
> > > end if
> > > end loop PersonLoop;
> > > close ThePerson;
> > > if sqlcode <> 0 then
> > > rollback work
> > > else
> > > commit work
> > > end if
> > > end
> > >
> > > Thanks
> > > Newbegin
> >
> >


Newbegin Posted on 2004-03-04 20:25:57.0Z
Sender: 4c1d.40478fd6.1804289383@sybase.com
From: Newbegin
Newsgroups: ianywhere.public.general
Subject: Re: Stored procedure error primary key is not unique
X-Mailer: WebNews to Mail Gateway v1.1s
Message-ID: <40479103.4c2d.846930886@sybase.com>
References: <40476f12.4913.846930886@sybase.com><40477693$1@forums-2-dub> <4047832d.5de0.846930886@sybase.com><40478c07$1@forums-1-dub>
X-Original-NNTP-Posting-Host: 10.22.241.42
X-Original-Trace: 4 Mar 2004 12:26:43 -0800, 10.22.241.42
Lines: 127
X-Original-NNTP-Posting-Host: forums-2-dub.sybase.com
X-Original-Trace: 4 Mar 2004 12:24:31 -0800, forums-2-dub.sybase.com
NNTP-Posting-Host: forums-master.sybase.com
X-Original-NNTP-Posting-Host: forums-master.sybase.com
Date: 4 Mar 2004 12:25:57 -0800
X-Trace: forums-1-dub 1078431957 10.22.108.75 (4 Mar 2004 12:25:57 -0800)
X-Original-Trace: 4 Mar 2004 12:25:57 -0800, forums-master.sybase.com
X-Authenticated-User: ngsysop
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub ianywhere.public.general:2707
Article PK: 6179

The row is not supposed to be added if the primary key
already exists.

> I'm not quite sure what the problem is then. If the
> engine reports a primary key violation when you try to
> insert a duplicate primary key, that's a good thing.
>
> --
> Reg Domaratzki, Sybase iAnywhere Solutions
> Sybase Certified Professional - Sybase ASA Developer
> Version 8 Please reply only to the newsgroup
>
> iAnywhere Developer Community :
> http://www.ianywhere.com/developer ASA Patches and EBFs :
> http://downloads.sybase.com/swx/sdmain.stm
> -> Choose SQL Anywhere Studio
> -> Set "Platform Preview" and "Time Frame" to ALL
>
> <Newbegin> wrote in message
> > news:4047832d.5de0.846930886@sybase.com... Yes the error
> > > is accurate At the time you get the error, is it
> > accurate? >
> > > --
> > > -----------------------------------------------
> > > Robert Waywell
> > > Sybase Adaptive Server Anywhere Developer - Version 8
> > > Sybase Certified Professional
> > >
> > > Sybase's iAnywhere Solutions
> > >
> > > Please respond ONLY to newsgroup
> > >
> > > EBF's and Patches: http://downloads.sybase.com
> > > choose SQL Anywhere Studio >> change 'time frame' to
> > all >
> > > To Submit Bug Reports:
> > >
> >
>
http://case-express.sybase.com/cx/cx.stm?starturl=casemessage.ssc?CASETYPE=B
> > > ug
> > >
> > > SQL Anywhere Studio Supported Platforms and Support
> > > Status http://my.sybase.com/detail?id=1002288
> > >
> > > Whitepapers, TechDocs, and bug fixes are all available
> > > through the iAnywhere Developer Community at
> > > www.ianywhere.com/developer
> > >
> > > <Newbegin> wrote in message
> > > > news:40476f12.4913.846930886@sybase.com... We are
> > > > using a cursor to loop through all the person_id and
> > > > enter value in the emp_leave_balance table for which
> > > > it does not have data. when we run the stored
> > > > procedure for a single person the procedure works
> > > > fine but when we include multiple person it does not
> > > > work. We have ASA V9 with the patch up date of 9.0.1
> > > >
> > > > This same stored procedure is working on seven.
> > > >
> > > > The following is the stored procedure
> > > >
> > > > ALTER PROCEDURE
> > > > "resource_owner"."emp_leave_balance_sp"() begin
> > > > declare @payper_yr_id id;
> > > > declare @payper_seq seq;
> > > > declare @personid id;
> > > > declare err_notfound exception for sqlstate value
> > > > '02000'; declare ThePerson dynamic scroll cursor
> > > > for select person_id from person;
> > > > select pay_per_yr_id,pay_per_seq into
> > > > @payper_yr_id,@payper_seq from pay_per where
> > > > start_dt <= TODAY(*) and end_dt >= TODAY(*);
> > > > open ThePerson;
> > > > PersonLoop: loop
> > > > fetch next ThePerson into @personid;
> > > > if sqlstate = err_notfound then
> > > > leave PersonLoop
> > > > end if;
> > > > if @personid is not null then
> > > > insert into emp_leave_balance(
> > > > person_id,task_id,pay_per_yr_id,pay_per_seq
> > > > ,leave_accrual_rate ,
> > > >
> > > > leave_freq_cd,adjustment,used_hour,pay_per_balance
> > > > ,final_balance) select
> > > > a.person_id,a.task_id,a.pay_per_yr_id,a.pay_per_seq
> > > > ,a.leave_accrual_rate ,
> > > > a.leave_freq_cd,a.adjustment,a.used_hour,
> > > > (select b.pay_per_balance from
> > > > v_emp_pref_leave_payper_balance as b where
> > > > a.person_id = b.person_id and
> > > > a.task_id = b.task_id and
> > > > a.pay_per_yr_id = b.pay_per_yr_id and
> > > > a.pay_per_seq = b.pay_per_seq) as
> > > > pay_per_balance,
> > > > (select f.final_balance from
> > > > v_emp_pref_leave_balance_final as f where
> > > > a.person_id = f.person_id and
> > > > a.task_id = f.task_id and
> > > > a.pay_per_yr_id = f.pay_per_yr_id and
> > > > a.pay_per_seq = f.pay_per_seq) as
> > > > final_balance from
> > > > v_emp_leave_rate_payper_adj as a where
> > > > a.person_id = @personid and
> > > > a.pay_per_yr_id = @payper_yr_id and
> > > > a.pay_per_seq = @payper_seq and
> > > > not a.task_id = any(select b1.task_id from
> > > > emp_leave_balance as b1 where
> > > > b1.person_id = @personid and
> > > > b1.pay_per_yr_id = @payper_yr_id and
> > > > b1.pay_per_seq = @payper_seq)
> > > > end if
> > > > end loop PersonLoop;
> > > > close ThePerson;
> > > > if sqlcode <> 0 then
> > > > rollback work
> > > > else
> > > > commit work
> > > > end if
> > > > end
> > > >
> > > > Thanks
> > > > Newbegin
> > >
> > >
>
>


Newbegin Posted on 2004-03-04 22:11:28.0Z
Sender: 4d88.4047a93f.1804289383@sybase.com
From: Newbegin
Newsgroups: ianywhere.public.general
Subject: Re: Stored procedure error primary key is not unique
X-Mailer: WebNews to Mail Gateway v1.1s
Message-ID: <4047a9bf.4d98.846930886@sybase.com>
References: <40476f12.4913.846930886@sybase.com><40477693$1@forums-2-dub> <4047832d.5de0.846930886@sybase.com><40478c07$1@forums-1-dub><40479103.4c2d.846930886@sybase.com>
X-Original-NNTP-Posting-Host: 10.22.241.42
X-Original-Trace: 4 Mar 2004 14:12:15 -0800, 10.22.241.42
Lines: 132
X-Original-NNTP-Posting-Host: forums-2-dub.sybase.com
X-Original-Trace: 4 Mar 2004 14:10:02 -0800, forums-2-dub.sybase.com
NNTP-Posting-Host: forums-master.sybase.com
X-Original-NNTP-Posting-Host: forums-master.sybase.com
Date: 4 Mar 2004 14:11:28 -0800
X-Trace: forums-1-dub 1078438288 10.22.108.75 (4 Mar 2004 14:11:28 -0800)
X-Original-Trace: 4 Mar 2004 14:11:28 -0800, forums-master.sybase.com
X-Authenticated-User: ngsysop
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub ianywhere.public.general:2716
Article PK: 6189

The patch update we have is 9.0.1. Does this patch include
all the previous builds for version ASA V9?

> The row is not supposed to be added if the primary key
> already exists.
> > I'm not quite sure what the problem is then. If the
> > engine reports a primary key violation when you try to
> > insert a duplicate primary key, that's a good thing.
> >
> > --
> > Reg Domaratzki, Sybase iAnywhere Solutions
> > Sybase Certified Professional - Sybase ASA Developer
> > Version 8 Please reply only to the newsgroup
> >
> > iAnywhere Developer Community :
> > http://www.ianywhere.com/developer ASA Patches and EBFs
> > : http://downloads.sybase.com/swx/sdmain.stm
> > -> Choose SQL Anywhere Studio
> > -> Set "Platform Preview" and "Time Frame" to ALL
> >
> > <Newbegin> wrote in message
> > > news:4047832d.5de0.846930886@sybase.com... Yes the
> > > > error is accurate At the time you get the error, is
> > > it accurate? >
> > > > --
> > > > -----------------------------------------------
> > > > Robert Waywell
> > > > Sybase Adaptive Server Anywhere Developer - Version
> > > > 8 Sybase Certified Professional
> > > >
> > > > Sybase's iAnywhere Solutions
> > > >
> > > > Please respond ONLY to newsgroup
> > > >
> > > > EBF's and Patches: http://downloads.sybase.com
> > > > choose SQL Anywhere Studio >> change 'time frame'
> > > to all >
> > > > To Submit Bug Reports:
> > > >
> > >
> >
>
http://case-express.sybase.com/cx/cx.stm?starturl=casemessage.ssc?CASETYPE=B
> > > > ug
> > > >
> > > > SQL Anywhere Studio Supported Platforms and Support
> > > > Status http://my.sybase.com/detail?id=1002288
> > > >
> > > > Whitepapers, TechDocs, and bug fixes are all
> > > > available through the iAnywhere Developer Community
> > > > at www.ianywhere.com/developer
> > > >
> > > > <Newbegin> wrote in message
> > > > > news:40476f12.4913.846930886@sybase.com... We are
> > > > > using a cursor to loop through all the person_id
> > > > > and enter value in the emp_leave_balance table for
> > > > > which it does not have data. when we run the
> > > > > stored procedure for a single person the procedure
> > > > > works fine but when we include multiple person it
> > > > > does not work. We have ASA V9 with the patch up
> > > > date of 9.0.1 >
> > > > > This same stored procedure is working on seven.
> > > > >
> > > > > The following is the stored procedure
> > > > >
> > > > > ALTER PROCEDURE
> > > > > "resource_owner"."emp_leave_balance_sp"() begin
> > > > > declare @payper_yr_id id;
> > > > > declare @payper_seq seq;
> > > > > declare @personid id;
> > > > > declare err_notfound exception for sqlstate
> > > > > value '02000'; declare ThePerson dynamic scroll
> > > > > cursor for select person_id from person;
> > > > > select pay_per_yr_id,pay_per_seq into
> > > > > @payper_yr_id,@payper_seq from pay_per where
> > > > > start_dt <= TODAY(*) and end_dt >= TODAY(*);
> > > > > open ThePerson;
> > > > > PersonLoop: loop
> > > > > fetch next ThePerson into @personid;
> > > > > if sqlstate = err_notfound then
> > > > > leave PersonLoop
> > > > > end if;
> > > > > if @personid is not null then
> > > > > insert into emp_leave_balance(
> > > > > person_id,task_id,pay_per_yr_id,pay_per_seq
> > > > > ,leave_accrual_rate ,
> > > > >
> > > > > leave_freq_cd,adjustment,used_hour,pay_per_balance
> > > > > ,final_balance) select
> > > > > a.person_id,a.task_id,a.pay_per_yr_id
> > > > > ,a.pay_per_seq ,a.leave_accrual_rate ,
> > > > > a.leave_freq_cd,a.adjustment,a.used_hour
> > > > > , (select b.pay_per_balance from
> > > > > v_emp_pref_leave_payper_balance as b where
> > > > > a.person_id = b.person_id and
> > > > > a.task_id = b.task_id and
> > > > > a.pay_per_yr_id = b.pay_per_yr_id and
> > > > > a.pay_per_seq = b.pay_per_seq) as
> > > > > pay_per_balance,
> > > > > (select f.final_balance from
> > > > > v_emp_pref_leave_balance_final as f where
> > > > > a.person_id = f.person_id and
> > > > > a.task_id = f.task_id and
> > > > > a.pay_per_yr_id = f.pay_per_yr_id and
> > > > > a.pay_per_seq = f.pay_per_seq) as
> > > > > final_balance from
> > > > > v_emp_leave_rate_payper_adj as a where
> > > > > a.person_id = @personid and
> > > > > a.pay_per_yr_id = @payper_yr_id and
> > > > > a.pay_per_seq = @payper_seq and
> > > > > not a.task_id = any(select b1.task_id
> > > > > from emp_leave_balance as b1 where
> > > > > b1.person_id = @personid and
> > > > > b1.pay_per_yr_id = @payper_yr_id and
> > > > > b1.pay_per_seq = @payper_seq)
> > > > > end if
> > > > > end loop PersonLoop;
> > > > > close ThePerson;
> > > > > if sqlcode <> 0 then
> > > > > rollback work
> > > > > else
> > > > > commit work
> > > > > end if
> > > > > end
> > > > >
> > > > > Thanks
> > > > > Newbegin
> > > >
> > > >
> >
> >


Robert Waywell Posted on 2004-03-05 15:32:39.0Z
From: "Robert Waywell" <nospam_rwaywell@ianywhere.com>
Newsgroups: ianywhere.public.general
References: <40476f12.4913.846930886@sybase.com><40477693$1@forums-2-dub> <4047832d.5de0.846930886@sybase.com><40478c07$1@forums-1-dub><40479103.4c2d.846930886@sybase.com> <4047a9bf.4d98.846930886@sybase.com>
Subject: Re: Stored procedure error primary key is not unique
Lines: 166
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2600.0000
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2600.0000
X-Original-NNTP-Posting-Host: rwaywell-pc.sybase.com
Message-ID: <40489dc8$1@forums-2-dub>
X-Original-Trace: 5 Mar 2004 07:33:28 -0800, rwaywell-pc.sybase.com
X-Original-NNTP-Posting-Host: forums-2-dub.sybase.com
X-Original-Trace: 5 Mar 2004 07:31:09 -0800, forums-2-dub.sybase.com
NNTP-Posting-Host: forums-master.sybase.com
X-Original-NNTP-Posting-Host: forums-master.sybase.com
Date: 5 Mar 2004 07:32:39 -0800
X-Trace: forums-1-dub 1078500759 10.22.108.75 (5 Mar 2004 07:32:39 -0800)
X-Original-Trace: 5 Mar 2004 07:32:39 -0800, forums-master.sybase.com
X-Authenticated-User: ngsysop
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub ianywhere.public.general:2725
Article PK: 6195

There can be a 'timing gap' between an EBF on say the 9.0.0 line and the
9.0.1 GA. This is certainly true for any EBF's released on an older point
version after the GA of the next point version. However, fixes are made on
the current version first and then backported as appropriate to older
versions. This means that the current EBF of the most recent patch release
will include all previous fixes.

--
-----------------------------------------------
Robert Waywell
Sybase Adaptive Server Anywhere Developer - Version 8
Sybase Certified Professional

Sybase's iAnywhere Solutions

Please respond ONLY to newsgroup

EBF's and Patches: http://downloads.sybase.com
choose SQL Anywhere Studio >> change 'time frame' to all

To Submit Bug Reports:
http://case-express.sybase.com/cx/cx.stm?starturl=casemessage.ssc?CASETYPE=B
ug

SQL Anywhere Studio Supported Platforms and Support Status
http://my.sybase.com/detail?id=1002288

Whitepapers, TechDocs, and bug fixes are all available through the iAnywhere
Developer Community at www.ianywhere.com/developer

<Newbegin> wrote in message news:4047a9bf.4d98.846930886@sybase.com...
> The patch update we have is 9.0.1. Does this patch include
> all the previous builds for version ASA V9?
>
>
> > The row is not supposed to be added if the primary key
> > already exists.
> > > I'm not quite sure what the problem is then. If the
> > > engine reports a primary key violation when you try to
> > > insert a duplicate primary key, that's a good thing.
> > >
> > > --
> > > Reg Domaratzki, Sybase iAnywhere Solutions
> > > Sybase Certified Professional - Sybase ASA Developer
> > > Version 8 Please reply only to the newsgroup
> > >
> > > iAnywhere Developer Community :
> > > http://www.ianywhere.com/developer ASA Patches and EBFs
> > > : http://downloads.sybase.com/swx/sdmain.stm
> > > -> Choose SQL Anywhere Studio
> > > -> Set "Platform Preview" and "Time Frame" to ALL
> > >
> > > <Newbegin> wrote in message
> > > > news:4047832d.5de0.846930886@sybase.com... Yes the
> > > > > error is accurate At the time you get the error, is
> > > > it accurate? >
> > > > > --
> > > > > -----------------------------------------------
> > > > > Robert Waywell
> > > > > Sybase Adaptive Server Anywhere Developer - Version
> > > > > 8 Sybase Certified Professional
> > > > >
> > > > > Sybase's iAnywhere Solutions
> > > > >
> > > > > Please respond ONLY to newsgroup
> > > > >
> > > > > EBF's and Patches: http://downloads.sybase.com
> > > > > choose SQL Anywhere Studio >> change 'time frame'
> > > > to all >
> > > > > To Submit Bug Reports:
> > > > >
> > > >
> > >
> >
>
http://case-express.sybase.com/cx/cx.stm?starturl=casemessage.ssc?CASETYPE=B
> > > > > ug
> > > > >
> > > > > SQL Anywhere Studio Supported Platforms and Support
> > > > > Status http://my.sybase.com/detail?id=1002288
> > > > >
> > > > > Whitepapers, TechDocs, and bug fixes are all
> > > > > available through the iAnywhere Developer Community
> > > > > at www.ianywhere.com/developer
> > > > >
> > > > > <Newbegin> wrote in message
> > > > > > news:40476f12.4913.846930886@sybase.com... We are
> > > > > > using a cursor to loop through all the person_id
> > > > > > and enter value in the emp_leave_balance table for
> > > > > > which it does not have data. when we run the
> > > > > > stored procedure for a single person the procedure
> > > > > > works fine but when we include multiple person it
> > > > > > does not work. We have ASA V9 with the patch up
> > > > > date of 9.0.1 >
> > > > > > This same stored procedure is working on seven.
> > > > > >
> > > > > > The following is the stored procedure
> > > > > >
> > > > > > ALTER PROCEDURE
> > > > > > "resource_owner"."emp_leave_balance_sp"() begin
> > > > > > declare @payper_yr_id id;
> > > > > > declare @payper_seq seq;
> > > > > > declare @personid id;
> > > > > > declare err_notfound exception for sqlstate
> > > > > > value '02000'; declare ThePerson dynamic scroll
> > > > > > cursor for select person_id from person;
> > > > > > select pay_per_yr_id,pay_per_seq into
> > > > > > @payper_yr_id,@payper_seq from pay_per where
> > > > > > start_dt <= TODAY(*) and end_dt >= TODAY(*);
> > > > > > open ThePerson;
> > > > > > PersonLoop: loop
> > > > > > fetch next ThePerson into @personid;
> > > > > > if sqlstate = err_notfound then
> > > > > > leave PersonLoop
> > > > > > end if;
> > > > > > if @personid is not null then
> > > > > > insert into emp_leave_balance(
> > > > > > person_id,task_id,pay_per_yr_id,pay_per_seq
> > > > > > ,leave_accrual_rate ,
> > > > > >
> > > > > > leave_freq_cd,adjustment,used_hour,pay_per_balance
> > > > > > ,final_balance) select
> > > > > > a.person_id,a.task_id,a.pay_per_yr_id
> > > > > > ,a.pay_per_seq ,a.leave_accrual_rate ,
> > > > > > a.leave_freq_cd,a.adjustment,a.used_hour
> > > > > > , (select b.pay_per_balance from
> > > > > > v_emp_pref_leave_payper_balance as b where
> > > > > > a.person_id = b.person_id and
> > > > > > a.task_id = b.task_id and
> > > > > > a.pay_per_yr_id = b.pay_per_yr_id and
> > > > > > a.pay_per_seq = b.pay_per_seq) as
> > > > > > pay_per_balance,
> > > > > > (select f.final_balance from
> > > > > > v_emp_pref_leave_balance_final as f where
> > > > > > a.person_id = f.person_id and
> > > > > > a.task_id = f.task_id and
> > > > > > a.pay_per_yr_id = f.pay_per_yr_id and
> > > > > > a.pay_per_seq = f.pay_per_seq) as
> > > > > > final_balance from
> > > > > > v_emp_leave_rate_payper_adj as a where
> > > > > > a.person_id = @personid and
> > > > > > a.pay_per_yr_id = @payper_yr_id and
> > > > > > a.pay_per_seq = @payper_seq and
> > > > > > not a.task_id = any(select b1.task_id
> > > > > > from emp_leave_balance as b1 where
> > > > > > b1.person_id = @personid and
> > > > > > b1.pay_per_yr_id = @payper_yr_id and
> > > > > > b1.pay_per_seq = @payper_seq)
> > > > > > end if
> > > > > > end loop PersonLoop;
> > > > > > close ThePerson;
> > > > > > if sqlcode <> 0 then
> > > > > > rollback work
> > > > > > else
> > > > > > commit work
> > > > > > end if
> > > > > > end
> > > > > >
> > > > > > Thanks
> > > > > > Newbegin
> > > > >
> > > > >
> > >
> > >


Robert Waywell Posted on 2004-03-05 15:42:13.0Z
From: "Robert Waywell" <nospam_rwaywell@ianywhere.com>
Newsgroups: ianywhere.public.general
References: <40476f12.4913.846930886@sybase.com><40477693$1@forums-2-dub> <4047832d.5de0.846930886@sybase.com><40478c07$1@forums-1-dub> <40479103.4c2d.846930886@sybase.com>
Subject: Re: Stored procedure error primary key is not unique
Lines: 162
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2600.0000
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2600.0000
X-Original-NNTP-Posting-Host: rwaywell-pc.sybase.com
Message-ID: <4048a005$1@forums-2-dub>
X-Original-Trace: 5 Mar 2004 07:43:01 -0800, rwaywell-pc.sybase.com
X-Original-NNTP-Posting-Host: forums-2-dub.sybase.com
X-Original-Trace: 5 Mar 2004 07:40:43 -0800, forums-2-dub.sybase.com
NNTP-Posting-Host: forums-master.sybase.com
X-Original-NNTP-Posting-Host: forums-master.sybase.com
Date: 5 Mar 2004 07:42:13 -0800
X-Trace: forums-1-dub 1078501333 10.22.108.75 (5 Mar 2004 07:42:13 -0800)
X-Original-Trace: 5 Mar 2004 07:42:13 -0800, forums-master.sybase.com
X-Authenticated-User: ngsysop
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub ianywhere.public.general:2728
Article PK: 6201

And the row isn't being added, you are getting the duplicate primary key
error.

I did take a quick glance at your procedure and I don't see where you are
checking that this is a new primary key value before attempting to insert
the record. It was only a quick glance so I may well have missed where you
are doing this.

--
-----------------------------------------------
Robert Waywell
Sybase Adaptive Server Anywhere Developer - Version 8
Sybase Certified Professional

Sybase's iAnywhere Solutions

Please respond ONLY to newsgroup

EBF's and Patches: http://downloads.sybase.com
choose SQL Anywhere Studio >> change 'time frame' to all

To Submit Bug Reports:
http://case-express.sybase.com/cx/cx.stm?starturl=casemessage.ssc?CASETYPE=B
ug

SQL Anywhere Studio Supported Platforms and Support Status
http://my.sybase.com/detail?id=1002288

Whitepapers, TechDocs, and bug fixes are all available through the iAnywhere
Developer Community at www.ianywhere.com/developer

<Newbegin> wrote in message news:40479103.4c2d.846930886@sybase.com...
> The row is not supposed to be added if the primary key
> already exists.
> > I'm not quite sure what the problem is then. If the
> > engine reports a primary key violation when you try to
> > insert a duplicate primary key, that's a good thing.
> >
> > --
> > Reg Domaratzki, Sybase iAnywhere Solutions
> > Sybase Certified Professional - Sybase ASA Developer
> > Version 8 Please reply only to the newsgroup
> >
> > iAnywhere Developer Community :
> > http://www.ianywhere.com/developer ASA Patches and EBFs :
> > http://downloads.sybase.com/swx/sdmain.stm
> > -> Choose SQL Anywhere Studio
> > -> Set "Platform Preview" and "Time Frame" to ALL
> >
> > <Newbegin> wrote in message
> > > news:4047832d.5de0.846930886@sybase.com... Yes the error
> > > > is accurate At the time you get the error, is it
> > > accurate? >
> > > > --
> > > > -----------------------------------------------
> > > > Robert Waywell
> > > > Sybase Adaptive Server Anywhere Developer - Version 8
> > > > Sybase Certified Professional
> > > >
> > > > Sybase's iAnywhere Solutions
> > > >
> > > > Please respond ONLY to newsgroup
> > > >
> > > > EBF's and Patches: http://downloads.sybase.com
> > > > choose SQL Anywhere Studio >> change 'time frame' to
> > > all >
> > > > To Submit Bug Reports:
> > > >
> > >
> >
>
http://case-express.sybase.com/cx/cx.stm?starturl=casemessage.ssc?CASETYPE=B
> > > > ug
> > > >
> > > > SQL Anywhere Studio Supported Platforms and Support
> > > > Status http://my.sybase.com/detail?id=1002288
> > > >
> > > > Whitepapers, TechDocs, and bug fixes are all available
> > > > through the iAnywhere Developer Community at
> > > > www.ianywhere.com/developer
> > > >
> > > > <Newbegin> wrote in message
> > > > > news:40476f12.4913.846930886@sybase.com... We are
> > > > > using a cursor to loop through all the person_id and
> > > > > enter value in the emp_leave_balance table for which
> > > > > it does not have data. when we run the stored
> > > > > procedure for a single person the procedure works
> > > > > fine but when we include multiple person it does not
> > > > > work. We have ASA V9 with the patch up date of 9.0.1
> > > > >
> > > > > This same stored procedure is working on seven.
> > > > >
> > > > > The following is the stored procedure
> > > > >
> > > > > ALTER PROCEDURE
> > > > > "resource_owner"."emp_leave_balance_sp"() begin
> > > > > declare @payper_yr_id id;
> > > > > declare @payper_seq seq;
> > > > > declare @personid id;
> > > > > declare err_notfound exception for sqlstate value
> > > > > '02000'; declare ThePerson dynamic scroll cursor
> > > > > for select person_id from person;
> > > > > select pay_per_yr_id,pay_per_seq into
> > > > > @payper_yr_id,@payper_seq from pay_per where
> > > > > start_dt <= TODAY(*) and end_dt >= TODAY(*);
> > > > > open ThePerson;
> > > > > PersonLoop: loop
> > > > > fetch next ThePerson into @personid;
> > > > > if sqlstate = err_notfound then
> > > > > leave PersonLoop
> > > > > end if;
> > > > > if @personid is not null then
> > > > > insert into emp_leave_balance(
> > > > > person_id,task_id,pay_per_yr_id,pay_per_seq
> > > > > ,leave_accrual_rate ,
> > > > >
> > > > > leave_freq_cd,adjustment,used_hour,pay_per_balance
> > > > > ,final_balance) select
> > > > > a.person_id,a.task_id,a.pay_per_yr_id,a.pay_per_seq
> > > > > ,a.leave_accrual_rate ,
> > > > > a.leave_freq_cd,a.adjustment,a.used_hour,
> > > > > (select b.pay_per_balance from
> > > > > v_emp_pref_leave_payper_balance as b where
> > > > > a.person_id = b.person_id and
> > > > > a.task_id = b.task_id and
> > > > > a.pay_per_yr_id = b.pay_per_yr_id and
> > > > > a.pay_per_seq = b.pay_per_seq) as
> > > > > pay_per_balance,
> > > > > (select f.final_balance from
> > > > > v_emp_pref_leave_balance_final as f where
> > > > > a.person_id = f.person_id and
> > > > > a.task_id = f.task_id and
> > > > > a.pay_per_yr_id = f.pay_per_yr_id and
> > > > > a.pay_per_seq = f.pay_per_seq) as
> > > > > final_balance from
> > > > > v_emp_leave_rate_payper_adj as a where
> > > > > a.person_id = @personid and
> > > > > a.pay_per_yr_id = @payper_yr_id and
> > > > > a.pay_per_seq = @payper_seq and
> > > > > not a.task_id = any(select b1.task_id from
> > > > > emp_leave_balance as b1 where
> > > > > b1.person_id = @personid and
> > > > > b1.pay_per_yr_id = @payper_yr_id and
> > > > > b1.pay_per_seq = @payper_seq)
> > > > > end if
> > > > > end loop PersonLoop;
> > > > > close ThePerson;
> > > > > if sqlcode <> 0 then
> > > > > rollback work
> > > > > else
> > > > > commit work
> > > > > end if
> > > > > end
> > > > >
> > > > > Thanks
> > > > > Newbegin
> > > >
> > > >
> >
> >


newbegind Posted on 2004-03-05 16:21:34.0Z
Sender: 57b3.4048a7ee.1804289383@sybase.com
From: newbegind
Newsgroups: ianywhere.public.general
Subject: Re: Stored procedure error primary key is not unique
X-Mailer: WebNews to Mail Gateway v1.1s
Message-ID: <4048a93c.57c2.846930886@sybase.com>
References: <40476f12.4913.846930886@sybase.com><40477693$1@forums-2-dub> <4047832d.5de0.846930886@sybase.com><40478c07$1@forums-1-dub> <40479103.4c2d.846930886@sybase.com><4048a005$1@forums-2-dub>
X-Original-NNTP-Posting-Host: 10.22.241.42
X-Original-Trace: 5 Mar 2004 08:22:20 -0800, 10.22.241.42
Lines: 177
X-Original-NNTP-Posting-Host: forums-2-dub.sybase.com
X-Original-Trace: 5 Mar 2004 08:20:00 -0800, forums-2-dub.sybase.com
NNTP-Posting-Host: forums-master.sybase.com
X-Original-NNTP-Posting-Host: forums-master.sybase.com
Date: 5 Mar 2004 08:21:34 -0800
X-Trace: forums-1-dub 1078503694 10.22.108.75 (5 Mar 2004 08:21:34 -0800)
X-Original-Trace: 5 Mar 2004 08:21:34 -0800, forums-master.sybase.com
X-Authenticated-User: ngsysop
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub ianywhere.public.general:2730
Article PK: 6203

The last part of the insert statement checks for the primary
key value.
The following is the check part in the stored procedure.

any(select b1.task_id from
emp_leave_balance as b1 where
b1.person_id = @personid and
b1.pay_per_yr_id = @payper.yr.id and
b1.pay_per_seq = @payper.seq)

Newbegin

> And the row isn't being added, you are getting the
> duplicate primary key error.
>
> I did take a quick glance at your procedure and I don't
> see where you are checking that this is a new primary key
> value before attempting to insert the record. It was only
> a quick glance so I may well have missed where you are
> doing this.
>
> --
> -----------------------------------------------
> Robert Waywell
> Sybase Adaptive Server Anywhere Developer - Version 8
> Sybase Certified Professional
>
> Sybase's iAnywhere Solutions
>
> Please respond ONLY to newsgroup
>
> EBF's and Patches: http://downloads.sybase.com
> choose SQL Anywhere Studio >> change 'time frame' to all
>
> To Submit Bug Reports:
>
http://case-express.sybase.com/cx/cx.stm?starturl=casemessage.ssc?CASETYPE=B
> ug
>
> SQL Anywhere Studio Supported Platforms and Support Status
> http://my.sybase.com/detail?id=1002288
>
> Whitepapers, TechDocs, and bug fixes are all available
> through the iAnywhere Developer Community at
> www.ianywhere.com/developer
>
> <Newbegin> wrote in message
> > news:40479103.4c2d.846930886@sybase.com... The row is
> > not supposed to be added if the primary key already
> > > exists. I'm not quite sure what the problem is then.
> > > If the engine reports a primary key violation when you
> > > try to insert a duplicate primary key, that's a good
> > thing. >
> > > --
> > > Reg Domaratzki, Sybase iAnywhere Solutions
> > > Sybase Certified Professional - Sybase ASA Developer
> > > Version 8 Please reply only to the newsgroup
> > >
> > > iAnywhere Developer Community :
> > > http://www.ianywhere.com/developer ASA Patches and
> > > EBFs : http://downloads.sybase.com/swx/sdmain.stm
> > > -> Choose SQL Anywhere Studio
> > > -> Set "Platform Preview" and "Time Frame" to ALL
> > >
> > > <Newbegin> wrote in message
> > > > news:4047832d.5de0.846930886@sybase.com... Yes the
> > > > > error is accurate At the time you get the error,
> > > > is it accurate? >
> > > > > --
> > > > > -----------------------------------------------
> > > > > Robert Waywell
> > > > > Sybase Adaptive Server Anywhere Developer -
> > > > > Version 8 Sybase Certified Professional
> > > > >
> > > > > Sybase's iAnywhere Solutions
> > > > >
> > > > > Please respond ONLY to newsgroup
> > > > >
> > > > > EBF's and Patches: http://downloads.sybase.com
> > > > > choose SQL Anywhere Studio >> change 'time frame'
> > > > to all >
> > > > > To Submit Bug Reports:
> > > > >
> > > >
> > >
> >
>
http://case-express.sybase.com/cx/cx.stm?starturl=casemessage.ssc?CASETYPE=B
> > > > > ug
> > > > >
> > > > > SQL Anywhere Studio Supported Platforms and
> > > > > Support Status
> > > > http://my.sybase.com/detail?id=1002288 >
> > > > > Whitepapers, TechDocs, and bug fixes are all
> > > > > available through the iAnywhere Developer
> > > > > Community at www.ianywhere.com/developer
> > > > >
> > > > > <Newbegin> wrote in message
> > > > > > news:40476f12.4913.846930886@sybase.com... We
> > > > > > are using a cursor to loop through all the
> > > > > > person_id and enter value in the
> > > > > > emp_leave_balance table for which it does not
> > > > > > have data. when we run the stored procedure for
> > > > > > a single person the procedure works fine but
> > > > > > when we include multiple person it does not
> > > > > work. We have ASA V9 with the patch up date of
> > > > > > 9.0.1 > This same stored procedure is working on
> > > > > seven. >
> > > > > > The following is the stored procedure
> > > > > >
> > > > > > ALTER PROCEDURE
> > > > > > "resource_owner"."emp_leave_balance_sp"() begin
> > > > > > declare @payper_yr_id id;
> > > > > > declare @payper_seq seq;
> > > > > > declare @personid id;
> > > > > > declare err_notfound exception for sqlstate
> > > > > > value '02000'; declare ThePerson dynamic
> > > > > > scroll cursor for select person_id from person;
> > > > > > select pay_per_yr_id,pay_per_seq into
> > > > > > @payper_yr_id,@payper_seq from pay_per where
> > > > > > start_dt <= TODAY(*) and end_dt >= TODAY(*);
> > > > > > open ThePerson;
> > > > > > PersonLoop: loop
> > > > > > fetch next ThePerson into @personid;
> > > > > > if sqlstate = err_notfound then
> > > > > > leave PersonLoop
> > > > > > end if;
> > > > > > if @personid is not null then
> > > > > > insert into emp_leave_balance(
> > > > > > person_id,task_id,pay_per_yr_id,pay_per_seq
> > > > > > ,leave_accrual_rate ,
> > > > > >
> > > > > > leave_freq_cd,adjustment,used_hour
> > > > > > ,pay_per_balance ,final_balance) select
> > > > > > a.person_id,a.task_id,a.pay_per_yr_id
> > > > > > ,a.pay_per_seq ,a.leave_accrual_rate ,
> > > > > > a.leave_freq_cd,a.adjustment
> > > > > > ,a.used_hour, (select
> > > > > > b.pay_per_balance from
> > > > > > v_emp_pref_leave_payper_balance as b
> > > > > > where a.person_id = b.person_id and
> > > > > > a.task_id = b.task_id and
> > > > > > a.pay_per_yr_id = b.pay_per_yr_id
> > > > > > and a.pay_per_seq = b.pay_per_seq) as
> > > > > > pay_per_balance, (select
> > > > > > f.final_balance from
> > > > > > v_emp_pref_leave_balance_final as f
> > > > > > where a.person_id = f.person_id and
> > > > > > a.task_id = f.task_id and
> > > > > > a.pay_per_yr_id = f.pay_per_yr_id
> > > > > > and a.pay_per_seq = f.pay_per_seq) as
> > > > > > final_balance from
> > > > > > v_emp_leave_rate_payper_adj as a where
> > > > > > a.person_id = @personid and
> > > > > > a.pay_per_yr_id = @payper_yr_id and
> > > > > > a.pay_per_seq = @payper_seq and not
> > > > > > a.task_id = any(select b1.task_id from
> > > > > > emp_leave_balance as b1 where
> > > > > > b1.person_id = @personid and
> > > > > > b1.pay_per_yr_id = @payper_yr_id and
> > > > > > b1.pay_per_seq = @payper_seq) end if
> > > > > > end loop PersonLoop;
> > > > > > close ThePerson;
> > > > > > if sqlcode <> 0 then
> > > > > > rollback work
> > > > > > else
> > > > > > commit work
> > > > > > end if
> > > > > > end
> > > > > >
> > > > > > Thanks
> > > > > > Newbegin
> > > > >
> > > > >
> > >
> > >
>
>


Glenn Paulley Posted on 2004-03-05 17:32:17.0Z
Newsgroups: ianywhere.public.general
Subject: Re: Stored procedure error primary key is not unique
From: Glenn Paulley <paulley@ianywhere.com>
References: <4048a93c.57c2.846930886@sybase.com>
Organization: iAnywhere Solutions
Message-ID: <Xns94A37F4B39FD2paulleyianywherecom@10.22.241.106>
User-Agent: Xnews/5.04.25
NNTP-Posting-Host: paulley-t41.sybase.com
X-Original-NNTP-Posting-Host: paulley-t41.sybase.com
Date: 5 Mar 2004 09:32:17 -0800
X-Trace: forums-1-dub 1078507937 172.31.141.249 (5 Mar 2004 09:32:17 -0800)
X-Original-Trace: 5 Mar 2004 09:32:17 -0800, paulley-t41.sybase.com
Lines: 217
X-Authenticated-User: techsupp
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub ianywhere.public.general:2731
Article PK: 6204

Despite your claim that this stored procedure works in ASA 7, the NOT ANY
subquery you've coded below will not, by itself, eliminate possible
duplicates from being inserted into the emp_leave_balance table.

Since this is an INSERT INTO .. FROM SELECT statement, to ensure
consistent semantics the result of the SELECT is completely materialized
*before* any rows are inserted. Consequently the subquery in the SELECT
can only refer to rows in the emp_leave_balance table that exist *prior*
to the execution of the statement. If the result of the SELECT *itself*
contains duplicates then the subsequent INSERT will fail with a primary
key violation.

All ASA releases, including ASA 7, do precisely the same processing when
an INSERT FROM SELECT statement is self-referencing.

Handling this type of scenario is what the ON EXISTING clause is intended
for. When using this clause, the NOT ANY subquery can be eliminated and
the entire request will be much more efficient.

Glenn

newbegind wrote in news:4048a93c.57c2.846930886@sybase.com:

> The last part of the insert statement checks for the primary
> key value.
> The following is the check part in the stored procedure.
>
> any(select b1.task_id from
> emp_leave_balance as b1 where
> b1.person_id = @personid and
> b1.pay_per_yr_id = @payper.yr.id and
> b1.pay_per_seq = @payper.seq)
>
> Newbegin
>> And the row isn't being added, you are getting the
>> duplicate primary key error.
>>
>> I did take a quick glance at your procedure and I don't
>> see where you are checking that this is a new primary key
>> value before attempting to insert the record. It was only
>> a quick glance so I may well have missed where you are
>> doing this.
>>
>> --
>> -----------------------------------------------
>> Robert Waywell
>> Sybase Adaptive Server Anywhere Developer - Version 8
>> Sybase Certified Professional
>>
>> Sybase's iAnywhere Solutions
>>
>> Please respond ONLY to newsgroup
>>
>> EBF's and Patches: http://downloads.sybase.com
>> choose SQL Anywhere Studio >> change 'time frame' to all
>>
>> To Submit Bug Reports:
>>
> http://case-express.sybase.com/cx/cx.stm?starturl=casemessage.ssc?CASET
> YPE=B
>> ug
>>
>> SQL Anywhere Studio Supported Platforms and Support Status
>> http://my.sybase.com/detail?id=1002288
>>
>> Whitepapers, TechDocs, and bug fixes are all available
>> through the iAnywhere Developer Community at
>> www.ianywhere.com/developer
>>
>> <Newbegin> wrote in message
>> > news:40479103.4c2d.846930886@sybase.com... The row is
>> > not supposed to be added if the primary key already
>> > > exists. I'm not quite sure what the problem is then.
>> > > If the engine reports a primary key violation when you
>> > > try to insert a duplicate primary key, that's a good
>> > thing. >
>> > > --
>> > > Reg Domaratzki, Sybase iAnywhere Solutions
>> > > Sybase Certified Professional - Sybase ASA Developer
>> > > Version 8 Please reply only to the newsgroup
>> > >
>> > > iAnywhere Developer Community :
>> > > http://www.ianywhere.com/developer ASA Patches and
>> > > EBFs : http://downloads.sybase.com/swx/sdmain.stm
>> > > -> Choose SQL Anywhere Studio
>> > > -> Set "Platform Preview" and "Time Frame" to ALL
>> > >
>> > > <Newbegin> wrote in message
>> > > > news:4047832d.5de0.846930886@sybase.com... Yes the
>> > > > > error is accurate At the time you get the error,
>> > > > is it accurate? >
>> > > > > --
>> > > > > -----------------------------------------------
>> > > > > Robert Waywell
>> > > > > Sybase Adaptive Server Anywhere Developer -
>> > > > > Version 8 Sybase Certified Professional
>> > > > >
>> > > > > Sybase's iAnywhere Solutions
>> > > > >
>> > > > > Please respond ONLY to newsgroup
>> > > > >
>> > > > > EBF's and Patches: http://downloads.sybase.com
>> > > > > choose SQL Anywhere Studio >> change 'time frame'
>> > > > to all >
>> > > > > To Submit Bug Reports:
>> > > > >
>> > > >
>> > >
>> >
>>
> http://case-express.sybase.com/cx/cx.stm?starturl=casemessage.ssc?CASET
> YPE=B
>> > > > > ug
>> > > > >
>> > > > > SQL Anywhere Studio Supported Platforms and
>> > > > > Support Status
>> > > > http://my.sybase.com/detail?id=1002288 >
>> > > > > Whitepapers, TechDocs, and bug fixes are all
>> > > > > available through the iAnywhere Developer
>> > > > > Community at www.ianywhere.com/developer
>> > > > >
>> > > > > <Newbegin> wrote in message
>> > > > > > news:40476f12.4913.846930886@sybase.com... We
>> > > > > > are using a cursor to loop through all the
>> > > > > > person_id and enter value in the
>> > > > > > emp_leave_balance table for which it does not
>> > > > > > have data. when we run the stored procedure for
>> > > > > > a single person the procedure works fine but
>> > > > > > when we include multiple person it does not
>> > > > > work. We have ASA V9 with the patch up date of
>> > > > > > 9.0.1 > This same stored procedure is working on
>> > > > > seven. >
>> > > > > > The following is the stored procedure
>> > > > > >
>> > > > > > ALTER PROCEDURE
>> > > > > > "resource_owner"."emp_leave_balance_sp"() begin
>> > > > > > declare @payper_yr_id id;
>> > > > > > declare @payper_seq seq;
>> > > > > > declare @personid id;
>> > > > > > declare err_notfound exception for sqlstate
>> > > > > > value '02000'; declare ThePerson dynamic
>> > > > > > scroll cursor for select person_id from person;
>> > > > > > select pay_per_yr_id,pay_per_seq into
>> > > > > > @payper_yr_id,@payper_seq from pay_per where
>> > > > > > start_dt <= TODAY(*) and end_dt >= TODAY(*);
>> > > > > > open ThePerson;
>> > > > > > PersonLoop: loop
>> > > > > > fetch next ThePerson into @personid;
>> > > > > > if sqlstate = err_notfound then
>> > > > > > leave PersonLoop
>> > > > > > end if;
>> > > > > > if @personid is not null then
>> > > > > > insert into emp_leave_balance(
>> > > > > > person_id,task_id,pay_per_yr_id,pay_per_seq
>> > > > > > ,leave_accrual_rate ,
>> > > > > >
>> > > > > > leave_freq_cd,adjustment,used_hour
>> > > > > > ,pay_per_balance ,final_balance) select
>> > > > > > a.person_id,a.task_id,a.pay_per_yr_id
>> > > > > > ,a.pay_per_seq ,a.leave_accrual_rate ,
>> > > > > > a.leave_freq_cd,a.adjustment
>> > > > > > ,a.used_hour, (select
>> > > > > > b.pay_per_balance from
>> > > > > > v_emp_pref_leave_payper_balance as b
>> > > > > > where a.person_id = b.person_id and
>> > > > > > a.task_id = b.task_id and
>> > > > > > a.pay_per_yr_id = b.pay_per_yr_id
>> > > > > > and a.pay_per_seq = b.pay_per_seq) as
>> > > > > > pay_per_balance, (select
>> > > > > > f.final_balance from
>> > > > > > v_emp_pref_leave_balance_final as f
>> > > > > > where a.person_id = f.person_id and
>> > > > > > a.task_id = f.task_id and
>> > > > > > a.pay_per_yr_id = f.pay_per_yr_id
>> > > > > > and a.pay_per_seq = f.pay_per_seq) as
>> > > > > > final_balance from
>> > > > > > v_emp_leave_rate_payper_adj as a where
>> > > > > > a.person_id = @personid and
>> > > > > > a.pay_per_yr_id = @payper_yr_id and
>> > > > > > a.pay_per_seq = @payper_seq and not
>> > > > > > a.task_id = any(select b1.task_id from
>> > > > > > emp_leave_balance as b1 where
>> > > > > > b1.person_id = @personid and
>> > > > > > b1.pay_per_yr_id = @payper_yr_id and
>> > > > > > b1.pay_per_seq = @payper_seq) end if
>> > > > > > end loop PersonLoop;
>> > > > > > close ThePerson;
>> > > > > > if sqlcode <> 0 then
>> > > > > > rollback work
>> > > > > > else
>> > > > > > commit work
>> > > > > > end if
>> > > > > > end
>> > > > > >
>> > > > > > Thanks
>> > > > > > Newbegin
>> > > > >
>> > > > >
>> > >
>> > >
>>
>>

--
Glenn Paulley
Research and Development Manager, Query Processing
iAnywhere Solutions Engineering

EBF's and Patches: http://downloads.sybase.com
choose SQL Anywhere Studio >> change 'time frame' to all

To Submit Bug Reports: http://casexpress.sybase.com/cx/cx.stm

SQL Anywhere Studio Supported Platforms and Support Status
http://my.sybase.com/detail?id=1002288


Newbegin Posted on 2004-03-05 18:21:20.0Z
Sender: 6c58.4048c4b3.1804289383@sybase.com
From: Newbegin
Newsgroups: ianywhere.public.general
Subject: Re: Stored procedure error primary key is not unique
X-Mailer: WebNews to Mail Gateway v1.1s
Message-ID: <4048c520.6c60.846930886@sybase.com>
References: <4048a93c.57c2.846930886@sybase.com><Xns94A37F4B39FD2paulleyianywherecom@10.22.241.106>
NNTP-Posting-Host: 10.22.241.41
X-Original-NNTP-Posting-Host: 10.22.241.41
Date: 5 Mar 2004 10:21:20 -0800
X-Trace: forums-1-dub 1078510880 10.22.241.41 (5 Mar 2004 10:21:20 -0800)
X-Original-Trace: 5 Mar 2004 10:21:20 -0800, 10.22.241.41
Lines: 230
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub ianywhere.public.general:2732
Article PK: 6205

Thanks for your help. It worked.
Newbegin

> Despite your claim that this stored procedure works in ASA
> 7, the NOT ANY subquery you've coded below will not, by
> itself, eliminate possible duplicates from being inserted
> into the emp_leave_balance table.
>
> Since this is an INSERT INTO .. FROM SELECT statement, to
> ensure consistent semantics the result of the SELECT is
> completely materialized *before* any rows are inserted.
> Consequently the subquery in the SELECT can only refer to
> rows in the emp_leave_balance table that exist *prior* to
> the execution of the statement. If the result of the
> SELECT *itself* contains duplicates then the subsequent
> INSERT will fail with a primary key violation.
>
> All ASA releases, including ASA 7, do precisely the same
> processing when an INSERT FROM SELECT statement is
> self-referencing.
>
> Handling this type of scenario is what the ON EXISTING
> clause is intended for. When using this clause, the NOT
> ANY subquery can be eliminated and the entire request
> will be much more efficient.
>
> Glenn
>
> newbegind wrote in
> news:4048a93c.57c2.846930886@sybase.com:
>
> > The last part of the insert statement checks for the
> > primary key value.
> > The following is the check part in the stored procedure.
> >
> > any(select b1.task_id from
> > emp_leave_balance as b1 where
> > b1.person_id = @personid and
> > b1.pay_per_yr_id = @payper.yr.id and
> > b1.pay_per_seq = @payper.seq)
> >
> > Newbegin
> >> And the row isn't being added, you are getting the
> >> duplicate primary key error.
> >>
> >> I did take a quick glance at your procedure and I don't
> >> see where you are checking that this is a new primary
> key >> value before attempting to insert the record. It
> was only >> a quick glance so I may well have missed where
> you are >> doing this.
> >>
> >> --
> >> -----------------------------------------------
> >> Robert Waywell
> >> Sybase Adaptive Server Anywhere Developer - Version 8
> >> Sybase Certified Professional
> >>
> >> Sybase's iAnywhere Solutions
> >>
> >> Please respond ONLY to newsgroup
> >>
> >> EBF's and Patches: http://downloads.sybase.com
> >> choose SQL Anywhere Studio >> change 'time frame' to
> all >>
> >> To Submit Bug Reports:
> >>
> >
>
http://case-express.sybase.com/cx/cx.stm?starturl=casemessage.ssc?CASET
> > YPE=B
> >> ug
> >>
> >> SQL Anywhere Studio Supported Platforms and Support
> Status >> http://my.sybase.com/detail?id=1002288
> >>
> >> Whitepapers, TechDocs, and bug fixes are all available
> >> through the iAnywhere Developer Community at
> >> www.ianywhere.com/developer
> >>
> >> <Newbegin> wrote in message
> >> > news:40479103.4c2d.846930886@sybase.com... The row is
> >> > not supposed to be added if the primary key already
> >> > > exists. I'm not quite sure what the problem is
> then. >> > > If the engine reports a primary key violation
> when you >> > > try to insert a duplicate primary key,
> that's a good >> > thing. >
> >> > > --
> >> > > Reg Domaratzki, Sybase iAnywhere Solutions
> >> > > Sybase Certified Professional - Sybase ASA
> Developer >> > > Version 8 Please reply only to the
> newsgroup >> > >
> >> > > iAnywhere Developer Community :
> >> > > http://www.ianywhere.com/developer ASA Patches and
> >> > > EBFs : http://downloads.sybase.com/swx/sdmain.stm
> >> > > -> Choose SQL Anywhere Studio
> >> > > -> Set "Platform Preview" and "Time Frame" to
> ALL >> > >
> >> > > <Newbegin> wrote in message
> >> > > > news:4047832d.5de0.846930886@sybase.com... Yes
> the >> > > > > error is accurate At the time you get the
> error, >> > > > is it accurate? >
> >> > > > > --
> >> > > > > -----------------------------------------------
> >> > > > > Robert Waywell
> >> > > > > Sybase Adaptive Server Anywhere Developer -
> >> > > > > Version 8 Sybase Certified Professional
> >> > > > >
> >> > > > > Sybase's iAnywhere Solutions
> >> > > > >
> >> > > > > Please respond ONLY to newsgroup
> >> > > > >
> >> > > > > EBF's and Patches: http://downloads.sybase.com
> >> > > > > choose SQL Anywhere Studio >> change 'time
> frame' >> > > > to all >
> >> > > > > To Submit Bug Reports:
> >> > > > >
> >> > > >
> >> > >
> >> >
> >>
> >
>
http://case-express.sybase.com/cx/cx.stm?starturl=casemessage.ssc?CASET
> > YPE=B
> >> > > > > ug
> >> > > > >
> >> > > > > SQL Anywhere Studio Supported Platforms and
> >> > > > > Support Status
> >> > > > http://my.sybase.com/detail?id=1002288 >
> >> > > > > Whitepapers, TechDocs, and bug fixes are all
> >> > > > > available through the iAnywhere Developer
> >> > > > > Community at www.ianywhere.com/developer
> >> > > > >
> >> > > > > <Newbegin> wrote in message
> >> > > > > > news:40476f12.4913.846930886@sybase.com... We
> >> > > > > > are using a cursor to loop through all the
> >> > > > > > person_id and enter value in the
> >> > > > > > emp_leave_balance table for which it does not
> >> > > > > > have data. when we run the stored procedure
> for >> > > > > > a single person the procedure works fine
> but >> > > > > > when we include multiple person it does
> not >> > > > > work. We have ASA V9 with the patch up date
> of >> > > > > > 9.0.1 > This same stored procedure is
> working on >> > > > > seven. >
> >> > > > > > The following is the stored procedure
> >> > > > > >
> >> > > > > > ALTER PROCEDURE
> >> > > > > > "resource_owner"."emp_leave_balance_sp"()
> begin >> > > > > > declare @payper_yr_id id;
> >> > > > > > declare @payper_seq seq;
> >> > > > > > declare @personid id;
> >> > > > > > declare err_notfound exception for sqlstate
> >> > > > > > value '02000'; declare ThePerson dynamic
> >> > > > > > scroll cursor for select person_id from
> person; >> > > > > > select pay_per_yr_id,pay_per_seq
> into >> > > > > > @payper_yr_id,@payper_seq from pay_per
> where >> > > > > > start_dt <= TODAY(*) and end_dt >=
> TODAY(*); >> > > > > > open ThePerson;
> >> > > > > > PersonLoop: loop
> >> > > > > > fetch next ThePerson into @personid;
> >> > > > > > if sqlstate = err_notfound then
> >> > > > > > leave PersonLoop
> >> > > > > > end if;
> >> > > > > > if @personid is not null then
> >> > > > > > insert into emp_leave_balance(
> >> > > > > > person_id,task_id,pay_per_yr_id,pay_per_seq
> >> > > > > > ,leave_accrual_rate ,
> >> > > > > >
> >> > > > > > leave_freq_cd,adjustment,used_hour
> >> > > > > > ,pay_per_balance ,final_balance)
> select >> > > > > > a.person_id,a.task_id,a.pay_per_yr_id
> >> > > > > > ,a.pay_per_seq ,a.leave_accrual_rate ,
> >> > > > > > a.leave_freq_cd,a.adjustment
> >> > > > > > ,a.used_hour, (select
> >> > > > > > b.pay_per_balance from
> >> > > > > > v_emp_pref_leave_payper_balance
> as b >> > > > > > where a.person_id =
> b.person_id and >> > > > > > a.task_id =
> b.task_id and >> > > > > > a.pay_per_yr_id =
> b.pay_per_yr_id >> > > > > > and a.pay_per_seq =
> b.pay_per_seq) as >> > > > > > pay_per_balance,
> (select >> > > > > > f.final_balance from
> >> > > > > > v_emp_pref_leave_balance_final as
> f >> > > > > > where a.person_id = f.person_id
> and >> > > > > > a.task_id = f.task_id and
> >> > > > > > a.pay_per_yr_id = f.pay_per_yr_id
> >> > > > > > and a.pay_per_seq = f.pay_per_seq) as
> >> > > > > > final_balance from
> >> > > > > > v_emp_leave_rate_payper_adj as a
> where >> > > > > > a.person_id = @personid and
> >> > > > > > a.pay_per_yr_id = @payper_yr_id and
> >> > > > > > a.pay_per_seq = @payper_seq and not
> >> > > > > > a.task_id = any(select b1.task_id from
> >> > > > > > emp_leave_balance as b1 where
> >> > > > > > b1.person_id = @personid and
> >> > > > > > b1.pay_per_yr_id = @payper_yr_id
> and >> > > > > > b1.pay_per_seq = @payper_seq) end if
> >> > > > > > end loop PersonLoop;
> >> > > > > > close ThePerson;
> >> > > > > > if sqlcode <> 0 then
> >> > > > > > rollback work
> >> > > > > > else
> >> > > > > > commit work
> >> > > > > > end if
> >> > > > > > end
> >> > > > > >
> >> > > > > > Thanks
> >> > > > > > Newbegin
> >> > > > >
> >> > > > >
> >> > >
> >> > >
> >>
> >>
>
>
>
> --
> Glenn Paulley
> Research and Development Manager, Query Processing
> iAnywhere Solutions Engineering
>
> EBF's and Patches: http://downloads.sybase.com
> choose SQL Anywhere Studio >> change 'time frame' to all
>
> To Submit Bug Reports:
> http://casexpress.sybase.com/cx/cx.stm
>
> SQL Anywhere Studio Supported Platforms and Support Status
> http://my.sybase.com/detail?id=1002288


Prabir Posted on 2004-03-04 20:46:30.0Z
From: "Prabir" <pchaudhuri@esc20.net>
Newsgroups: ianywhere.public.general
References: <40476f12.4913.846930886@sybase.com>
Subject: Re: Stored procedure error primary key is not unique
Lines: 84
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2800.1158
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1165
NNTP-Posting-Host: 168.37.200.1
X-Original-NNTP-Posting-Host: 168.37.200.1
Message-ID: <404795a6@forums-1-dub>
Date: 4 Mar 2004 12:46:30 -0800
X-Trace: forums-1-dub 1078433190 168.37.200.1 (4 Mar 2004 12:46:30 -0800)
X-Original-Trace: 4 Mar 2004 12:46:30 -0800, 168.37.200.1
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub ianywhere.public.general:2709
Article PK: 6184

what are the primary keys for ur table
emp_leave_balance ?



Prabir

<Newbegin> wrote in message news:40476f12.4913.846930886@sybase.com...
> We are using a cursor to loop through all the person_id and
> enter value in the emp_leave_balance table for which it does
> not have data. when we run the stored procedure for a
> single person the procedure works fine but when we include
> multiple person it does not work.
> We have ASA V9
> with the patch up date of 9.0.1
>
> This same stored procedure is working on seven.
>
> The following is the stored procedure
>
> ALTER PROCEDURE "resource_owner"."emp_leave_balance_sp"()
> begin
> declare @payper_yr_id id;
> declare @payper_seq seq;
> declare @personid id;
> declare err_notfound exception for sqlstate value '02000';
> declare ThePerson dynamic scroll cursor for select
> person_id from person;
> select pay_per_yr_id,pay_per_seq into
> @payper_yr_id,@payper_seq from pay_per where
> start_dt <= TODAY(*) and end_dt >= TODAY(*);
> open ThePerson;
> PersonLoop: loop
> fetch next ThePerson into @personid;
> if sqlstate = err_notfound then
> leave PersonLoop
> end if;
> if @personid is not null then
> insert into emp_leave_balance(
> person_id,task_id,pay_per_yr_id,pay_per_seq,leave_accrual_rate
> ,
>
> leave_freq_cd,adjustment,used_hour,pay_per_balance,final_balance)
> select
> a.person_id,a.task_id,a.pay_per_yr_id,a.pay_per_seq,a.leave_accrual_rate
> ,
> a.leave_freq_cd,a.adjustment,a.used_hour,
> (select b.pay_per_balance from
> v_emp_pref_leave_payper_balance as b where
> a.person_id = b.person_id and
> a.task_id = b.task_id and
> a.pay_per_yr_id = b.pay_per_yr_id and
> a.pay_per_seq = b.pay_per_seq) as
> pay_per_balance,
> (select f.final_balance from
> v_emp_pref_leave_balance_final as f where
> a.person_id = f.person_id and
> a.task_id = f.task_id and
> a.pay_per_yr_id = f.pay_per_yr_id and
> a.pay_per_seq = f.pay_per_seq) as final_balance
> from
> v_emp_leave_rate_payper_adj as a where
> a.person_id = @personid and
> a.pay_per_yr_id = @payper_yr_id and
> a.pay_per_seq = @payper_seq and
> not a.task_id = any(select b1.task_id from
> emp_leave_balance as b1 where
> b1.person_id = @personid and
> b1.pay_per_yr_id = @payper_yr_id and
> b1.pay_per_seq = @payper_seq)
> end if
> end loop PersonLoop;
> close ThePerson;
> if sqlcode <> 0 then
> rollback work
> else
> commit work
> end if
> end
>
> Thanks
> Newbegin


Newbegin Posted on 2004-03-04 20:49:04.0Z
Sender: 5f46.404795be.1804289383@sybase.com
From: newbegin
Newsgroups: ianywhere.public.general
Subject: Re: Stored procedure error primary key is not unique
X-Mailer: WebNews to Mail Gateway v1.1s
Message-ID: <40479640.5f4c.846930886@sybase.com>
References: <40476f12.4913.846930886@sybase.com><404795a6@forums-1-dub>
NNTP-Posting-Host: 10.22.241.41
X-Original-NNTP-Posting-Host: 10.22.241.41
Date: 4 Mar 2004 12:49:04 -0800
X-Trace: forums-1-dub 1078433344 10.22.241.41 (4 Mar 2004 12:49:04 -0800)
X-Original-Trace: 4 Mar 2004 12:49:04 -0800, 10.22.241.41
Lines: 87
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub ianywhere.public.general:2710
Article PK: 6183

These are the primary keys - person_id, task_id,
pay_per_yr_id, pay_per_seq

> what are the primary keys for ur table
> emp_leave_balance ?
>
>
>
> Prabir
>
> <Newbegin> wrote in message
> > news:40476f12.4913.846930886@sybase.com... We are using
> > a cursor to loop through all the person_id and enter
> > value in the emp_leave_balance table for which it does
> > not have data. when we run the stored procedure for a
> > single person the procedure works fine but when we
> > include multiple person it does not work. We have ASA V9
> > with the patch up date of 9.0.1
> >
> > This same stored procedure is working on seven.
> >
> > The following is the stored procedure
> >
> > ALTER PROCEDURE
> > "resource_owner"."emp_leave_balance_sp"() begin
> > declare @payper_yr_id id;
> > declare @payper_seq seq;
> > declare @personid id;
> > declare err_notfound exception for sqlstate value
> > '02000'; declare ThePerson dynamic scroll cursor for
> > select person_id from person;
> > select pay_per_yr_id,pay_per_seq into
> > @payper_yr_id,@payper_seq from pay_per where
> > start_dt <= TODAY(*) and end_dt >= TODAY(*);
> > open ThePerson;
> > PersonLoop: loop
> > fetch next ThePerson into @personid;
> > if sqlstate = err_notfound then
> > leave PersonLoop
> > end if;
> > if @personid is not null then
> > insert into emp_leave_balance(
> > person_id,task_id,pay_per_yr_id,pay_per_seq
> > ,leave_accrual_rate ,
> >
> > leave_freq_cd,adjustment,used_hour,pay_per_balance
> > ,final_balance) select
> > a.person_id,a.task_id,a.pay_per_yr_id,a.pay_per_seq
> > ,a.leave_accrual_rate ,
> > a.leave_freq_cd,a.adjustment,a.used_hour,
> > (select b.pay_per_balance from
> > v_emp_pref_leave_payper_balance as b where
> > a.person_id = b.person_id and
> > a.task_id = b.task_id and
> > a.pay_per_yr_id = b.pay_per_yr_id and
> > a.pay_per_seq = b.pay_per_seq) as
> > pay_per_balance,
> > (select f.final_balance from
> > v_emp_pref_leave_balance_final as f where
> > a.person_id = f.person_id and
> > a.task_id = f.task_id and
> > a.pay_per_yr_id = f.pay_per_yr_id and
> > a.pay_per_seq = f.pay_per_seq) as
> > final_balance from
> > v_emp_leave_rate_payper_adj as a where
> > a.person_id = @personid and
> > a.pay_per_yr_id = @payper_yr_id and
> > a.pay_per_seq = @payper_seq and
> > not a.task_id = any(select b1.task_id from
> > emp_leave_balance as b1 where
> > b1.person_id = @personid and
> > b1.pay_per_yr_id = @payper_yr_id and
> > b1.pay_per_seq = @payper_seq)
> > end if
> > end loop PersonLoop;
> > close ThePerson;
> > if sqlcode <> 0 then
> > rollback work
> > else
> > commit work
> > end if
> > end
> >
> > Thanks
> > Newbegin
>
>


Prabir Posted on 2004-03-04 21:02:58.0Z
From: "Prabir" <pchaudhuri@esc20.net>
Newsgroups: ianywhere.public.general
References: <40476f12.4913.846930886@sybase.com><404795a6@forums-1-dub> <40479640.5f4c.846930886@sybase.com>
Subject: Re: Stored procedure error primary key is not unique
Lines: 95
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2800.1158
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1165
X-Original-NNTP-Posting-Host: 168.37.200.1
Message-ID: <404799b0@forums-2-dub>
X-Original-Trace: 4 Mar 2004 13:03:44 -0800, 168.37.200.1
X-Original-NNTP-Posting-Host: forums-2-dub.sybase.com
X-Original-Trace: 4 Mar 2004 13:01:32 -0800, forums-2-dub.sybase.com
NNTP-Posting-Host: forums-master.sybase.com
X-Original-NNTP-Posting-Host: forums-master.sybase.com
Date: 4 Mar 2004 13:02:58 -0800
X-Trace: forums-1-dub 1078434178 10.22.108.75 (4 Mar 2004 13:02:58 -0800)
X-Original-Trace: 4 Mar 2004 13:02:58 -0800, forums-master.sybase.com
X-Authenticated-User: ngsysop
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub ianywhere.public.general:2711
Article PK: 6186

R u sure that your "v_emp_leave_rate_payper_adj" table also have the same
primary key combination ?

Prabir

<newbegin> wrote in message news:40479640.5f4c.846930886@sybase.com...
> These are the primary keys - person_id, task_id,
> pay_per_yr_id, pay_per_seq
>
> > what are the primary keys for ur table
> > emp_leave_balance ?
> >
> >
> >
> > Prabir
> >
> > <Newbegin> wrote in message
> > > news:40476f12.4913.846930886@sybase.com... We are using
> > > a cursor to loop through all the person_id and enter
> > > value in the emp_leave_balance table for which it does
> > > not have data. when we run the stored procedure for a
> > > single person the procedure works fine but when we
> > > include multiple person it does not work. We have ASA V9
> > > with the patch up date of 9.0.1
> > >
> > > This same stored procedure is working on seven.
> > >
> > > The following is the stored procedure
> > >
> > > ALTER PROCEDURE
> > > "resource_owner"."emp_leave_balance_sp"() begin
> > > declare @payper_yr_id id;
> > > declare @payper_seq seq;
> > > declare @personid id;
> > > declare err_notfound exception for sqlstate value
> > > '02000'; declare ThePerson dynamic scroll cursor for
> > > select person_id from person;
> > > select pay_per_yr_id,pay_per_seq into
> > > @payper_yr_id,@payper_seq from pay_per where
> > > start_dt <= TODAY(*) and end_dt >= TODAY(*);
> > > open ThePerson;
> > > PersonLoop: loop
> > > fetch next ThePerson into @personid;
> > > if sqlstate = err_notfound then
> > > leave PersonLoop
> > > end if;
> > > if @personid is not null then
> > > insert into emp_leave_balance(
> > > person_id,task_id,pay_per_yr_id,pay_per_seq
> > > ,leave_accrual_rate ,
> > >
> > > leave_freq_cd,adjustment,used_hour,pay_per_balance
> > > ,final_balance) select
> > > a.person_id,a.task_id,a.pay_per_yr_id,a.pay_per_seq
> > > ,a.leave_accrual_rate ,
> > > a.leave_freq_cd,a.adjustment,a.used_hour,
> > > (select b.pay_per_balance from
> > > v_emp_pref_leave_payper_balance as b where
> > > a.person_id = b.person_id and
> > > a.task_id = b.task_id and
> > > a.pay_per_yr_id = b.pay_per_yr_id and
> > > a.pay_per_seq = b.pay_per_seq) as
> > > pay_per_balance,
> > > (select f.final_balance from
> > > v_emp_pref_leave_balance_final as f where
> > > a.person_id = f.person_id and
> > > a.task_id = f.task_id and
> > > a.pay_per_yr_id = f.pay_per_yr_id and
> > > a.pay_per_seq = f.pay_per_seq) as
> > > final_balance from
> > > v_emp_leave_rate_payper_adj as a where
> > > a.person_id = @personid and
> > > a.pay_per_yr_id = @payper_yr_id and
> > > a.pay_per_seq = @payper_seq and
> > > not a.task_id = any(select b1.task_id from
> > > emp_leave_balance as b1 where
> > > b1.person_id = @personid and
> > > b1.pay_per_yr_id = @payper_yr_id and
> > > b1.pay_per_seq = @payper_seq)
> > > end if
> > > end loop PersonLoop;
> > > close ThePerson;
> > > if sqlcode <> 0 then
> > > rollback work
> > > else
> > > commit work
> > > end if
> > > end
> > >
> > > Thanks
> > > Newbegin
> >
> >


Newbegin Posted on 2004-03-04 21:13:55.0Z
Sender: 5fb7.40479bf0.1804289383@sybase.com
From: Newbegin
Newsgroups: ianywhere.public.general
Subject: Re: Stored procedure error primary key is not unique
X-Mailer: WebNews to Mail Gateway v1.1s
Message-ID: <40479c13.5fbc.846930886@sybase.com>
References: <40476f12.4913.846930886@sybase.com><404795a6@forums-1-dub> <40479640.5f4c.846930886@sybase.com><404799b0@forums-2-dub>
NNTP-Posting-Host: 10.22.241.41
X-Original-NNTP-Posting-Host: 10.22.241.41
Date: 4 Mar 2004 13:13:55 -0800
X-Trace: forums-1-dub 1078434835 10.22.241.41 (4 Mar 2004 13:13:55 -0800)
X-Original-Trace: 4 Mar 2004 13:13:55 -0800, 10.22.241.41
Lines: 107
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub ianywhere.public.general:2712
Article PK: 6182

This stored procedure works fine for individual employee
records but when I run it with select all employee records I
have a problem. It should give the same error when I run
this procedure for individual records.


v_emp_leave_rate_payper_adj is a complicated view based on
several tables but the out put of the view is person_id,
task_id pay_per_yr_id, pay_per_seq

> R u sure that your "v_emp_leave_rate_payper_adj" table
> also have the same primary key combination ?
>
> Prabir
>
> <newbegin> wrote in message
> > news:40479640.5f4c.846930886@sybase.com... These are the
> > primary keys - person_id, task_id, pay_per_yr_id,
> pay_per_seq >
> > > what are the primary keys for ur table
> > > emp_leave_balance ?
> > >
> > >
> > >
> > > Prabir
> > >
> > > <Newbegin> wrote in message
> > > > news:40476f12.4913.846930886@sybase.com... We are
> > > > using a cursor to loop through all the person_id and
> > > > enter value in the emp_leave_balance table for which
> > > > it does not have data. when we run the stored
> > > > procedure for a single person the procedure works
> > > > fine but when we include multiple person it does not
> > > > work. We have ASA V9 with the patch up date of 9.0.1
> > > >
> > > > This same stored procedure is working on seven.
> > > >
> > > > The following is the stored procedure
> > > >
> > > > ALTER PROCEDURE
> > > > "resource_owner"."emp_leave_balance_sp"() begin
> > > > declare @payper_yr_id id;
> > > > declare @payper_seq seq;
> > > > declare @personid id;
> > > > declare err_notfound exception for sqlstate value
> > > > '02000'; declare ThePerson dynamic scroll cursor
> > > > for select person_id from person;
> > > > select pay_per_yr_id,pay_per_seq into
> > > > @payper_yr_id,@payper_seq from pay_per where
> > > > start_dt <= TODAY(*) and end_dt >= TODAY(*);
> > > > open ThePerson;
> > > > PersonLoop: loop
> > > > fetch next ThePerson into @personid;
> > > > if sqlstate = err_notfound then
> > > > leave PersonLoop
> > > > end if;
> > > > if @personid is not null then
> > > > insert into emp_leave_balance(
> > > > person_id,task_id,pay_per_yr_id,pay_per_seq
> > > > ,leave_accrual_rate ,
> > > >
> > > > leave_freq_cd,adjustment,used_hour,pay_per_balance
> > > > ,final_balance) select
> > > > a.person_id,a.task_id,a.pay_per_yr_id,a.pay_per_seq
> > > > ,a.leave_accrual_rate ,
> > > > a.leave_freq_cd,a.adjustment,a.used_hour,
> > > > (select b.pay_per_balance from
> > > > v_emp_pref_leave_payper_balance as b where
> > > > a.person_id = b.person_id and
> > > > a.task_id = b.task_id and
> > > > a.pay_per_yr_id = b.pay_per_yr_id and
> > > > a.pay_per_seq = b.pay_per_seq) as
> > > > pay_per_balance,
> > > > (select f.final_balance from
> > > > v_emp_pref_leave_balance_final as f where
> > > > a.person_id = f.person_id and
> > > > a.task_id = f.task_id and
> > > > a.pay_per_yr_id = f.pay_per_yr_id and
> > > > a.pay_per_seq = f.pay_per_seq) as
> > > > final_balance from
> > > > v_emp_leave_rate_payper_adj as a where
> > > > a.person_id = @personid and
> > > > a.pay_per_yr_id = @payper_yr_id and
> > > > a.pay_per_seq = @payper_seq and
> > > > not a.task_id = any(select b1.task_id from
> > > > emp_leave_balance as b1 where
> > > > b1.person_id = @personid and
> > > > b1.pay_per_yr_id = @payper_yr_id and
> > > > b1.pay_per_seq = @payper_seq)
> > > > end if
> > > > end loop PersonLoop;
> > > > close ThePerson;
> > > > if sqlcode <> 0 then
> > > > rollback work
> > > > else
> > > > commit work
> > > > end if
> > > > end
> > > >
> > > > Thanks
> > > > Newbegin
> > >
> > >
>
>


Prabir Posted on 2004-03-04 21:18:41.0Z
From: "Prabir" <pchaudhuri@esc20.net>
Newsgroups: ianywhere.public.general
References: <40476f12.4913.846930886@sybase.com><404795a6@forums-1-dub> <40479640.5f4c.846930886@sybase.com><404799b0@forums-2-dub> <40479c13.5fbc.846930886@sybase.com>
Subject: Re: Stored procedure error primary key is not unique
Lines: 114
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2800.1158
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1165
X-Original-NNTP-Posting-Host: 168.37.200.1
Message-ID: <40479d5e@forums-2-dub>
X-Original-Trace: 4 Mar 2004 13:19:26 -0800, 168.37.200.1
X-Original-NNTP-Posting-Host: forums-2-dub.sybase.com
X-Original-Trace: 4 Mar 2004 13:17:14 -0800, forums-2-dub.sybase.com
NNTP-Posting-Host: forums-master.sybase.com
X-Original-NNTP-Posting-Host: forums-master.sybase.com
Date: 4 Mar 2004 13:18:41 -0800
X-Trace: forums-1-dub 1078435121 10.22.108.75 (4 Mar 2004 13:18:41 -0800)
X-Original-Trace: 4 Mar 2004 13:18:41 -0800, forums-master.sybase.com
X-Authenticated-User: ngsysop
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub ianywhere.public.general:2713
Article PK: 6185

I think you should check the data on your view. It must contains 2 same
records for that key combination.

Prabir

<Newbegin> wrote in message news:40479c13.5fbc.846930886@sybase.com...
> This stored procedure works fine for individual employee
> records but when I run it with select all employee records I
> have a problem. It should give the same error when I run
> this procedure for individual records.
>
>
> v_emp_leave_rate_payper_adj is a complicated view based on
> several tables but the out put of the view is person_id,
> task_id pay_per_yr_id, pay_per_seq
>
>
>
> > R u sure that your "v_emp_leave_rate_payper_adj" table
> > also have the same primary key combination ?
> >
> > Prabir
> >
> > <newbegin> wrote in message
> > > news:40479640.5f4c.846930886@sybase.com... These are the
> > > primary keys - person_id, task_id, pay_per_yr_id,
> > pay_per_seq >
> > > > what are the primary keys for ur table
> > > > emp_leave_balance ?
> > > >
> > > >
> > > >
> > > > Prabir
> > > >
> > > > <Newbegin> wrote in message
> > > > > news:40476f12.4913.846930886@sybase.com... We are
> > > > > using a cursor to loop through all the person_id and
> > > > > enter value in the emp_leave_balance table for which
> > > > > it does not have data. when we run the stored
> > > > > procedure for a single person the procedure works
> > > > > fine but when we include multiple person it does not
> > > > > work. We have ASA V9 with the patch up date of 9.0.1
> > > > >
> > > > > This same stored procedure is working on seven.
> > > > >
> > > > > The following is the stored procedure
> > > > >
> > > > > ALTER PROCEDURE
> > > > > "resource_owner"."emp_leave_balance_sp"() begin
> > > > > declare @payper_yr_id id;
> > > > > declare @payper_seq seq;
> > > > > declare @personid id;
> > > > > declare err_notfound exception for sqlstate value
> > > > > '02000'; declare ThePerson dynamic scroll cursor
> > > > > for select person_id from person;
> > > > > select pay_per_yr_id,pay_per_seq into
> > > > > @payper_yr_id,@payper_seq from pay_per where
> > > > > start_dt <= TODAY(*) and end_dt >= TODAY(*);
> > > > > open ThePerson;
> > > > > PersonLoop: loop
> > > > > fetch next ThePerson into @personid;
> > > > > if sqlstate = err_notfound then
> > > > > leave PersonLoop
> > > > > end if;
> > > > > if @personid is not null then
> > > > > insert into emp_leave_balance(
> > > > > person_id,task_id,pay_per_yr_id,pay_per_seq
> > > > > ,leave_accrual_rate ,
> > > > >
> > > > > leave_freq_cd,adjustment,used_hour,pay_per_balance
> > > > > ,final_balance) select
> > > > > a.person_id,a.task_id,a.pay_per_yr_id,a.pay_per_seq
> > > > > ,a.leave_accrual_rate ,
> > > > > a.leave_freq_cd,a.adjustment,a.used_hour,
> > > > > (select b.pay_per_balance from
> > > > > v_emp_pref_leave_payper_balance as b where
> > > > > a.person_id = b.person_id and
> > > > > a.task_id = b.task_id and
> > > > > a.pay_per_yr_id = b.pay_per_yr_id and
> > > > > a.pay_per_seq = b.pay_per_seq) as
> > > > > pay_per_balance,
> > > > > (select f.final_balance from
> > > > > v_emp_pref_leave_balance_final as f where
> > > > > a.person_id = f.person_id and
> > > > > a.task_id = f.task_id and
> > > > > a.pay_per_yr_id = f.pay_per_yr_id and
> > > > > a.pay_per_seq = f.pay_per_seq) as
> > > > > final_balance from
> > > > > v_emp_leave_rate_payper_adj as a where
> > > > > a.person_id = @personid and
> > > > > a.pay_per_yr_id = @payper_yr_id and
> > > > > a.pay_per_seq = @payper_seq and
> > > > > not a.task_id = any(select b1.task_id from
> > > > > emp_leave_balance as b1 where
> > > > > b1.person_id = @personid and
> > > > > b1.pay_per_yr_id = @payper_yr_id and
> > > > > b1.pay_per_seq = @payper_seq)
> > > > > end if
> > > > > end loop PersonLoop;
> > > > > close ThePerson;
> > > > > if sqlcode <> 0 then
> > > > > rollback work
> > > > > else
> > > > > commit work
> > > > > end if
> > > > > end
> > > > >
> > > > > Thanks
> > > > > Newbegin
> > > >
> > > >
> >
> >


Prabir Posted on 2004-03-04 21:25:19.0Z
From: "Prabir" <pchaudhuri@esc20.net>
Newsgroups: ianywhere.public.general
References: <40476f12.4913.846930886@sybase.com><404795a6@forums-1-dub> <40479640.5f4c.846930886@sybase.com><404799b0@forums-2-dub> <40479c13.5fbc.846930886@sybase.com>
Subject: Re: Stored procedure error primary key is not unique
Lines: 118
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2800.1158
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1165
X-Original-NNTP-Posting-Host: 168.37.200.1
Message-ID: <40479eef@forums-2-dub>
X-Original-Trace: 4 Mar 2004 13:26:07 -0800, 168.37.200.1
X-Original-NNTP-Posting-Host: forums-2-dub.sybase.com
X-Original-Trace: 4 Mar 2004 13:23:54 -0800, forums-2-dub.sybase.com
NNTP-Posting-Host: forums-master.sybase.com
X-Original-NNTP-Posting-Host: forums-master.sybase.com
Date: 4 Mar 2004 13:25:19 -0800
X-Trace: forums-1-dub 1078435520 10.22.108.75 (4 Mar 2004 13:25:20 -0800)
X-Original-Trace: 4 Mar 2004 13:25:20 -0800, forums-master.sybase.com
X-Authenticated-User: ngsysop
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub ianywhere.public.general:2714
Article PK: 6187

One thing u can do :

drop the primary keys for the table "emp_leave_balance" and run the
procedure. After running that procedure, check the duplicate primary key
combination records on "emp_leave_balance" and find the person id creating
the problem.

Prabir

<Newbegin> wrote in message news:40479c13.5fbc.846930886@sybase.com...
> This stored procedure works fine for individual employee
> records but when I run it with select all employee records I
> have a problem. It should give the same error when I run
> this procedure for individual records.
>
>
> v_emp_leave_rate_payper_adj is a complicated view based on
> several tables but the out put of the view is person_id,
> task_id pay_per_yr_id, pay_per_seq
>
>
>
> > R u sure that your "v_emp_leave_rate_payper_adj" table
> > also have the same primary key combination ?
> >
> > Prabir
> >
> > <newbegin> wrote in message
> > > news:40479640.5f4c.846930886@sybase.com... These are the
> > > primary keys - person_id, task_id, pay_per_yr_id,
> > pay_per_seq >
> > > > what are the primary keys for ur table
> > > > emp_leave_balance ?
> > > >
> > > >
> > > >
> > > > Prabir
> > > >
> > > > <Newbegin> wrote in message
> > > > > news:40476f12.4913.846930886@sybase.com... We are
> > > > > using a cursor to loop through all the person_id and
> > > > > enter value in the emp_leave_balance table for which
> > > > > it does not have data. when we run the stored
> > > > > procedure for a single person the procedure works
> > > > > fine but when we include multiple person it does not
> > > > > work. We have ASA V9 with the patch up date of 9.0.1
> > > > >
> > > > > This same stored procedure is working on seven.
> > > > >
> > > > > The following is the stored procedure
> > > > >
> > > > > ALTER PROCEDURE
> > > > > "resource_owner"."emp_leave_balance_sp"() begin
> > > > > declare @payper_yr_id id;
> > > > > declare @payper_seq seq;
> > > > > declare @personid id;
> > > > > declare err_notfound exception for sqlstate value
> > > > > '02000'; declare ThePerson dynamic scroll cursor
> > > > > for select person_id from person;
> > > > > select pay_per_yr_id,pay_per_seq into
> > > > > @payper_yr_id,@payper_seq from pay_per where
> > > > > start_dt <= TODAY(*) and end_dt >= TODAY(*);
> > > > > open ThePerson;
> > > > > PersonLoop: loop
> > > > > fetch next ThePerson into @personid;
> > > > > if sqlstate = err_notfound then
> > > > > leave PersonLoop
> > > > > end if;
> > > > > if @personid is not null then
> > > > > insert into emp_leave_balance(
> > > > > person_id,task_id,pay_per_yr_id,pay_per_seq
> > > > > ,leave_accrual_rate ,
> > > > >
> > > > > leave_freq_cd,adjustment,used_hour,pay_per_balance
> > > > > ,final_balance) select
> > > > > a.person_id,a.task_id,a.pay_per_yr_id,a.pay_per_seq
> > > > > ,a.leave_accrual_rate ,
> > > > > a.leave_freq_cd,a.adjustment,a.used_hour,
> > > > > (select b.pay_per_balance from
> > > > > v_emp_pref_leave_payper_balance as b where
> > > > > a.person_id = b.person_id and
> > > > > a.task_id = b.task_id and
> > > > > a.pay_per_yr_id = b.pay_per_yr_id and
> > > > > a.pay_per_seq = b.pay_per_seq) as
> > > > > pay_per_balance,
> > > > > (select f.final_balance from
> > > > > v_emp_pref_leave_balance_final as f where
> > > > > a.person_id = f.person_id and
> > > > > a.task_id = f.task_id and
> > > > > a.pay_per_yr_id = f.pay_per_yr_id and
> > > > > a.pay_per_seq = f.pay_per_seq) as
> > > > > final_balance from
> > > > > v_emp_leave_rate_payper_adj as a where
> > > > > a.person_id = @personid and
> > > > > a.pay_per_yr_id = @payper_yr_id and
> > > > > a.pay_per_seq = @payper_seq and
> > > > > not a.task_id = any(select b1.task_id from
> > > > > emp_leave_balance as b1 where
> > > > > b1.person_id = @personid and
> > > > > b1.pay_per_yr_id = @payper_yr_id and
> > > > > b1.pay_per_seq = @payper_seq)
> > > > > end if
> > > > > end loop PersonLoop;
> > > > > close ThePerson;
> > > > > if sqlcode <> 0 then
> > > > > rollback work
> > > > > else
> > > > > commit work
> > > > > end if
> > > > > end
> > > > >
> > > > > Thanks
> > > > > Newbegin
> > > >
> > > >
> >
> >


Newbegin Posted on 2004-03-04 22:08:49.0Z
Sender: 4d68.4047a7b8.1804289383@sybase.com
From: Newbegin
Newsgroups: ianywhere.public.general
Subject: Re: Stored procedure error primary key is not unique
X-Mailer: WebNews to Mail Gateway v1.1s
Message-ID: <4047a920.4d85.846930886@sybase.com>
References: <40476f12.4913.846930886@sybase.com><404795a6@forums-1-dub> <40479640.5f4c.846930886@sybase.com><404799b0@forums-2-dub> <40479c13.5fbc.846930886@sybase.com><40479eef@forums-2-dub>
X-Original-NNTP-Posting-Host: 10.22.241.42
X-Original-Trace: 4 Mar 2004 14:09:36 -0800, 10.22.241.42
Lines: 135
X-Original-NNTP-Posting-Host: forums-2-dub.sybase.com
X-Original-Trace: 4 Mar 2004 14:07:23 -0800, forums-2-dub.sybase.com
NNTP-Posting-Host: forums-master.sybase.com
X-Original-NNTP-Posting-Host: forums-master.sybase.com
Date: 4 Mar 2004 14:08:49 -0800
X-Trace: forums-1-dub 1078438129 10.22.108.75 (4 Mar 2004 14:08:49 -0800)
X-Original-Trace: 4 Mar 2004 14:08:49 -0800, forums-master.sybase.com
X-Authenticated-User: ngsysop
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub ianywhere.public.general:2715
Article PK: 6188

We ran the v_emp_pref_leave_balance_final there is no
duplicate key on this select
and there are no duplicate keys on
v_emp_leave_rate_payper_adj
and there are no duplicate keys on
v_emp_pref_leave_payper_balance either.

We still don't know why this same stored procedure works for
individual employee with out select all


We don't want to insert duplicate row. The insert should
only happen in the instance of if the primary key values are
missing. If we drop the primary key we would have duplicate
row.

> One thing u can do :
>
> drop the primary keys for the table "emp_leave_balance"
> and run the procedure. After running that procedure, check
> the duplicate primary key combination records on
> "emp_leave_balance" and find the person id creating the
> problem.
>
> Prabir
> <Newbegin> wrote in message
> > news:40479c13.5fbc.846930886@sybase.com... This stored
> > procedure works fine for individual employee records but
> > when I run it with select all employee records I have a
> > problem. It should give the same error when I run this
> procedure for individual records. >
> >
> > v_emp_leave_rate_payper_adj is a complicated view based
> > on several tables but the out put of the view is
> > person_id, task_id pay_per_yr_id, pay_per_seq
> >
> >
> >
> > > R u sure that your "v_emp_leave_rate_payper_adj" table
> > > also have the same primary key combination ?
> > >
> > > Prabir
> > >
> > > <newbegin> wrote in message
> > > > news:40479640.5f4c.846930886@sybase.com... These are
> > > > the primary keys - person_id, task_id, pay_per_yr_id
> > > , pay_per_seq >
> > > > > what are the primary keys for ur table
> > > > > emp_leave_balance ?
> > > > >
> > > > >
> > > > >
> > > > > Prabir
> > > > >
> > > > > <Newbegin> wrote in message
> > > > > > news:40476f12.4913.846930886@sybase.com... We
> > > > > > are using a cursor to loop through all the
> > > > > > person_id and enter value in the
> > > > > > emp_leave_balance table for which it does not
> > > > > > have data. when we run the stored procedure for
> > > > > > a single person the procedure works fine but
> > > > > > when we include multiple person it does not
> > > > > work. We have ASA V9 with the patch up date of
> > > > > > 9.0.1 > This same stored procedure is working on
> > > > > seven. >
> > > > > > The following is the stored procedure
> > > > > >
> > > > > > ALTER PROCEDURE
> > > > > > "resource_owner"."emp_leave_balance_sp"() begin
> > > > > > declare @payper_yr_id id;
> > > > > > declare @payper_seq seq;
> > > > > > declare @personid id;
> > > > > > declare err_notfound exception for sqlstate
> > > > > > value '02000'; declare ThePerson dynamic
> > > > > > scroll cursor for select person_id from person;
> > > > > > select pay_per_yr_id,pay_per_seq into
> > > > > > @payper_yr_id,@payper_seq from pay_per where
> > > > > > start_dt <= TODAY(*) and end_dt >= TODAY(*);
> > > > > > open ThePerson;
> > > > > > PersonLoop: loop
> > > > > > fetch next ThePerson into @personid;
> > > > > > if sqlstate = err_notfound then
> > > > > > leave PersonLoop
> > > > > > end if;
> > > > > > if @personid is not null then
> > > > > > insert into emp_leave_balance(
> > > > > > person_id,task_id,pay_per_yr_id,pay_per_seq
> > > > > > ,leave_accrual_rate ,
> > > > > >
> > > > > > leave_freq_cd,adjustment,used_hour
> > > > > > ,pay_per_balance ,final_balance) select
> > > > > > a.person_id,a.task_id,a.pay_per_yr_id
> > > > > > ,a.pay_per_seq ,a.leave_accrual_rate ,
> > > > > > a.leave_freq_cd,a.adjustment
> > > > > > ,a.used_hour, (select
> > > > > > b.pay_per_balance from
> > > > > > v_emp_pref_leave_payper_balance as b
> > > > > > where a.person_id = b.person_id and
> > > > > > a.task_id = b.task_id and
> > > > > > a.pay_per_yr_id = b.pay_per_yr_id
> > > > > > and a.pay_per_seq = b.pay_per_seq) as
> > > > > > pay_per_balance, (select
> > > > > > f.final_balance from
> > > > > > v_emp_pref_leave_balance_final as f
> > > > > > where a.person_id = f.person_id and
> > > > > > a.task_id = f.task_id and
> > > > > > a.pay_per_yr_id = f.pay_per_yr_id
> > > > > > and a.pay_per_seq = f.pay_per_seq) as
> > > > > > final_balance from
> > > > > > v_emp_leave_rate_payper_adj as a where
> > > > > > a.person_id = @personid and
> > > > > > a.pay_per_yr_id = @payper_yr_id and
> > > > > > a.pay_per_seq = @payper_seq and not
> > > > > > a.task_id = any(select b1.task_id from
> > > > > > emp_leave_balance as b1 where
> > > > > > b1.person_id = @personid and
> > > > > > b1.pay_per_yr_id = @payper_yr_id and
> > > > > > b1.pay_per_seq = @payper_seq) end if
> > > > > > end loop PersonLoop;
> > > > > > close ThePerson;
> > > > > > if sqlcode <> 0 then
> > > > > > rollback work
> > > > > > else
> > > > > > commit work
> > > > > > end if
> > > > > > end
> > > > > >
> > > > > > Thanks
> > > > > > Newbegin
> > > > >
> > > > >
> > >
> > >
>
>


Prabir Posted on 2004-03-04 22:13:20.0Z
From: "Prabir" <pchaudhuri@esc20.net>
Newsgroups: ianywhere.public.general
References: <40476f12.4913.846930886@sybase.com><404795a6@forums-1-dub> <40479640.5f4c.846930886@sybase.com><404799b0@forums-2-dub> <40479c13.5fbc.846930886@sybase.com><40479eef@forums-2-dub> <4047a920.4d85.846930886@sybase.com>
Subject: Re: Stored procedure error primary key is not unique
Lines: 141
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2800.1158
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1165
X-Original-NNTP-Posting-Host: 168.37.200.1
Message-ID: <4047aa2e$1@forums-2-dub>
X-Original-Trace: 4 Mar 2004 14:14:06 -0800, 168.37.200.1
X-Original-NNTP-Posting-Host: forums-2-dub.sybase.com
X-Original-Trace: 4 Mar 2004 14:11:54 -0800, forums-2-dub.sybase.com
NNTP-Posting-Host: forums-master.sybase.com
X-Original-NNTP-Posting-Host: forums-master.sybase.com
Date: 4 Mar 2004 14:13:20 -0800
X-Trace: forums-1-dub 1078438400 10.22.108.75 (4 Mar 2004 14:13:20 -0800)
X-Original-Trace: 4 Mar 2004 14:13:20 -0800, forums-master.sybase.com
X-Authenticated-User: ngsysop
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub ianywhere.public.general:2717
Article PK: 6190

I told u to drop primary key only for testing purpose

Prabir

<Newbegin> wrote in message news:4047a920.4d85.846930886@sybase.com...
> We ran the v_emp_pref_leave_balance_final there is no
> duplicate key on this select
> and there are no duplicate keys on
> v_emp_leave_rate_payper_adj
> and there are no duplicate keys on
> v_emp_pref_leave_payper_balance either.
>
> We still don't know why this same stored procedure works for
> individual employee with out select all
>
>
> We don't want to insert duplicate row. The insert should
> only happen in the instance of if the primary key values are
> missing. If we drop the primary key we would have duplicate
> row.
>
> > One thing u can do :
> >
> > drop the primary keys for the table "emp_leave_balance"
> > and run the procedure. After running that procedure, check
> > the duplicate primary key combination records on
> > "emp_leave_balance" and find the person id creating the
> > problem.
> >
> > Prabir
> > <Newbegin> wrote in message
> > > news:40479c13.5fbc.846930886@sybase.com... This stored
> > > procedure works fine for individual employee records but
> > > when I run it with select all employee records I have a
> > > problem. It should give the same error when I run this
> > procedure for individual records. >
> > >
> > > v_emp_leave_rate_payper_adj is a complicated view based
> > > on several tables but the out put of the view is
> > > person_id, task_id pay_per_yr_id, pay_per_seq
> > >
> > >
> > >
> > > > R u sure that your "v_emp_leave_rate_payper_adj" table
> > > > also have the same primary key combination ?
> > > >
> > > > Prabir
> > > >
> > > > <newbegin> wrote in message
> > > > > news:40479640.5f4c.846930886@sybase.com... These are
> > > > > the primary keys - person_id, task_id, pay_per_yr_id
> > > > , pay_per_seq >
> > > > > > what are the primary keys for ur table
> > > > > > emp_leave_balance ?
> > > > > >
> > > > > >
> > > > > >
> > > > > > Prabir
> > > > > >
> > > > > > <Newbegin> wrote in message
> > > > > > > news:40476f12.4913.846930886@sybase.com... We
> > > > > > > are using a cursor to loop through all the
> > > > > > > person_id and enter value in the
> > > > > > > emp_leave_balance table for which it does not
> > > > > > > have data. when we run the stored procedure for
> > > > > > > a single person the procedure works fine but
> > > > > > > when we include multiple person it does not
> > > > > > work. We have ASA V9 with the patch up date of
> > > > > > > 9.0.1 > This same stored procedure is working on
> > > > > > seven. >
> > > > > > > The following is the stored procedure
> > > > > > >
> > > > > > > ALTER PROCEDURE
> > > > > > > "resource_owner"."emp_leave_balance_sp"() begin
> > > > > > > declare @payper_yr_id id;
> > > > > > > declare @payper_seq seq;
> > > > > > > declare @personid id;
> > > > > > > declare err_notfound exception for sqlstate
> > > > > > > value '02000'; declare ThePerson dynamic
> > > > > > > scroll cursor for select person_id from person;
> > > > > > > select pay_per_yr_id,pay_per_seq into
> > > > > > > @payper_yr_id,@payper_seq from pay_per where
> > > > > > > start_dt <= TODAY(*) and end_dt >= TODAY(*);
> > > > > > > open ThePerson;
> > > > > > > PersonLoop: loop
> > > > > > > fetch next ThePerson into @personid;
> > > > > > > if sqlstate = err_notfound then
> > > > > > > leave PersonLoop
> > > > > > > end if;
> > > > > > > if @personid is not null then
> > > > > > > insert into emp_leave_balance(
> > > > > > > person_id,task_id,pay_per_yr_id,pay_per_seq
> > > > > > > ,leave_accrual_rate ,
> > > > > > >
> > > > > > > leave_freq_cd,adjustment,used_hour
> > > > > > > ,pay_per_balance ,final_balance) select
> > > > > > > a.person_id,a.task_id,a.pay_per_yr_id
> > > > > > > ,a.pay_per_seq ,a.leave_accrual_rate ,
> > > > > > > a.leave_freq_cd,a.adjustment
> > > > > > > ,a.used_hour, (select
> > > > > > > b.pay_per_balance from
> > > > > > > v_emp_pref_leave_payper_balance as b
> > > > > > > where a.person_id = b.person_id and
> > > > > > > a.task_id = b.task_id and
> > > > > > > a.pay_per_yr_id = b.pay_per_yr_id
> > > > > > > and a.pay_per_seq = b.pay_per_seq) as
> > > > > > > pay_per_balance, (select
> > > > > > > f.final_balance from
> > > > > > > v_emp_pref_leave_balance_final as f
> > > > > > > where a.person_id = f.person_id and
> > > > > > > a.task_id = f.task_id and
> > > > > > > a.pay_per_yr_id = f.pay_per_yr_id
> > > > > > > and a.pay_per_seq = f.pay_per_seq) as
> > > > > > > final_balance from
> > > > > > > v_emp_leave_rate_payper_adj as a where
> > > > > > > a.person_id = @personid and
> > > > > > > a.pay_per_yr_id = @payper_yr_id and
> > > > > > > a.pay_per_seq = @payper_seq and not
> > > > > > > a.task_id = any(select b1.task_id from
> > > > > > > emp_leave_balance as b1 where
> > > > > > > b1.person_id = @personid and
> > > > > > > b1.pay_per_yr_id = @payper_yr_id and
> > > > > > > b1.pay_per_seq = @payper_seq) end if
> > > > > > > end loop PersonLoop;
> > > > > > > close ThePerson;
> > > > > > > if sqlcode <> 0 then
> > > > > > > rollback work
> > > > > > > else
> > > > > > > commit work
> > > > > > > end if
> > > > > > > end
> > > > > > >
> > > > > > > Thanks
> > > > > > > Newbegin
> > > > > >
> > > > > >
> > > >
> > > >
> >
> >


Newbegin Posted on 2004-03-05 14:11:16.0Z
Sender: 5556.40488a08.1804289383@sybase.com
From: Newbegin
Newsgroups: ianywhere.public.general
Subject: Re: Stored procedure error primary key is not unique
X-Mailer: WebNews to Mail Gateway v1.1s
Message-ID: <40488ab3.5560.846930886@sybase.com>
References: <40476f12.4913.846930886@sybase.com><404795a6@forums-1-dub> <40479640.5f4c.846930886@sybase.com><404799b0@forums-2-dub> <40479c13.5fbc.846930886@sybase.com><40479eef@forums-2-dub> <4047a920.4d85.846930886@sybase.com><4047aa2e$1@forums-2-dub>
X-Original-NNTP-Posting-Host: 10.22.241.42
X-Original-Trace: 5 Mar 2004 06:12:03 -0800, 10.22.241.42
Lines: 148
X-Original-NNTP-Posting-Host: forums-2-dub.sybase.com
X-Original-Trace: 5 Mar 2004 06:09:46 -0800, forums-2-dub.sybase.com
NNTP-Posting-Host: forums-master.sybase.com
X-Original-NNTP-Posting-Host: forums-master.sybase.com
Date: 5 Mar 2004 06:11:16 -0800
X-Trace: forums-1-dub 1078495876 10.22.108.75 (5 Mar 2004 06:11:16 -0800)
X-Original-Trace: 5 Mar 2004 06:11:16 -0800, forums-master.sybase.com
X-Authenticated-User: ngsysop
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub ianywhere.public.general:2721
Article PK: 6194

We dropped the primary key and ran the stored procedure
again. I expected the row count to double when I ran the
second time. but the row count at the first run was 2022 and
the row count after the second run was 3649

Newbegin

> I told u to drop primary key only for testing purpose
>
> Prabir
> <Newbegin> wrote in message
> > news:4047a920.4d85.846930886@sybase.com... We ran the
> > v_emp_pref_leave_balance_final there is no duplicate key
> > on this select and there are no duplicate keys on
> > v_emp_leave_rate_payper_adj
> > and there are no duplicate keys on
> > v_emp_pref_leave_payper_balance either.
> >
> > We still don't know why this same stored procedure works
> > for individual employee with out select all
> >
> >
> > We don't want to insert duplicate row. The insert
> > should only happen in the instance of if the primary key
> > values are missing. If we drop the primary key we would
> > have duplicate row.
> >
> > > One thing u can do :
> > >
> > > drop the primary keys for the table
> > > "emp_leave_balance" and run the procedure. After
> > > running that procedure, check the duplicate primary
> > > key combination records on "emp_leave_balance" and
> > > find the person id creating the problem.
> > >
> > > Prabir
> > > <Newbegin> wrote in message
> > > > news:40479c13.5fbc.846930886@sybase.com... This
> > > > stored procedure works fine for individual employee
> > > > records but when I run it with select all employee
> > > > records I have a problem. It should give the same
> > > error when I run this procedure for individual
> > > records. > >
> > > > v_emp_leave_rate_payper_adj is a complicated view
> > > > based on several tables but the out put of the view
> > > > is person_id, task_id pay_per_yr_id, pay_per_seq
> > > >
> > > >
> > > >
> > > > > R u sure that your "v_emp_leave_rate_payper_adj"
> > > > > table also have the same primary key combination ?
> > > > >
> > > > > Prabir
> > > > >
> > > > > <newbegin> wrote in message
> > > > > > news:40479640.5f4c.846930886@sybase.com... These
> > > > > > are the primary keys - person_id, task_id,
> > > > > pay_per_yr_id , pay_per_seq >
> > > > > > > what are the primary keys for ur table
> > > > > > > emp_leave_balance ?
> > > > > > >
> > > > > > >
> > > > > > >
> > > > > > > Prabir
> > > > > > >
> > > > > > > <Newbegin> wrote in message
> > > > > > > > news:40476f12.4913.846930886@sybase.com...
> > > > > > > > We are using a cursor to loop through all
> > > > > > > > the person_id and enter value in the
> > > > > > > > emp_leave_balance table for which it does
> > > > > > > > not have data. when we run the stored
> > > > > > > > procedure for a single person the procedure
> > > > > > > > works fine but when we include multiple
> > > > > > > person it does not work. We have ASA V9 with
> > > > > > > > the patch up date of 9.0.1 > This same
> > > > > > > stored procedure is working on seven. >
> > > > > > > > The following is the stored procedure
> > > > > > > >
> > > > > > > > ALTER PROCEDURE
> > > > > > > > "resource_owner"."emp_leave_balance_sp"()
> > > > > > > > begin declare @payper_yr_id id;
> > > > > > > > declare @payper_seq seq;
> > > > > > > > declare @personid id;
> > > > > > > > declare err_notfound exception for
> > > > > > > > sqlstate value '02000'; declare ThePerson
> > > > > > > > dynamic scroll cursor for select person_id
> > > > > > > > from person; select pay_per_yr_id
> > > > > > > > ,pay_per_seq into @payper_yr_id,@payper_seq
> > > > > > > > from pay_per where start_dt <= TODAY(*)
> > > > > > > > and end_dt >= TODAY(*); open ThePerson;
> > > > > > > > PersonLoop: loop
> > > > > > > > fetch next ThePerson into @personid;
> > > > > > > > if sqlstate = err_notfound then
> > > > > > > > leave PersonLoop
> > > > > > > > end if;
> > > > > > > > if @personid is not null then
> > > > > > > > insert into emp_leave_balance(
> > > > > > > > person_id,task_id,pay_per_yr_id,pay_per_seq
> > > > > > > > ,leave_accrual_rate ,
> > > > > > > >
> > > > > > > > leave_freq_cd,adjustment,used_hour
> > > > > > > > ,pay_per_balance ,final_balance)
> > > > > > > > select a.person_id,a.task_id,a.pay_per_yr_id
> > > > > > > > ,a.pay_per_seq ,a.leave_accrual_rate ,
> > > > > > > > a.leave_freq_cd,a.adjustment
> > > > > > > > ,a.used_hour, (select
> > > > > > > > b.pay_per_balance from
> > > > > > > > v_emp_pref_leave_payper_balance
> > > > > > > > as b where a.person_id =
> > > > > > > > b.person_id and a.task_id =
> > > > > > > > b.task_id and a.pay_per_yr_id =
> > > > > > > > b.pay_per_yr_id and a.pay_per_seq =
> > > > > > > > b.pay_per_seq) as pay_per_balance,
> > > > > > > > (select f.final_balance from
> > > > > > > > v_emp_pref_leave_balance_final
> > > > > > > > as f where a.person_id =
> > > > > > > > f.person_id and a.task_id =
> > > > > > > > f.task_id and a.pay_per_yr_id =
> > > > > > > > f.pay_per_yr_id and a.pay_per_seq =
> > > > > > > > f.pay_per_seq) as final_balance
> > > > > > > > from v_emp_leave_rate_payper_adj
> > > > > > > > as a where a.person_id = @personid
> > > > > > > > and a.pay_per_yr_id =
> > > > > > > > @payper_yr_id and a.pay_per_seq =
> > > > > > > > @payper_seq and not a.task_id = any(select
> > > > > > > > b1.task_id from
> > > > > > > > emp_leave_balance as b1 where
> > > > > > > > b1.person_id = @personid and
> > > > > > > > b1.pay_per_yr_id = @payper_yr_id and
> > > > > > > > b1.pay_per_seq = @payper_seq) end if end
> > > > > > > > loop PersonLoop; close ThePerson;
> > > > > > > > if sqlcode <> 0 then
> > > > > > > > rollback work
> > > > > > > > else
> > > > > > > > commit work
> > > > > > > > end if
> > > > > > > > end
> > > > > > > >
> > > > > > > > Thanks
> > > > > > > > Newbegin
> > > > > > >
> > > > > > >
> > > > >
> > > > >
> > >
> > >
>
>