INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

aggregated fields divided by 100 when insert into temp table

aggregated fields divided by 100 when insert into temp table

(OP)
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?



 

RE: aggregated fields divided by 100 when insert into temp table

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

RE: aggregated fields divided by 100 when insert into temp table

(OP)
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?

 

RE: aggregated fields divided by 100 when insert into temp table

(OP)
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.  
 

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close