Results 1 to 4 of 4
  1. #1
    Join Date
    Dec 2003
    Location
    Oklahoma, USA
    Posts
    354

    Unanswered: Trying to write a query...

    I seem to be having a brain-cramp. I'm pretty sure I've written something like this before but just can't remember how to do it...

    Creating a query for data that will match hourly data in the correct spots, and show nulls where no corresponding data exists.

    So, I would like my output to look like this:

    Code:
    HOUR    ITEM
    =====   ==============
    5          
    6       Meeting
    7
    8       Routine visit
    So, in the above example, my table would only have two entries in it, one for "Meeting" at hour 6, and one for "Routine visit" at hour 8.

    Hope this makes sense...
    JoeB
    save disk space, use smaller fonts

  2. #2
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    This is output. What is input?

  3. #3
    Join Date
    May 2004
    Location
    Dominican Republic
    Posts
    721
    Left join.
    Code:
    SQL> with hours
      2       as ( select level hr
      3              from dual
      4           connect by level <= 8 ),
      5       data
      6       as ( select 6 hr,
      7                   'Meeting' item
      8              from dual
      9            union all
     10            select 8 hr,
     11                   'Routine visit' item
     12              from dual )
     13  select t1.hr, t2.item
     14    from hours t1
     15    left outer join data t2
     16      on t1.hr = t2.hr
     17   where t1.hr >= 5
     18   order by t1.hr
     19  /
    
            HR ITEM
    ---------- -------------
             5
             6 Meeting
             7
             8 Routine visit
    
    SQL>

  4. #4
    Join Date
    Dec 2003
    Location
    Oklahoma, USA
    Posts
    354
    Thank you JMartinez! That's what I was looking for... I've never used the "WITH" notation, but great solution.

    I think the way I used it before was:

    Code:
    SELECT   hours.hr, data.item
        FROM (SELECT     LEVEL hr
                    FROM DUAL
              CONNECT BY LEVEL <= 8) hours,
             (SELECT 6 hr, 'Meeting' item FROM dual
               UNION ALL
              SELECT 8 hr, 'Routine visit' item FROM dual) data
       WHERE hours.hr = data.hr(+) 
         AND hours.hr >= 5
    ORDER BY hours.hr;
    Thanks again!
    JoeB
    save disk space, use smaller fonts

Posting Permissions

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