sql query not returns zero

[swstos]

Hi...

The query below returns the number of times that each subject has been used, but if the subject has been used zero times it does not return it.


Can please anyone tell me how can i change it in order to return zero as well...

select offer_details.subject, count(offer.offerdate) AS "Number Of Times"
from offer, offer_details
where offer.offerDate > '1/jan/00'
AND offer.offercode = offer_details.offercode
group by offer_details.subject;

Thanks on advance...

[mkumarnk]

Just check that date format is correct .This might be tha time stamp problem with the date.U can try like this

trunc(offer.offerDate)=trunc(to_date('01/jan/00','dd/mon/yy').

Hope this would help u

[swstos]

the date its correct... it returns the values after that day but it does not return the zero ( e.g. when a subject is not been used it doesnot returns (subject name No of times ))
anything 0

[mkumarnk]

This offerdate's type is date or not.

Did u try in this way

trunc(offer.offerDate)>trunc(to_date('01/jan/00','dd/mon/yy').

[swstos]

It is a date... i tried it and is doing the same thing...

Returns the subjects that have been used after that date... It also suppose to return the subject that havent been used after that date and in the column <number of times> have 0...

[DaPi]

It sounds like you need an OUTER JOIN - do I understand correctly that there are rows in offer_details with no corresponding rows in offer? (sounds very strange to me . . .)

select offer_details.subject, count(offer.offerdate) AS "Number Of Times"
from offer, offer_details
where (offer.offerDate > '1/jan/00' OR offer.offerDate is NULL)
AND offer.offercode(+) = offer_details.offercode
group by offer_details.subject;

[nandu]

I believe Dapi's query is an answer for your problem.

Are you finished yet?

Please continue the thread if you are still in trouble.

Cheers
Nandu

[ccastaneda]

Originally posted by DaPi
The query below returns the number of times that each subject has been used, but if the subject has been used zero times it does not return it.


I understand this...

If a subject has been used zero times in an offer, there is no sense that its record appears in the offers_details table.. Of course you have the last word here because this situation depends of the logic of your app.

But if THIS would be your actual situation, you'll have to include your details master table in the logic of your query to get the infor that you want using.

I am assuming that the details master table name is det_master, and its PK is subject.

This query will give you the info that you need:

select offer_details.subject, count(offer.offerdate) AS "Number Of Times"
from offer, offer_details
where (offer.offerDate > '1/jan/00' OR offer.offerDate is NULL)
AND offer.offercode(+) = offer_details.offercode
group by offer_details.subject
union
select det_master.subject, 0
from det_master
where
not exists
(
select 1
from offer, offer_details
where
offer_details.subject=det_master.subject
AND (offer.offerDate > '1/jan/00' OR offer.offerDate is NULL)
AND offer.offercode(+) = offer_details.offercode
);

Is this what you need?????...
Let us know or provide more info.. because is not clear what exactly do you want..

[DaPi]

quote:
--------------------------------------------------------------------------------
Originally posted by DaPi
The query below returns the number of times that each subject has been used, but if the subject has been used zero times it does not return it.

--------------------------------------------------------------------------------

'twasn't I !

[ccastaneda]

'twasn't I ! [/B]

You're right Dapi.. my mistake..

Wrong quote button i think.. :D