Results 1 to 5 of 5
  1. #1
    Join Date
    May 2012
    Posts
    3

    Unanswered: CONNECT BY LEVEL in a query?

    I have a basic query that returns SUM's broken out by YYYY-MM and Date Ranged. If there is a gap in the data for a particular month, I get no row back. I want to force it to return a row for each month in the range regardless if there is data to be summed or not.

    Code:
    CREATE
      TABLE MY_DATA
      (
        ADDED_DATE DATE
      , CONTENT_SIZE NUMBER
      );
    INSERT
    INTO
      MY_DATA VALUES
      (
        '01-JAN-2012'
      , 100
      );
    INSERT
    INTO
      MY_DATA VALUES
      (
        '01-JAN-2012'
      , 100
      );
    INSERT
    INTO
      MY_DATA VALUES
      (
        '01-MAR-2012'
      , 100
      );
    INSERT
    INTO
      MY_DATA VALUES
      (
        '01-MAR-2012'
      , 100
      );
    --
    --
    --
    SELECT
      A.ADDED_DATE
    , SUM(A.CONTENT_SIZE) CONTENT_SIZE
    FROM
      (
        SELECT
          TO_CHAR(A.ADDED_DATE, 'YYYY-MM') ADDED_DATE
        , A.CONTENT_SIZE
        FROM
          MY_DATA A
        WHERE
          A.CONTENT_SIZE IS NOT NULL
        AND A.ADDED_DATE BETWEEN '01-JAN-2012' AND '01-APR-2012'
      )
      A
    GROUP BY
      A.ADDED_DATE
    ORDER BY
      A.ADDED_DATE ASC;
    --
    --
    WHAT I GET: 
    ADDED_D CONTENT_SIZE
    ------- ------------
    2012-01          200
    2012-03          200
    
    --
    --
    WHAT I WANT:
    ADDED_D CONTENT_SIZE
    ------- ------------
    2012-01          200
    2012-02            0
    2012-03          200
    My guess is that CONNECT BY LEVEL needs to be used here, but I cannot get it to work. Ideally, I would like to control how many months are forced in the data returned, showing months with no data as zero (see above: 2012-02 = 0 with no data in dataset)

    Any help is appreciated.

    Regards,
    Mark

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    OraFAQ Forum: General Puzzle n00 - Row generator *

    apply similar technique as shown in URL above
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    May 2012
    Posts
    3
    Great. That will work. Thanks a bunch!

  4. #4
    Join Date
    May 2012
    Posts
    3
    I hate to be a pill, but I am revisiting this today as I got sidetracked. I could not get this to work correctly. Can somebody employ the connect by with my original query to include a row for the February month that is missing in the dataset?

    Thanks.

  5. #5
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    Code:
      1  select to_char(add_months(to_date('2011-12','YYYY-MM'),LEVEL),'YYYY-MM') YYYY_MM FROM DUAL
      2* CONNECT BY LEVEL < 13
    07:27:37 SQL> /
    
    YYYY_MM
    -------
    2012-01
    2012-02
    2012-03
    2012-04
    2012-05
    2012-06
    2012-07
    2012-08
    2012-09
    2012-10
    2012-11
    
    YYYY_MM
    -------
    2012-12
    
    12 rows selected.
    
    07:27:38 SQL>
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •