× The internal search function is temporarily non-functional. The current search engine is no longer viable and we are researching alternatives.
As a stop gap measure, we are using Google's custom search engine service.
If you know of an easy to use, open source, search engine ... please contact support@midrange.com.



Okay although it might be right, I do see something that should be changed
as Birgitta suggested.


Update CaRates as a
Set a.term = :xTermDate
Where exists
(select * from xtCaRates as b

where a.ptccp1 = b. and
a.ptccp2 = b.ptccp2 and
a.ptccp3 = b.ptccp3 and
a.ptccp4 = b.ptccp4 and
a.ptccp5 = b.ptccp5 and
a.ptccp6 = b.ptccp6 and
a.ptccp7 = b.ptccp7 and
(a.term = 0 or a.term > :xDate))


It has to do with this right here. You have selection being done on Table
a (CaRates) within the Exists Clause.
WHERE EXISTS (...
and
(a.term = 0 or a.term > :xDate))

I say move it outside of the EXISTS.

So...

Update CaRates as a
Set a.term = :xTermDate
Where exists
(select * from xtCaRates as b
where a.ptccp1 = b. and
a.ptccp2 = b.ptccp2 and
a.ptccp3 = b.ptccp3 and
a.ptccp4 = b.ptccp4 and
a.ptccp5 = b.ptccp5 and
a.ptccp6 = b.ptccp6 and
a.ptccp7 = b.ptccp7 ) and -- notice the parenthesis
added.
(a.term = 0 or a.term > :xDate) -- not the parenthesis
removed.

Another option would be to use the IN clause.

Update CaRates as a
Set a.term = :xTermDate
Where
(a.ptccp1, a.ptccp2, a.ptccp3, a.ptccp4, a.ptccp5, a.ptccp6, a.ptccp7) in
(select b.ptccp1, b.ptccp2, b.ptccp3, b.ptccp4, b.ptccp5,
b.ptccp6, b.ptccp7
from xtCaRates as b)
and
(a.term = 0 or a.term > :xDate)

Change Update CaRates to Select Count(1) From CaRates

To get a count of records.



On Wed, Nov 21, 2012 at 10:51 AM, <rpglist@xxxxxxxxxxx> wrote:

I just didn't get it right Lin, no other reason. Your correct that the
two need to be the same or should be. There are no triggers on the file.

value of xdate is : 20121231


This is where it gets confusing to me. Is there a reason why you use a
completely different construct and conditions to perform the count? If
you want to verify you update count, I would think you want the count sql
statement to be similar to the update statement. Do you might tell us
the
value of :xDate? Also, can you make sure there is no trigger on the
file?



<rpglist@xxxxxxxxxxx> wrote in message
news:<mailman.1108.1353515813.10847.rpg400-l@xxxxxxxxxxxx>...
Primary file - CaRates has 15,538,315
Secondary file - xtCaRates has 45,612.

First query run - 45,504 (this looks far better)
Ran update
Second Qery run - 45,504.

However, here is something that is strange. I had zero records prior to
the update run that matched a date of 20121231, after the update I had
140,640.....definitely not correct.


Can you use this query and tell us what is the count before the
update
occur?
Select count(*) from CaRates as a
Where exists
(select * from xtCaRates as b
where a.ptccp7 = b.ptccp7 and
a.ptccp6 = b.ptccp6 and
a.ptccp5 = b.ptccp5 and
a.ptccp4 = b.ptccp4 and
a.ptccp3 = b.ptccp3 and
a.ptcpp2 = b.ptccp2 and
a.ptcpp1 = b.ptcpp1 and
(a.Term = 0 or a.Term > :xDate)); // replace :xDate
to
whatever that date is.

After the update, can you use this query and tell us what is the
count?
Select count(*) from CaRates as a
Where exists
(select * from xtCaRates as b
where a.ptccp7 = b.ptccp7 and
a.ptccp6 = b.ptccp6 and
a.ptccp5 = b.ptccp5 and
a.ptccp4 = b.ptccp4 and
a.ptccp3 = b.ptccp3 and
a.ptcpp2 = b.ptccp2 and
a.ptcpp1 = b.ptcpp1 and
(a.Term = :xDate)); // replace :xDate to whatever
that
date is.



<rpglist@xxxxxxxxxxx> wrote in message
news:<mailman.629.1353363908.10847.rpg400-l@xxxxxxxxxxxx>...
I'm having a problem trying to get an accurate count of my records
being
updated.

Here is the SQL statement for the update:

Exec Sql
Update CaRates as a
Set a.Term = :xTermDate
Where exists
(select * from xtCaRates as b
where a.ptccp7 = b.ptccp7 and
a.ptccp6 = b.ptccp6 and
a.ptccp5 = b.ptccp5 and
a.ptccp4 = b.ptccp4 and
a.ptccp3 = b.ptccp3 and
a.ptcpp2 = b.ptccp2 and
a.ptcpp1 = b.ptcpp1 and
(a.Term = 0 or a.Term > :xDate));

The update seems to work just fine, but I'm trying to validate the
results
and this statement always seems to give me more records than I have:

SELECT count(*)
FROM CaRates a, xtCaRates b WHERE a.ptccp7 = b.ptccp7
and a.ptccp6 = b.ptccp6 and a.ptccp5 = b.ptccp5 and a.ptccp4 =
b.ptccp4 and a.ptccp3 = b.ptccp3 and a.ptccp2 = b.ptccp2 and
a.ptccp1 = b.ptccp1
and (a.Term= 0 or a.Term > 20121214 and
a.Term <> 20121231)

Any help or suggestion would be greatly appreciated.




--
This is the RPG programming on the IBM i / System i (RPG400-L) mailing
list
To post a message email: RPG400-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/rpg400-l.




--
This is the RPG programming on the IBM i / System i (RPG400-L) mailing
list
To post a message email: RPG400-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/rpg400-l.




--
This is the RPG programming on the IBM i / System i (RPG400-L) mailing list
To post a message email: RPG400-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/rpg400-l.



As an Amazon Associate we earn from qualifying purchases.

This thread ...

Replies:

Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2024 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].

Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.