Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Help decode function multiple tables

Posted on 2004-09-11
5
Medium Priority
?
914 Views
Last Modified: 2008-01-09
Hi,

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)))
                  )as y#COMPLAINTRATIO                          
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.

Please help!

Thank you,
Pratibha
0
Comment
Question by:pratibharao
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
5 Comments
 
LVL 7

Expert Comment

by:yoren
ID: 12037438
I'm not sure I understand. What info does table 1 have versus table 2?
0
 
LVL 4

Expert Comment

by:mottor
ID: 12037873
What is in this tables?
Where is your join condition?
0
 

Accepted Solution

by:
Captive1180 earned 400 total points
ID: 12131808
Try this

I think you wanna to something like this:

You have a column which has diff values in the form of  'aol.com' or it may be 'msn.com' etc.
Now according to this values you wanna perform some calculation.

You the structure of you decode function should be something like this
decode(table.column name,value to be checked,operation to be performed if the 2nd parameter is true)

So it should be something like this:

select DECODE(T1.DOMAINNAME, 'aol.com',
                      T2.DOMAINNAME, 'aol.com',T1.TOTAL_COMPLAINTS)/T2.SENT/1000000
                      )as y#COMPLAINTRATIO                      
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')

So do make some changes according to your need ,but keep in mind the decode function structure.

All the best
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article started out as an Experts-Exchange question, which then grew into a quick tip to go along with an IOUG presentation for the Collaborate confernce and then later grew again into a full blown article with expanded functionality and legacy…
Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.

719 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question