4 Replies Latest reply: Apr 1, 2013 12:42 PM by mbb774 RSS

    order by 'mon-yyyy' desc

    mbb774
      Hi experts,

      i wrote a query like below.

      SQL> SELECT mon."months",
      2 COUNT (DECODE (e1.ename,
      3 'RAVI', 1,
      4 DECODE (e1.ename, 'KIRAN', 1, NULL)
      5 )
      6 ) AS "num-review"
      7 FROM (SELECT TO_CHAR (ADD_MONTHS (SYSDATE, LEVEL - 7),
      8 'MON-YYYY'
      9 ) "months"
      10 FROM DUAL
      11 CONNECT BY LEVEL <= 18
      12 ORDER BY LEVEL) mon,
      13 (SELECT ename, hiredate, TO_CHAR (hiredate, 'MON-YYYY') "Month"
      14 FROM emp_copy) e1
      15 WHERE mon."months" = e1."Month"(+)
      16 GROUP BY "months";

      months num-review
      -------- ----------
      APR-2013 0
      AUG-2013 0
      DEC-2012 1
      DEC-2013 0
      FEB-2013 2
      FEB-2014 0
      JAN-2013 1
      JAN-2014 0
      JUL-2013 0
      JUN-2013 0
      MAR-2013 0

      months num-review
      -------- ----------
      MAY-2013 0
      NOV-2012 0
      NOV-2013 0
      OCT-2012 1
      OCT-2013 0
      SEP-2012 1
      SEP-2013 0

      18 rows selected.

      here i am getting a problem on months column, i supposed to get output like

      Sep-2012
      Oct-2012
      Nov-2012
      Dec-2012
      Jan-2013
      Feb-2013
      Mar-2013
      Apr-2013
      May-2013
      Jun-2013
      Jul-2013
      Aug-2013
      Sep-2013
      Oct-2013
      Nov-2013
      Dec-2013
      Jan-2014
      Feb-2014

      can you guys please help in getting the output like above..