Results 1 to 4 of 4
  1. #1
    Join Date
    Apr 2006
    Posts
    2

    Unanswered: outer join query

    Hi this is my first post so please excuse me if Im not clear on something

    I have a simple schema I need to report on

    catfiles
    catno
    fileno

    downloads
    fileno
    entrydate
    .
    .

    what I want to do is this essentially
    Code:
    SELECT to_char(d.entrydate, 'MONTH YYYY'), count(*)
    from products.downloads d, products.catfiles cf
    where cf.fileno = d.fileno
    and cf.catno = 123456 
    and d.entrydate >= to_date('01 JAN 2006', 'DD MONTH YYYY')
    and d.entrydate <= to_date('01 MARCH 2006', 'DD MONTH YYYY')
    group by to_char(d.entrydate, 'MONTH YYYY')
    order by to_date(to_char(d.entrydate, 'MONTH YYYY'),'Month YYYY') asc
    so im counting how often a file was downloaded grouped by months, so I think Ive got that right, im sure theres a 'between' date function but irralivant at the moment.

    the problem I have is that in this instance file '123456' was only downloaded in 'feburary' so I get one tuple returned say

    FEB, 5

    (downloaded 5 times in FEB) and what I want is to get is this

    JAN, 0
    FEB, 5
    MAR, 0

    a return for every month in that time period, and 0 if nothing was present. You still with me? okay so yeah thats what I need. I'll also post my attempt at using anynomuse views to do this

    Code:
    SELECT to_date(to_char(d.entrydate, 'Month YYYY'), 'Month YYYY'), count(*)
    from 
    products.downloads d, products.catfiles cf, 
    (
        SELECT to_date(to_char(ADD_MONTHS(to_date('01 JAN 2006', 'DD MONTH YYYY'), (ROWNUM - 1)), 'MONTH' ) || ' ' || to_char(ADD_MONTHS(to_date('01 JAN 2006', 'DD MONTH YYYY'), (ROWNUM - 1)), 'YYYY' ), 'Month YYYY') entrydate
        FROM 
        (
            SELECT level 
            FROM DUAL CONNECT BY level <= MONTHS_BETWEEN(to_date('01 MAY 2006', 'DD MONTH YYYY'), to_date('01 JAN 2006', 'DD MONTH YYYY'))+1
        )
    ) t
    where cf.fileno = d.fileno
    and cf.catno = 123456
    and d.entrydate >= to_date('01 JAN 2006', 'DD MONTH YYYY')
    and d.entrydate <= to_date('01 MAY 2006', 'DD MONTH YYYY')
    and t.entrydate = to_date(to_char(d.entrydate(+), 'Month YYYY'), 'Month YYYY')
    group by to_date(to_char(d.entrydate, 'Month YYYY'), 'Month YYYY')
    The inner view there simply returns a result set of month - years for a given time period, and I was hoping to outer join that to my data and get what I was after. All the to_date to_char garbage there is simply to massage the data into the right format (grouping by month and dropping the hours seconds etc, dont know if it needed it but I was getting despirate :P )

    I can do this programmatically but there is a real drive to get this run nativly on the database, so it is very much the preference.

    So fire away ppl, any ideas? need me to clarify anything?

    cheers,
    Mik.
    Last edited by mikgan; 05-01-06 at 00:13.

  2. #2
    Join Date
    Jan 2004
    Posts
    370
    Does this help?

    Code:
    SELECT 	to_date(
    		 to_char(decode(d.entrydate, null, t.entrydate
    				 , d.entrydate), 'Month YYYY'), 'Month YYYY'), 
    	count(d.entrydate)
    from 
    	downloads d, 
    	catfiles cf, 
    (
        SELECT to_date(to_char(ADD_MONTHS(to_date('01 JAN 2006', 'DD MONTH YYYY'), (ROWNUM - 1)), 'MONTH' ) || ' ' || to_char(ADD_MONTHS(to_date('01 JAN 2006', 'DD MONTH YYYY'), (ROWNUM - 1)), 'YYYY' ), 'Month YYYY') entrydate
        FROM 
        (
            SELECT level 
            FROM DUAL CONNECT BY level <= MONTHS_BETWEEN(to_date('01 MAY 2006', 'DD MONTH YYYY'), to_date('01 JAN 2006', 'DD MONTH YYYY'))+1
        )
    ) t
    where  t.entrydate = to_date(to_char(d.entrydate(+), 'Month YYYY'), 'Month YYYY')
    and	(cf.fileno = d.fileno or d.fileno is null)
    and (d.entrydate >= to_date('01 JAN 2006', 'DD MONTH YYYY')
    and d.entrydate <= to_date('01 MAY 2006', 'DD MONTH YYYY') or d.entrydate is null)
    and	cf.catno = 123456 
    group by to_date(
    		 to_char(decode(d.entrydate, null, t.entrydate
    				 , d.entrydate), 'Month YYYY'), 'Month YYYY')
    order by to_date(
    		 to_char(decode(d.entrydate, null, t.entrydate
    				 , d.entrydate), 'Month YYYY'), 'Month YYYY')

  3. #3
    Join Date
    Apr 2006
    Posts
    2
    no, funortunatly didnt have the desired result, it seemes to concatonate the result of one of the months in with another.

    Can you explain what you were trying to do and I may still be able to make it fit.

    I've got a few hooks out there at the moment, so if I get a positive result I'll be sure to post it for all to see
    Last edited by mikgan; 05-01-06 at 20:19.

  4. #4
    Join Date
    Sep 2004
    Location
    London, UK
    Posts
    565
    How about this:

    Code:
    CREATE TABLE catfiles
    ( catno INTEGER NOT NULL
    , fileno INTEGER NOT NULL );
    
    CREATE TABLE downloads
    ( fileno INTEGER NOT NULL
    , entrydate DATE NOT NULL );
    
    INSERT ALL
    INTO catfiles VALUES (123456, 123456)
    INTO downloads VALUES (123456, DATE '2006-02-01')
    INTO downloads VALUES (123456, DATE '2006-02-02')
    INTO downloads VALUES (123456, DATE '2006-02-03')
    INTO downloads VALUES (123456, DATE '2006-02-04')
    INTO downloads VALUES (123456, DATE '2006-02-05')
    SELECT * FROM dual;
    Code:
    SELECT TO_CHAR(m.month,'MON YYYY') AS month
         , COUNT(d.entrydate)
    FROM   ( SELECT ADD_MONTHS(DATE '2006-01-01', rownum -1) AS month
             FROM   dual
             CONNECT BY LEVEL < 4 ) m
         , downloads d
         , catfiles cf
    WHERE  TRUNC(d.entrydate(+),'MONTH') = m.month
    AND    cf.fileno(+) = d.fileno
    AND    cf.catno(+) = 123456
    GROUP BY m.month
    ORDER BY m.month;
    
    MONTH    COUNT(D.ENTRYDATE)
    -------- ------------------
    JAN 2006                  0
    FEB 2006                  5
    MAR 2006                  0
    
    3 rows selected.
    I did originally include the condition

    AND d.entrydate(+) BETWEEN DATE '2006-01-01' AND DATE '2006-03-01'

    but then I realised that the date range is already constrained by the join to m.

    The outer join might be clearer in ANSI syntax:

    Code:
    SELECT TO_CHAR(m.month,'MON YYYY') AS month
         , COUNT(d.entrydate)
    FROM   ( SELECT ADD_MONTHS(DATE '2006-01-01', rownum -1) AS month
             FROM   dual
             CONNECT BY LEVEL < 4 ) m
           LEFT OUTER JOIN downloads d
           ON TRUNC(d.entrydate,'MONTH') = m.month
           LEFT OUTER JOIN catfiles cf
           ON  cf.fileno = d.fileno
           AND cf.catno = 123456
    GROUP BY m.month
    ORDER BY m.month;
    
    MONTH    COUNT(D.ENTRYDATE)
    -------- ------------------
    JAN 2006                  0
    FEB 2006                  5
    MAR 2006                  0
    
    3 rows selected.
    Last edited by WilliamR; 05-02-06 at 03:37.

Posting Permissions

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