Results 1 to 8 of 8

Thread: query problem

  1. #1
    Join Date
    Jul 2003
    Posts
    21

    Unanswered: query problem

    I have 4 tables:

    Date, Total1
    Date, Total2
    Date, Total3
    Date, Total4

    for a particular table, the data may look like this
    aug1 1
    aug2 2
    aug4 3
    aug5 4
    ....
    All the way to Aug30

    Whenever there is no data, there will be no entry in the table (eg. Aug3)

    Now what I want to do is join the above 4 tables
    How would I write my query so that if one of the tables has data for a particular day, this would be shown in the result, while the tables without data for that day would show 0.

    date total1 total2 total3 total4
    aug1 1 0 3 0
    aug2 3 3 0 1
    aug3 0 0 0 2
    ...

  2. #2
    Join Date
    Jul 2003
    Location
    US
    Posts
    314

    Full Outer Join is required

    Hi

    As per u requirement, you need to perform a full outer join on the tables.
    You could search for the syntax of the full outer join.

    I would assume you write it something like

    select t1.date, t1.total, t2.total from t1 full outer join t2 on (t1.date=t2.date) ........

    where ... are similar thing continued for t3 and t4.

    But make sure you first get the syntax of full outer join.

    Thanx and Regards
    Aruneesh

  3. #3
    Join Date
    Nov 2002
    Location
    Desk, slightly south of keyboard
    Posts
    697
    I believe up to Oracle 9, ANSI join syntax was not supported, and from 9 onwards was bugged in some versions... this gives you (old but workiing Oracle) syntax....

    select t1.datecol, t2.datecol, t3,datecol
    from t1,t2,t3
    where t2.datecol (+) = t1.datecol and
    t3.datecol (+) = t2.datecol and
    etc

    this will of course only return dates that already exist in T1, ie if it doesnt exist there you won't get any row at all.

    I suspect however that you want all dates for a date range, in which case the SQL will be somewhat more complex - probably resorting to our good freind user_objects with a typecast of rownum to the first date in your range.

    Hth
    Bill

  4. #4
    Join Date
    Jul 2003
    Posts
    21
    I'm a sql newbie so what exactly are the 'user-objects' ?

  5. #5
    Join Date
    Nov 2002
    Location
    Desk, slightly south of keyboard
    Posts
    697
    Hi,

    yes, sorry. Sometimes you want to generate lots of values purely to seed a query such as yours with data. USER_OBJECTS is a view on Oracle which (usually) contains lots of rows. ALL_OBJECTS will definately contain enough for you to satisfy a month of dates.

    USER_OBJECTS contains a row for every object owned by your schema(user), and ALL_OBJECTS - yes, one for every object on the database. An object being a table/view/trigger/package etc.

    You might for examples do this...
    select rownum+to_date('31/08/2003','DD/MM/YYYY')
    from all_objects
    where rownum <=31

    This gives a resultset guaranteed to contain enough dates for each date of any month following 31st August. You would then use this as the base table in your joins (usually as a subselect - or inline query).

    select *
    from (inline query above but using some date ranges) b,
    t1,
    t2,
    t3
    where t1.datecol (+) = b.datecol and
    t2.datecol (+) = t1.datecol ..... etc for all tables.

    Result
    01/09 null null null
    02/09 02/09 null null
    03/09 03/09 03/09 null

    Oracle has some methods for obtaining first day of month, last day of month etc. This still may not do exactly what you want, if not, post exactly what you want and someone (or I) will attempt to help.

    Hth
    Bill

  6. #6
    Join Date
    Jul 2003
    Posts
    21
    From the 4 tables I have, I'm trying to get the following result

    2003-08-01 0 0 0 1
    2003-08-02 1 2 1 0
    2003-08-03 0 1 1 1
    .....

    and so on

  7. #7
    Join Date
    Nov 2002
    Location
    Desk, slightly south of keyboard
    Posts
    697
    Apologies again, I sort of went off one one there. I was trying to deal with missing dates when you hadn't asked for such :-)

    This should do what you want but saying that I just got home from the pub - there may be more efficient solutions :-)

    Amend table names/column names as appropriate...

    create table test1 (datecol date,totval number(5));
    create table test2 (datecol date,totval number(5));
    create table test3 (datecol date,totval number(5));
    create table test4 (datecol date,totval number(5));

    insert into test1 values (to_date('01/09/2003','DD/MM/YYYY'),1);
    insert into test1 values (to_date('02/09/2003','DD/MM/YYYY'),2);
    insert into test2 values (to_date('02/09/2003','DD/MM/YYYY'),1);
    insert into test2 values (to_date('03/09/2003','DD/MM/YYYY'),2);
    insert into test3 values (to_date('03/09/2003','DD/MM/YYYY'),1);
    insert into test3 values (to_date('04/09/2003','DD/MM/YYYY'),2);
    insert into test4 values (to_date('04/09/2003','DD/MM/YYYY'),1);
    insert into test4 values (to_date('05/09/2003','DD/MM/YYYY'),2);

    select datecol, sum( t1 ), sum( t2 ), sum( t3 ), sum( t4 )
    from (
    select datecol, totval as t1, 0 as t2, 0 as t3, 0 as t4 from test1
    union
    select datecol, 0 as t1, totval as t2, 0 as t3, 0 as t4 from test2
    union
    select datecol, 0 as t1, 0 as t2, totval as t3, 0 as t4 from test3
    union
    select datecol, 0 as t1, 0 as t2, 0 as t3, totval as t4 from test3
    )
    group by datecol

    Hth
    Bill

  8. #8
    Join Date
    Jul 2003
    Posts
    21

    Smile

    that was exactly what i needed
    thanks a lot !

Posting Permissions

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