aggregated fields divided by 100 when insert into temp table

[richardleehl]

Hi,

I have been working for the last two hours trying to figure out why this was happening in Pervasive 8.

table employee (simplified table)

ID salary
A 100
A 100
A 100

simplified version of stored procedure

my stored proc:

select ID, sum(salary) Total from
employee
group by ID


The resultset from this stored proc returns:

ID Total
A 300



However, if I take this simplified stored proc and insert the results of that into a temp table before displaying,
it provides a resultset divided by exactly 100.


stored proc that divides by 100 incorrectly:

select ID, sum(salary) Total
into "#table1"
from employee
group by ID

select ID, TOTAL from "#table1"


Resultset is as follows:

ID Total
A 3.00


The example I'm providing is absolutely simplified and the stored proc actually does a lot more. But if I take the results and insert into a temp table before displaying the temp table contents, the results are always divided by 100.

has anyone else seen this problem?

 

[mirtheil]

I've not heard of this behavior. A few questions:
- What version of V8 are you using?
- Also, what tool are you using (PCC, ODBC Test, etc)?
- Do you see the behavior using ODBC Test?
- What's the data type of "salary"?

Mirtheil
Certified Pervasive Developer
Certified Pervasive Technician

http://www.mirtheil.com

 

[richardleehl]

The version I'm using is 8.60.192.030.
I'm creating the stored proc through the Data Manager and executing the stored proc by calling it.
the salary field is a currency field.
I'm not sure what you mean by (PCC, ODBC Test). I've since reduplicated this many times and have found that if you take an aggregated field and store it into a temp table before displaying, it divides by 100.
If I just aggregate the field and display, there is no issue with the resultset.

Any idea why this is the case?

 

[richardleehl]

After some research, I believe we are using PCC since I sometimes get an erro with PCC.exe.

I've tried to create another stored proc that aggregates and stores to a temp table then aggregate that result and store to a another temp table to see if the result set would be divided by 100 twice.

create procedure altwork2 ()


RETURNS(
TicketNum int,
Ptotal currency

);

begin

select TicketNum, PriceExtension
into "#table1"
from "CRAFTSMAN".TICKHISD
where departmentnum=97
and
(TicketNum=27184
or TicketNum=27185
or TicketNum=27266);

select TicketNum, sum(PriceExtension) Price_promo
into "#table2"
from "#table1"
group by TicketNum;

select TicketNum, sum(Price_promo) Ptotal
into "#table3"
from "#table2"
group by Ticketnum;

select * from "#table3";

end;


however, everytime i try to call this stored proc, the server disconnects and I lose connection to the server. I'm wondering since this is a really simple stored proc why this would cause the system to disconnect vs the earlier two stored procs i had called.