Results 1 to 2 of 2
  1. #1
    Join Date
    Oct 2004
    Posts
    25

    Question Unanswered: Record with NULL values

    Hi, people

    I have a table with a list of hotels, and another table with the reservations.
    I want to make a simple statistic with the total number of nights for each hotel.

    This query works fine:
    Code:
    SELECT DISTINCT b.name, b.city, SUM(a.nights) AS nights 
    FROM reservation A, hotels B 
    WHERE a.id_hotel(+) = b.id_hotel
    GROUP BY b.name, b.city ORDER by b.name
    but, if I add this clause -->
    Code:
    AND TRUNC(a.dateBegin) <= NVL(TRUNC(TO_DATE('" + Session["src_dateBegin"] + "','dd/mm/yyyy')),TRUNC(TO_DATE('31/12/2999','dd/mm/yyyy')))
    AND TRUNC(a.dateEnd) >= NVL(TRUNC(TO_DATE('" + Session["src_dateEnd"] + "','dd/mm/yyyy')),TRUNC(TO_DATE('01/01/1900','dd/mm/yyyy')))"
    it doesn't extract anymore the hotels without any reservation (nights = 0).

    How should I write my query to get a full list with all the hotels, even though I have a clause on the date?

    Thank you

  2. #2
    Join Date
    Oct 2004
    Posts
    25

    Arrow

    I just found out that it's enough to use
    "a.dateBegin(+)" and "a.dateEnd(+)" to get the null values, too...

    Thanks anyway!

Posting Permissions

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