5 Replies Latest reply: Nov 23, 2012 11:01 AM by Frank Kulash RSS

    SQL Query problem

    RajeshKanna
      Sir,

      I have two tables,my first table account is having only one column acc_no.

      and the second table Transaction is having 2 columns acc_no and transactiondate.

      My Requirement is to display the account no's along with transactiondate if there is no transaction on a particular month.

      I am using below procedure to display on a paricular date,
      procedure notrans(p_date in date)
      is
      begin
      
      select acc_no
      from account
      minus
      select acc_no
      from transaction
      where to_char(transaction_date,'DD-MON-YY')=p_date;
      
      dbms_output.put_line(acc_no||p_date);
      
      end notrans;
      but i want to display no transaction on a particular month along with accountno and transactiondate, can any one help me.

      Regards,
      Rajesh

      Edited by: 969052 on Nov 22, 2012 11:11 AM
        • 1. Re: SQL Query problem
          TPD-Opitz
          Welcome to the forum.
          969052 wrote:
          My Requirement is to display the account no's along with transactiondate if there is no transaction on a particular month.
          where do you get a transactiondate from when no transaction has occured?

          bye
          TPD
          • 2. Re: SQL Query problem
            Frank Kulash
            Hi, Rajesh,

            Welcome to the forum!

            Whenever you have a problem, please post CREATE TABLE and INSERT statements for a little sample data, and the results you want from that data. Explain how you get those results from that data.
            If you can show what you want to do using commonly available tables (such as the tables in the SCOTT schema), then you don't need to post CREATE TABLE or INSERT statements, just the results you want and the explanation.
            Either way, always say which version of Oracle you're using (e.g., 11.2.0.3.0).
            See the forum FAQ {message:id=9360002}

            I think you're asking something like this:
            Using the scott.emp table, we can see the hiredates of employees in a given department like this:
            SELECT       ename
            ,       hiredate
            FROM       scott.emp
            WHERE       deptno     = 30
            ORDER BY  hiredate
            ;
            Output:
            ENAME      HIREDATE
            ---------- -----------
            ALLEN      20-Feb-1981
            WARD       22-Feb-1981
            BLAKE      01-May-1981
            TURNER     08-Sep-1981
            MARTIN     28-Sep-1981
            JAMES      03-Dec-1981
            and we can summarize that data by months like this:
            SELECT       TO_CHAR ( TRUNC (hiredate, 'MONTH')
                          , 'Mon-YYYY'
                        )          AS month
            ,       COUNT (*)          AS num_hired
            FROM       scott.emp
            WHERE       deptno     = 30
            GROUP BY  TRUNC (hiredate, 'MONTH')
            ORDER BY  TRUNC (hiredate, 'MONTH')
            ;
            Output:
            MONTH              NUM_HIRED
            ----------------- ----------
            Feb-1981                   2
            May-1981                   1
            Sep-1981                   2
            Dec-1981                   1
            Now say we want to show this same information, but include all the months in that range, whether there were any empoyees hred or not. That is, say, we want to get output like this:
            MONTH              NUM_HIRED
            ----------------- ----------
            Feb-1981                   2
            Mar-1981                   0
            Apr-1981                   0
            May-1981                   1
            Jun-1981                   0
            Jul-1981                   0
            Aug-1981                   0
            Sep-1981                   2
            Oct-1981                   0
            Nov-1981                   0
            Dec-1981                   1
            How can we get those results? That seems to be an outer-join problem; that is, we want to join a table like this:
            A_MONTH
            -----------
            01-Feb-1981
            01-Mar-1981
            01-Apr-1981
            01-May-1981
            01-Jun-1981
            01-Jul-1981
            01-Aug-1981
            01-Sep-1981
            01-Oct-1981
            01-Nov-1981
            01-Dec-1981
            but make it an outer join, so that every month above will be in the result set even if it doesn't match anything in scott.emp. Even if there is no such table, we can generate one (or, actually, a result set that can function as a table) like this:
            SELECT     ADD_MONTHS (first_month, LEVEL - 1)     AS a_month
            FROM     (
                     SELECT  TRUNC (MIN (hiredate), 'MONTH')     AS first_month
                     ,         TRUNC (MAX (hiredate), 'MONTH')     AS last_month
                     FROM    scott.emp
                     WHERE   deptno     = 30
                 )
            CONNECT BY     LEVEL     <= 1 + MONTHS_BETWEEN ( last_month
                                                  , first_month
                                           )
            ;
            and we can outer-join this result set to the scott.emp table, to get the output we ultimately want, like this:
            WITH     all_months     AS
            (
                 SELECT     ADD_MONTHS (first_month, LEVEL - 1)     AS a_month
                 FROM     (
                          SELECT  TRUNC (MIN (hiredate), 'MONTH')     AS first_month
                          ,         TRUNC (MAX (hiredate), 'MONTH')     AS last_month
                          FROM    scott.emp
                          WHERE   deptno     = 30
                      )
                 CONNECT BY     LEVEL     <= 1 + MONTHS_BETWEEN ( last_month
                                                             , first_month
                                                      )
            )
            SELECT       TO_CHAR (a.a_month, 'Mon-YYYY')     AS month
            ,       COUNT (e.hiredate)                 AS num_hired
            FROM              all_months  a
            LEFT OUTER JOIN  scott.emp   e  ON  TRUNC (e.hiredate, 'MONTH') = a.a_month
                                              AND e.deptno                      = 30
            GROUP BY  a.a_month
            ORDER BY  a.a_month
            ;
            • 3. Re: SQL Query problem
              RajeshKanna
              Hi Frank,

              Sorry for not providing details in my previous thread. Here I am explaining the problem again. Please let me know if you need any further details.

              I have two tables,my first table account is having only one column acc_no.
              Create table account(acc_no number(5));
              
              insert into account values(101);
              insert into account values(102);
              insert into account values(103);
              insert into account values(104);
              commit;
              and the second table Transaction is having 2 columns acc_no and transactiondate.
              Create table Transaction(acc_no number(5),Transtype varchar2(20),Transaction_date date);
              
              insert into Transaction values(101,'Saving','21-NOV-12');
              insert into Transaction values(102,'Saving','21-NOV-12');
              insert into Transaction values(102,'Saving','22-NOV-12');
              insert into Transaction values(103,'Saving','23-NOV-12');
              commit;
              My Requirement is to display the account no's which are not having any transactions for a particular month. (I'll pass month as input parameter) And transaction table will have only one year data.

              desired output:
               101 01-NOV-12
               102 01-NOV-12
               103 01-NOV-12
               104 01-NOV-12
               101 02-NOV-12
               102 02-NOV-12
               103 02-NOV-12
               104 02-NOV-12
               ............
              .............
               103 21-NOV-12
               104 21-NOV-12
               101 22-NOV-12
               103 22-NOV-12
               104 22-NOV-12
               101 23-NOV-12
               102 23-NOV-12
               104 23-NOV-12
               ............
              .............
              
               101 30-NOV-12
               102 30-NOV-12
               103 30-NOV-12
               104 30-NOV-12
              I am using below procedure to display accounts which are not having any transactions on a paricular date,
              procedure notrans(p_date in date)
              is
              begin
              
              select acc_no
              from account
              minus
              select acc_no
              from transaction
              where to_char(transaction_date,'DD-MON-YY')=p_date;
              
              dbms_output.put_line(acc_no||p_date);
              
              end notrans;
              Regards,
              Rajesh

              Edited by: 969052 on Nov 22, 2012 8:15 PM
              Added CREATE TABLE and INSERT STMT scripts
              • 4. Re: SQL Query problem
                rp0428
                >
                and the second table Transaction is having 2 columns acc_no and transactiondate.

                Create table Transaction(acc_no number(5),Transtype varchar2(20),Transaction_date date);
                >
                Really? Would you like to count those columns again and see how many you get the second time?
                >
                My Requirement is to display the account no's which are not having any transactions for a particular month. (I'll pass month as input parameter) And transaction table will have only one year data.

                desired output:

                101 01-NOV-12
                102 01-NOV-12
                >
                Please explain how that output is displaying account nos which are not having any transactions for a particular month. Looks like those are account nos that DO have transactions for November.
                >
                I am using below procedure to display accounts which are not having any transactions on a paricular date,
                >
                No you're not. That procedure won't even compile because of the syntax errors.

                You can't do a SELECT in PL/SQL unless you give Oracle some place to put the data that is selected. You need to either select INTO a variable or BULK COLLECT into a collection. And what do you expect this line to do?
                where to_char(transaction_date,'DD-MON-YY')=p_date;
                You are using TO_CHAR to convert a string to a string and then compare that string to a date? Shouldn't that be TO_DATE instead TO_CHAR?
                And if 'p_date' has a time component it will probably never match your transaction date because it won't have one. So 'p_date' needs to be TRUNC'd.

                Also, you should not store dates in VARCHAR2 columns. They should be stored in DATE columns.
                • 5. Re: SQL Query problem
                  Frank Kulash
                  Hi, Rajesh,
                  969052 wrote:
                  ... and the second table Transaction is having 2 columns acc_no and transactiondate.
                  Create table Transaction(acc_no number(5),Transtype varchar2(20),Transaction_date date);
                  
                  insert into Transaction values(101,'Saving','21-NOV-12');
                  insert into Transaction values(102,'Saving','21-NOV-12');
                  insert into Transaction values(102,'Saving','22-NOV-12');
                  insert into Transaction values(103,'Saving','23-NOV-12');
                  commit;
                  Transaction_date is a DATE; don't try to insert a VARCHAR2 value, such as '21-NOV-12' into a DATE column. Use TO_DATE to convert a VARCHAR2 into a DATE.
                  My Requirement is to display the account no's which are not having any transactions for a particular month. (I'll pass month as input parameter) And transaction table will have only one year data.
                  The query below will work regardless of how many years' data are in the transaction table.
                  desired output:
                  .............
                  103 21-NOV-12
                  104 21-NOV-12
                  101 22-NOV-12
                  103 22-NOV-12
                  104 22-NOV-12
                  101 23-NOV-12
                  102 23-NOV-12
                  104 23-NOV-12
                  ............
                  Here's one way to do that:
                  VARIABLE  month_wanted  VARCHAR2 (8)
                  EXEC     :month_wanted := 'Nov-2012';
                  
                  
                  WITH     all_days     AS
                  (
                       SELECT  TO_DATE (:month_wanted, 'Mon-YYYY') + LEVEL - 1     AS a_date
                       FROM     dual
                       CONNECT BY     LEVEL <= ADD_MONTHS ( TO_DATE (:month_wanted, 'Mon-YYYY')
                                                         , 1
                                               )
                                      - TO_DATE (:month_wanted, 'Mon-YYYY')
                  )
                  SELECT    a.acc_no
                  ,       d.a_date
                  FROM              account      a
                  CROSS JOIN       all_days     d
                  LEFT OUTER JOIN  transaction  t  ON   t.acc_no            = a.acc_no
                                                      AND  t.transaction_date  = d.a_date
                  WHERE       t.acc_no     IS NULL
                  --AND       d.a_date     BETWEEN  TO_DATE ('20-Nov-2012', 'DD-Mon-YYYY')     -- FOR TESTING ONLY
                  --                 AND      TO_DATE ('24-Nov-2012', 'DD-Mon-YYYY')     -- FOR TESTING ONLY
                  ORDER BY  d.a_date
                  ,            a.acc_no
                  ;
                  I am using below procedure to display accounts which are not having any transactions on a paricular date,
                  As you can see from my example, you can do this without PL/SQL. Of course, there are good reasons why you might want to use PL/SQL. If you happen to have one, then you can do the query above in PL/SQL. As mentioned in the last reply, you just have to say what you want to do with the results.