Re: Sql count not adding up correctly

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.