Home » Developer & Programmer » Reports & Discoverer » Strange behaving for a sum (Oracle Discoverer)
Strange behaving for a sum Fri, 18 April 2008 01:27
 jb084 Messages: 14Registered: April 2008 Junior Member
Hello.

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

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 !
Re: Strange behaving for a sum [message #315020 is a reply to message #314863] Fri, 18 April 2008 12:18
 jb084 Messages: 14Registered: 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.

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
 Littlefoot Messages: 21131Registered: June 2005 Location: Croatia, Europe Senior MemberAccount 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?
Re: Strange behaving for a sum [message #315127 is a reply to message #315124] Sat, 19 April 2008 04:16
 jb084 Messages: 14Registered: 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.
Re: Strange behaving for a sum [message #315314 is a reply to message #315127] Mon, 21 April 2008 01:19
 skooman Messages: 913Registered: 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)
Re: Strange behaving for a sum [message #315323 is a reply to message #314863] Mon, 21 April 2008 01:41
 jb084 Messages: 14Registered: 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.

Let me know if you don't understand a word of it

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 !

Re: Strange behaving for a sum [message #315392 is a reply to message #315323] Mon, 21 April 2008 06:53
 skooman Messages: 913Registered: 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

Anyway, related to the workbook screendump, which column is coming from which table?
Re: Strange behaving for a sum [message #315406 is a reply to message #314863] Mon, 21 April 2008 07:30
 jb084 Messages: 14Registered: 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

Re: Strange behaving for a sum [message #315461 is a reply to message #315406] Mon, 21 April 2008 09:55
 skooman Messages: 913Registered: 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?
Re: Strange behaving for a sum [message #315464 is a reply to message #314863] Mon, 21 April 2008 10:05
 jb084 Messages: 14Registered: April 2008 Junior Member

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 !

Re: Strange behaving for a sum [message #315617 is a reply to message #315464] Tue, 22 April 2008 03:43
 skooman Messages: 913Registered: March 2005 Location: Netherlands Senior Member

Have fun

[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:

Current Time: Thu Jul 27 11:50:25 CDT 2017

Total time taken to generate the page: 0.06327 seconds