Difference while summing [message #324805] |
Wed, 04 June 2008 02:41  |
Manesh
Messages: 4 Registered: June 2008 Location: Mumbai
|
Junior Member |
|
|
Dear All,
Have been facing a very peculiar problem where in if I extract a set of data from the source and later summarise the numeric columns through another sql statement, the totals do not match with a query which does the summarisation directly from the source. Has anyone come accross this.
Will be grateful if someone can throw some light on this or why this could occur.
eg .
Scenario 1
step 1 :
create table ABC as
select columA, columnB,nvl(columnC,0) columnC,nvl(coulmnD,0) columnD from TableA
where Date1='01-May-2008'
step 2 :
select sum(columnC),sum(columnD) from ABC.
Scenario 2
create table ABC as
select columA, columnB,sum(nvl(columnC,0)) columnC,sum(nvl(coulmnD,0)) columnD from TableA
where Date1='01-May-2008'
Output : Values of columnC and columnD in both the scenarios do not match. Am I doing something wrong here.
Would appreciate some feedback.
M.
|
|
|
|
Re: Difference while summing [message #324810 is a reply to message #324805] |
Wed, 04 June 2008 03:05   |
 |
Michel Cadot
Messages: 68737 Registered: March 2007 Location: Saint-Maur, France, https...
|
Senior Member Account Moderator |
|
|
1/ You should please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter) and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version (4 decimals).
2/ '01-May-2008' is not a date but a string and so:
SQL> select to_date('01-May-2008') from dual;
select to_date('01-May-2008') from dual
*
ERROR at line 1:
ORA-01858: a non-numeric character was found where a numeric was expected
3/ Scenario 2 is NOT a valid statement, so I wonder how you could compare the result
4/ Between 2 statements result set may change
5/ Use SQL*Plus and copy and paste your session, does not just describe it as it is likely you misinterpret what you did (otherwise you didn't have to post here). (Don't forget to format your post)
Regards
Michel
[Updated on: Wed, 04 June 2008 03:05] Report message to a moderator
|
|
|
Re: Difference while summing [message #324864 is a reply to message #324809] |
Wed, 04 June 2008 06:34   |
Manesh
Messages: 4 Registered: June 2008 Location: Mumbai
|
Junior Member |
|
|
sorry about the delay. the difference is that in the first scenario I am creating a temp table and then summing up the numeric columns. In the second scenario I am directly summing up while extracting the data in the select. The problem is that the totals of the numeric columns are not matching.
|
|
|
Re: Difference while summing [message #324868 is a reply to message #324864] |
Wed, 04 June 2008 06:44  |
 |
Michel Cadot
Messages: 68737 Registered: March 2007 Location: Saint-Maur, France, https...
|
Senior Member Account Moderator |
|
|
Quote: | 3/ Scenario 2 is NOT a valid statement, so I wonder how you could compare the result
5/ Use SQL*Plus and copy and paste your session, does not just describe it as it is likely you misinterpret what you did (otherwise you didn't have to post here). (Don't forget to format your post)
|
How can we answer to something that is just not valid.
Regards
Michel
[Updated on: Wed, 04 June 2008 06:45] Report message to a moderator
|
|
|