 |
BorlandTalk.com
Borland discussion newsgroups
|
| View previous topic :: View next topic |
| Author |
Message |
yichun
Guest
|
 Posted: Mon Aug 30, 2004 10:42 am Post subject: weird SQL result set from 2 similar SQL statement |
 |
|
hi,
i am a novice in ORACLE. i am using Oracle 8i.
i have two similar query, actually they should generate result sets but one
of them did not:
query1:(empty result set)
SELECT COL1, COL2, SUM(COL3) FROM TABLE1
GROUP BY COL1, COL2
query2:(got result set)
SELECT COL1, SUM(COL3) FROM TABLE1
GROUP BY COL1
if i exclude COL2 in query1(become query2), it have result set!
if COL2 did not contain any null data, what the reason is query1 doesnot
have result set?
Is oracle data corrupted?
|
|
| Back to top |
|
 |
Eric Hill
Guest
|
| Posted: Mon Aug 30, 2004 1:19 pm Post subject: Re: weird SQL result set from 2 similar SQL statement |
|
|
| Quote: | i am a novice in ORACLE. i am using Oracle 8i.
|
Welcome
| Quote: | if COL2 did not contain any null data, what the reason is query1 doesnot
have result set?
|
You probably have null data in COL3, not COL2. Summing values that include null returns no result
since you can't sum a null value. Add "where col3 is not null" to the end of the first query and
see if that helps.
| Quote: | Is oracle data corrupted?
|
I'd doubt it :)
Eric
|
|
| Back to top |
|
|
Wayne Niddery [TeamB]
Guest
|
| Posted: Mon Aug 30, 2004 5:05 pm Post subject: Re: weird SQL result set from 2 similar SQL statement |
|
|
Eric Hill wrote:
| Quote: |
You probably have null data in COL3, not COL2. Summing values that
include null returns no result since you can't sum a null value. Add
"where col3 is not null" to the end of the first query and see if
that helps.
|
But he's summing COL3 in both cases, only omiting COL2 from the grouping.
Definitely something strange, he should get *different* results, but not an
empty result in one while not the other.
--
Wayne Niddery - Logic Fundamentals, Inc. (www.logicfundamentals.com)
RADBooks: http://www.logicfundamentals.com/RADBooks.html
"True peace is not the absence of tension, but the presence of
justice." - Martin Luther King, Jr.
|
|
| Back to top |
|
|
Eric Hill
Guest
|
| Posted: Mon Aug 30, 2004 6:29 pm Post subject: Re: weird SQL result set from 2 similar SQL statement |
|
|
| Quote: | But he's summing COL3 in both cases, only omiting COL2 from the grouping.
Definitely something strange, he should get *different* results, but not an
empty result in one while not the other.
|
Yup, you're right. I went back and ran a test and it worked as expected.
Have you tried metalink? Maybe there's some bug in 8 that you're running into...
Eric
|
|
| Back to top |
|
|
yichun
Guest
|
| Posted: Tue Aug 31, 2004 2:52 am Post subject: Re: weird SQL result set from 2 similar SQL statement |
|
|
sorry, what is metalink.
"Eric Hill" <eric (AT) ijack (DOT) net> wrote
| Quote: | But he's summing COL3 in both cases, only omiting COL2 from the
grouping.
Definitely something strange, he should get *different* results, but not
an
empty result in one while not the other.
Yup, you're right. I went back and ran a test and it worked as expected.
Have you tried metalink? Maybe there's some bug in 8 that you're running
into...
Eric
|
|
|
| Back to top |
|
|
Eric Hill
Guest
|
| Posted: Tue Aug 31, 2004 4:22 am Post subject: Re: weird SQL result set from 2 similar SQL statement |
|
|
http://metalink.oracle.com
It's Oracle's complete knowledge base.
Eric
|
|
| Back to top |
|
|
|
|
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum
|
|
FAQ
Search
Memberlist
Usergroups
Register
Profile
Log in to check your private messages
Log in
