1 Reply Latest reply on Jan 23, 2013 4:28 PM by Frank Kulash

    Problem in generating Query

      Hello ,

      I am having two tables cdr and cdr_01


      id calling_no called_no call_date duration
      1 9899767876 0112356788 01-jan-2012 160
      2 9999758743 0244356788 01-jan-2012 191


      id calling_no called_no call_date duration
      1 9999758743 0244356788 01-jan-2012 190
      2 9899767876 0112356788 01-jan-2012 163

      i want output corresponding to the two tables by matching each column instaed of id
      and if difference of duration is -1, 0 , 1 than that is to be ignore rest to be displayed.

      output :

      calling_no called_no call_date duration
      9899767876 0112356788 01-jan-2012 163

      please help

        • 1. Re: Problem in generating Query
          Frank Kulash
          Hi, Esha,

          Welcome to the forum!
          As it happens, this is the wrong forum. Your question doesn't have anything to do with the iSQL*Plus or SQL*Plus front ends; it's a question about how to do soemthing in the SQL language. In the future, you should post questions like this in the PL/SQL and SQL. You'll get better replies faster if you do.

          Join conditions can include any kinds of conditions. The most frequent operator in join conditions is =, but all other kinds, such as NOT IN, are allowed, too. So you can do something like this:
          SELECT  c1.calling_no
          ,      c1.called_no
          ,      c1.call_date
          ,      c1.duration
          FROM     cdr     c
          JOIN     cdr_01     c1  ON   c1.id          = c.id
                        AND      c1.calling_no     = c.calling_no
                        AND      c1.called_no     = c.called_no
                        AND      c1.duration - c.duration
                                           NOT IN (-1, 0, 1)
          What results do you want if duration is NULL in one table, but not the other, and the rows match on the other columns? What if duration is NULL in both tables?
          Is duartion always an integer? What results would you want if duration=163 in one table, and 163.5 in the other table, on rows where the other columns matched?


          I hope this answers your question.
          If not, mark this thread as "Answered", and open a new thread in the PL/SQL and SQL. Post a little sample data (CREATE TABLE and INSERT statements, relevant columns only) for all tables involved, and also post the results you want from that data. If the special cases I mentioned (duration is NULL, or non-integer) can occur, inlcude examples in your data and results.
          Explain, using specific examples, how you get those results from that data.
          Always say which version of Oracle you're using (e.g.,
          See the forum FAQ {message:id=9360002}