Home » Developer & Programmer » Reports & Discoverer » Strange behaving for a sum (Oracle Discoverer)
Strange behaving for a sum [message #314863] Fri, 18 April 2008 01:27 Go to next message
jb084
Messages: 14
Registered: April 2008
Junior Member
Hello.

First thing, Thanks to be not too tough for my english, which isn't my first language Wink

Then, I can't get rid of a problem with a Discoverer query. To be simple, here it is :
- I made a query, which originally was on an unique table. There, all my sums worked.
- Then I made a join on another view, in order to display another field. And at this moment, one of my Sum didn't work anymore, if there is a negative number in the range ! I really don't understand what's going on.

I tried to change the format of the number, in vane.

Have someone already leet this problem ? Can someone help me ?

Thanks a lot !

Report message to a moderator

Re: Strange behaving for a sum [message #315020 is a reply to message #314863] Fri, 18 April 2008 12:18 Go to previous messageGo to next message
jb084
Messages: 14
Registered: April 2008
Junior Member
I know it's hard to figure out what's my problem, so I screenshotted my query, in order to show you more details.

http://farm4.static.flickr.com/3091/2422879411_0ba0ae4b2c.jpg?v=0

NB : The totalisation cells are the yellow one

As you can see, the last totalisation doesn't work : The weird thing is that the fifth column has the same numbers, and manage to get the good result !

[Updated on: Fri, 18 April 2008 12:19]

Report message to a moderator

Re: Strange behaving for a sum [message #315124 is a reply to message #315020] Sat, 19 April 2008 04:03 Go to previous messageGo to next message
Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Well, I took the calculator and subtracted these values:
1.  25829.78             = 25829.78
2.  12110.34 -   9694.78 =  2415.56
3. 108901.78 - 105911.78 =  2990.00
4.  43163.64 -  41005.45 =  2158.19

5.  80700.10 -  76665.09 =  4035.01

As far as I can tell, the fifth result is correct, just as all the others.

What do you find wrong in it?

Report message to a moderator

Re: Strange behaving for a sum [message #315127 is a reply to message #315124] Sat, 19 April 2008 04:16 Go to previous messageGo to next message
jb084
Messages: 14
Registered: April 2008
Junior Member
I didn't explain myself well

The problem is with the sixth column ("Solde"): Where there is a blank cell, should be displayed the sum of the column, such as the fifth column ("au dela de 90").

But, as you can see the sum is done for the fifth, but not for the sixth one, when there is a negative value. It's quite weird. This problem just appeared when I add a join with another table, and juste for the cell !

NB : All the cells are the result of functions.

Report message to a moderator

Re: Strange behaving for a sum [message #315314 is a reply to message #315127] Mon, 21 April 2008 01:19 Go to previous messageGo to next message
skooman
Messages: 913
Registered: March 2005
Location: Netherlands
Senior Member
How is the join between the two tables defined? It looks as if this second table you're joining is a master table (opposite to the detail table), in that case Discoverer doesn't "allow" totals because it can lead to wrong results. If you believe that the second table is (or should be) a detail table, then you should define that correctly in the EUL.
(there are some tricks to work around this strict logic, so if you insist in summing up masterrecord, let us know)

Report message to a moderator

Re: Strange behaving for a sum [message #315323 is a reply to message #314863] Mon, 21 April 2008 01:41 Go to previous messageGo to next message
jb084
Messages: 14
Registered: April 2008
Junior Member
There is a outter join defined between the two tables.

The master table is the main table, in which the results are outputted.

I uploaded the screenshot of the detail of the join. It's in French, but as you seeem to know very well the software, I think you'll be able to understand from the presentation of the screen the meanings of the fields.
http://farm4.static.flickr.com/3038/2430730004_e2834d6941.jpg?v=0
Let me know if you don't understand a word of it Wink

I would have understood if the total weren't allowed at all, but in my case, it doesn't work only when there is negative values !

Thanks for your help

Report message to a moderator

Re: Strange behaving for a sum [message #315392 is a reply to message #315323] Mon, 21 April 2008 06:53 Go to previous messageGo to next message
skooman
Messages: 913
Registered: March 2005
Location: Netherlands
Senior Member
From your screendump of the workbook results, all we can conclude is that the sum is not shown if there is more then one record within a group. Whether a positive or negative number has anything to do with it, that remains to be seen Wink

Anyway, related to the workbook screendump, which column is coming from which table?

Report message to a moderator

Re: Strange behaving for a sum [message #315406 is a reply to message #314863] Mon, 21 April 2008 07:30 Go to previous messageGo to next message
jb084
Messages: 14
Registered: April 2008
Junior Member
You thought right. I checked, and in all my file the sum doesn't work if there is more than one value to add !

The problem has a little bit changed, but the result is stille the same !

The "Solde" column is the result of the operation : 
DECODE(Solde,NULL,NVL(Debit,0)-NVL(Credit,0)-NVL(Regle,0),NVL(Solde,0))-NVL(Accepte,0)


And the "au dela de 90" column has the following operation: 
DECODE(GREATEST(nb Jour,90,5),nb Jour,Total Solde,0)


I just resolved my case : I changed my folder, I added an element in order to avoid one join, and then it works.

Thanks a lot for your help Wink

Report message to a moderator

Re: Strange behaving for a sum [message #315461 is a reply to message #315406] Mon, 21 April 2008 09:55 Go to previous messageGo to next message
skooman
Messages: 913
Registered: March 2005
Location: Netherlands
Senior Member
For the future, I suggest you read doc 208366.1 on Metalink. That explains the aggregate behavior of Discoverer in relation to decodes.
However, it doesn't explain the relation between aggregates and joins, whereas I know there is a relationship, but I can't find the documentation right now. Perhaps you could search for it yourself at Metalink?

Report message to a moderator

Re: Strange behaving for a sum [message #315464 is a reply to message #314863] Mon, 21 April 2008 10:05 Go to previous messageGo to next message
jb084
Messages: 14
Registered: April 2008
Junior Member
Thanks for the advice.

I don't know Metalink at all. Is it a oracle documentation ? I'll ask for the register code to my boss, i'm sure noone knew about it before !

Report message to a moderator

Re: Strange behaving for a sum [message #315617 is a reply to message #315464] Tue, 22 April 2008 03:43 Go to previous message
skooman
Messages: 913
Registered: March 2005
Location: Netherlands
Senior Member
Information about Metalink can be found at: http://www.oracle.com/support/premier/metalink.html

Have fun Wink

[Updated on: Tue, 22 April 2008 03:43]

Report message to a moderator

Previous Topic: Display Chinese characters in PDF report developed in D2K
Next Topic: Letter spacing problem in PDF reports developed in D2K
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Sat Nov 30 03:52:08 CST 2024
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.