Home » SQL & PL/SQL » SQL & PL/SQL » avoiding null results
avoiding null results [message #276395] Thu, 25 October 2007 01:43 Go to next message
RyjuRaju
Messages: 48
Registered: September 2007
Member
Hi
When i tried to execute a query like this
SELECT PRODUCTS.PROD_CATEGORY_DESC,
CHANNELS.CHANNEL_DESC,TO_CHAR(AVG(UNIT_COST), '9,999,999,999') COST$
FROM COSTS, PRODUCTS,CHANNELS
WHERE COSTS.PROD_ID=PRODUCTS.PROD_ID
AND COSTS.CHANNEL_ID=CHANNELS.CHANNEL_ID
AND CHANNELS.CHANNEL_DESC IN ('Direct Sales', 'Internet','Partners')
GROUP BY
rollup(PRODUCTS.PROD_CATEGORY_DESC,CHANNELS.CHANNEL_DESC);

i got the required results along with some unwanted null rows.
For eg,in product category column,there is written null along with the calculated results.How can i avoid this problem
many thanks
Ryju
Re: avoiding null results [message #276398 is a reply to message #276395] Thu, 25 October 2007 01:50 Go to previous messageGo to next message
dhananjay
Messages: 635
Registered: March 2002
Location: Mumbai
Senior Member
hi,

so just include in the where clause product_catogery is not null


regards,

[Updated on: Thu, 25 October 2007 01:51]

Report message to a moderator

Re: avoiding null results [message #276403 is a reply to message #276395] Thu, 25 October 2007 01:56 Go to previous messageGo to next message
RyjuRaju
Messages: 48
Registered: September 2007
Member
Hi
This doesnt work.I tried
 SELECT PRODUCTS.PROD_CATEGORY_DESC,CHANNELS.CHANNEL_DESC,
TO_CHAR(AVG(UNIT_COST), '9,999,999,999') COST$
FROM COSTS, PRODUCTS,CHANNELS
WHERE COSTS.PROD_ID=PRODUCTS.PROD_ID
AND COSTS.CHANNEL_ID=CHANNELS.CHANNEL_ID
AND CHANNELS.CHANNEL_DESC IN ('Direct Sales', 'Internet','Partners')
AND PRODUCTS.PROD_CATEGORY_DESC IS NOT NULL
AND CHANNELS.CHANNEL_DESC IS NOT NULL
GROUP BY
rollup(PRODUCTS.PROD_CATEGORY_DESC,CHANNELS.CHANNEL_DESC); 


still got the same result.Any ideas?
many thanks
Ryju
Re: avoiding null results [message #276405 is a reply to message #276403] Thu, 25 October 2007 01:58 Go to previous messageGo to next message
vamsi kasina
Messages: 2107
Registered: October 2003
Location: Riyadh, Saudi Arabia
Senior Member
Sample data and output please!

By
Vamsi
Re: avoiding null results [message #276407 is a reply to message #276395] Thu, 25 October 2007 01:59 Go to previous messageGo to next message
Michel Cadot
Messages: 64110
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Don't use ROLLUP if you don't want rollup results.

Regards
Michel
Re: avoiding null results [message #276410 is a reply to message #276395] Thu, 25 October 2007 02:03 Go to previous messageGo to next message
RyjuRaju
Messages: 48
Registered: September 2007
Member
Hi
sample output is
prod_category_desc channel_desc cost$
Photo internet 220
Photo (null) 234
(null) direct 234

The required ouput is just like this except these null rows.

cheers
Ryju
Re: avoiding null results [message #276412 is a reply to message #276395] Thu, 25 October 2007 02:05 Go to previous messageGo to next message
RyjuRaju
Messages: 48
Registered: September 2007
Member
Hi
I have to roll up datas.With simple group by ,i didn't get the required output format.So whats problem?

cheers
Ryju
Re: avoiding null results [message #276422 is a reply to message #276412] Thu, 25 October 2007 02:17 Go to previous messageGo to next message
Michel Cadot
Messages: 64110
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
The problem is you don't tell us what you want, you don't follow the rules and don't format your post, you don't answer to what is asked: "sample data and output" (post create table and insert statements and expected output with these data).

Regards
Michel
Re: avoiding null results [message #276435 is a reply to message #276395] Thu, 25 October 2007 02:54 Go to previous messageGo to next message
RyjuRaju
Messages: 48
Registered: September 2007
Member
i cant post the create table and insert statements here because that is too large to post.Now my problem is more simplified with the query
 SELECT PRODUCTS.PROD_CATEGORY_DESC,
CHANNELS.CHANNEL_DESC,
TO_CHAR(AVG(UNIT_COST), '9,999,999,999') COST$
FROM COSTS, PRODUCTS,CHANNELS
WHERE COSTS.PROD_ID=PRODUCTS.PROD_ID
AND CHANNELS.CHANNEL_ID= COSTS.CHANNEL_ID
AND CHANNELS.CHANNEL_DESC IN ('Direct Sales', 'Internet','Partners')
GROUP BY
PRODUCTS.PROD_CATEGORY_DESC,CHANNELS.CHANNEL_DESC; 

i tried to execute this query without rollup.So everything is fine except that the data is not rolled up.So when i use roll up,nulls are also comming.So i think the problem comes during roll up time.

output is
 prod_category_desc      channel_desc    cost$
        photo                 direct         200 
        (null)                online         132
        photo                 (null)         100 

Re: avoiding null results [message #276440 is a reply to message #276435] Thu, 25 October 2007 03:00 Go to previous messageGo to next message
pablolee
Messages: 2834
Registered: May 2007
Location: Scotland
Senior Member
Quote:

So i think the problem comes during roll up time.

I would be more incline to suggest that the problem is more likely to lie in 1 of 2 other possible scenarios.
1. You misunderstand the true functionality of rollup.
2. You fully understand the functionality of rollup and we are mis-understanding your question.
If it is the former, read the documentation (pay attention to mention of the grouping function) If it is the latter then this is fundamentally your issue as you refuse to post a valid test case (remember you can simplify it so long as it remains representative)
Re: avoiding null results [message #276452 is a reply to message #276395] Thu, 25 October 2007 03:39 Go to previous messageGo to next message
RyjuRaju
Messages: 48
Registered: September 2007
Member
Hi
thank you very much for all replies.I have almost solved my problem.I dont have to use roll up in this case.Simple group by is enough.But can anyone tell me to arrange the output of group by together.My group by statement produces results
for eg:
1st row:photo
second row:computer
third row:photo.
is it possible to arrange photo together so as to make it pretty looking?
thanks
Ryju
Re: avoiding null results [message #276456 is a reply to message #276395] Thu, 25 October 2007 03:51 Go to previous message
RyjuRaju
Messages: 48
Registered: September 2007
Member
Thanks everybody.I got it using group by and order by
cheers
Ryju
Previous Topic: ORA-00001: unique constraint Violation
Next Topic: Accessing DBA% tables in a SP
Goto Forum:
  


Current Time: Mon Dec 05 11:17:19 CST 2016

Total time taken to generate the page: 0.06214 seconds