Rounding/Truncate issue 1

[tpbjr]

Hi,
I am having a problem with a group by query that sums up a field that is defined as NUMBER(10,2). The result that comes back is dropping off the 2 place holder on the right side of the decimal. Is there a way to sum and show both digits?

select journal_type, dr_cr_ind, sum(post_amt)
from other_journal
where fiscal_yr = 2006 and fiscal_period = 6 and journal_type = 'FT'
group by journal_type, dr_cr_ind

RESULT:
JO D SUM(POST_AMT)
-- - -------------
FT C 81999622.4
FT D 81999622.4


There is pl/sql logic that sums the results differently at month end and it detects the penny difference (05 compared to 06). I am trying to be proactive to catch an inbalance before the production job runs and fails. Then I can try and find the problem record during the day instead of in the middle of the night. Any help would be greatly appreciated.



Thank you for all your help

http://www.besware.com

Tom

 

[feherke]

Hi

That is matter of formating. In SQL*Plus :

[blue]SQL>[/blue] col formated format 000009.99
[blue]SQL>[/blue] select sum(post_amt),sum(post_amt) formated from tpbjr;

SUM(POST_AMT)    FORMATED
------------- -----------
        172.8      172.80

Feherke.

http://rootshell.be/~feherke/

 

[tpbjr]

Thanks for the input.

I did what you suggested but the results still came back the same. I am thinking that Oracle drops trailing zeros.
What do you think?

SQL> col formated format 000009.99
SQL>
SQL> select journal_type, dr_cr_ind, sum(post_amt)
2 from other_journal
3 where fiscal_yr = 2006 and fiscal_period = 6 and journal_type = 'FT'
group by journal_type, dr_cr_ind;


JO D SUM(POST_AMT)
-- - -------------
FT C 81999622.4
FT D 81999622.4

Thank you for all your help

http://www.besware.com

Tom

 

[feherke]

Hi

The word "formated" in the [tt]col[/tt] command is the name of the column for which to apply the format. So give tha same alias for the field :

select journal_type, dr_cr_ind, sum(post_amt) [red]formated[/red]
from other_journal
where fiscal_yr = 2006 and fiscal_period = 6 and journal_type = 'FT'
group by journal_type, dr_cr_ind;

Sorry, last time I copy&pasted other command then I intended. Better use this, will look better :

col formated format 9999999.99

Feherke.

http://rootshell.be/~feherke/

 

[tpbjr]

Thank you so much it worked.
However, the 9's only returned ##########.

SQL> col formated format 00000000.00
SQL> select journal_type, dr_cr_ind, sum(post_amt) formated
2 from other_journal
3 where fiscal_yr = 2006 and fiscal_period = 6 and journal_type = 'FT'
group by journal_type, dr_cr_ind;
4

JO D FORMATED
-- - ------------
FT C 81999622.39
FT D 81999622.39


Thank you so much. I am glad they are equal, though I still have to track down why they are not equal some months.

Thanks again--you have been a life saver.



Thank you for all your help

http://www.besware.com

Tom

 

[feherke]

Hi

However, the 9's only returned ##########.

Not the 9s. The hash ( # ) character is displayed when the value is larger then the given format.

Sorry, I am new in Oracle and I calculated as in DBFs : number(10,2) should fit in 9999999.99 ( 7 '9's, 1 dot, 2 '9'2 ).

Your format worked not because of the 0s, because you put 8 characters in front of the dot, no just 7 as I wrote.

Feherke.

http://rootshell.be/~feherke/