Help decode function multiple tables
Posted on 2004-09-11
I need to run a calculation and plot graphs using a different application off the data I retrieve from two separate tables. The idea is to plot complaints generated from each of the sources in a common bar graph over a time period. For ex, If on a particular day, AOL generated 300 complaints and MSN generated, 200 complaints, I would run a calculation that probably gave a result of 3 and 2 respectively for the two providers and on the bar graph, the aol complaints will be displayed from 0-3 and yahoo complaints from 3-5. This will repeat for all the days chosen.
I am trying to use DECODE to select the providers. This is my query:
select DECODE(T1.DOMAINNAME, 'aol.com',
DECODE(T2.DOMAINNAME, 'aol.com', ((T1.TOTAL_COMPLAINTS)/T2.SENT/1000000)))
FROM TABLE1 T1, TABLE2 T2
WHERE TRUNC(T2.SENT_DATE) = to_date('08/12/2004','MM/DD/YYYY')
AND TRUNC(T1.SENT_DATE) = to_date('08/12/2004','MM/DD/YYYY')
What I seem to be getting is a some sort of a sum and not the answer for aol for that particular day. I cannot achieve the same using where clause for the reasons mentioned above.