Home » SQL & PL/SQL » SQL & PL/SQL » Difference while summing (Oracle 8i)
Difference while summing [message #324805] Wed, 04 June 2008 02:41 Go to next message
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 #324809 is a reply to message #324805] Wed, 04 June 2008 02:57 Go to previous messageGo to next message
mm_kanish05
Messages: 487
Registered: January 2007
Location: Chennai
Senior Member

Can u Give sample of output

what is diff. between two statement.

kanish
Re: Difference while summing [message #324810 is a reply to message #324805] Wed, 04 June 2008 03:05 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
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 Go to previous messageGo to next message
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 Go to previous message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
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

Previous Topic: Problem: Do I need to use materialized view for this?
Next Topic: dbms_flashback package problem
Goto Forum:
  


Current Time: Sat Dec 10 16:41:04 CST 2016

Total time taken to generate the page: 0.04827 seconds