Oracle SQL LOOP (between 2 dates) and Counting

Question

I'm stuck with a SQL Query.

I have this table:

[Reserve]

• ID NUMBER
• START_DATE, DATE
• END_DATE, DATE

.......... (more cols)

I need to look if the next count is more than 0 for any of the dates between START_DATE and END_DATE (including both). If the count is more than 0, the query must stop inmediately and return "There is no location" as a string.

SELECT Count(*)
  FROM disponibilidad
  JOIN estadia ON disponibilidad.identificador_estadia = estadia.identificador
 WHERE estadia.identificador = 1
   AND disponibilidad.numero_reservas > 500

Answer 1

If I understand you correctly, this should work:

SELECT 'There is no location'
FROM Reserve r
WHERE EXISTS (SELECT *
              FROM disponibilidad 
                     JOIN estadia 
                        ON disponibilidad.identificador_estadia = estadia.identificador 
              WHERE estadia.identificador = r.ID
                   AND disponibilidad.numero_reservas > 500 
                   AND disponibilidad.date BETWEEN r.StartDate AND r.EndDate)
AND r.ID = 1

Since you didn't specify the relation between the table and the tables involved in the query, I guessed. And I may have guessed wrong. Such is life. :)