Re: Sql count not adding up correctly

Yep, but I'm now also questioning my update routine as well. I'll post it
here:
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));

When I ran this the last time it updated like a 140,000 records which is
like way wrong....
I'm trying to code it similar to the select statement....

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.