Using a Function inside a Select ORACLE SQL

Question

I have this function:

CREATE FUNCTION ISROOMAVAILABLE(P_ID_ROOM INT, P_DATE DATE) RETURN BOOLEAN
    IS
    V_COUNT INT;
BEGIN
    SELECT count(*)
    INTO V_COUNT
    FROM CHECKIN A
             LEFT JOIN CHECKOUT B ON (A.ID_RESERVATION = B.ID_RESERVATION)
    WHERE A.ID_ROOM = P_ID_ROOM
      AND ((A.DATE >= trunc(P_DATE) AND B.DATE IS NULL)
        OR
           (A.DATE <= trunc(P_DATE) AND B.DATE >= trunc(P_DATE)));
    RETURN (V_COUNT > 0);
END;

This returns a true if a room (identified by it's ID) is available in a certain date.

And I want to check multiple parameters using the function above as a determinant in my choice:

    SELECT Q.ID, COUNT(R.DATE_OUT - R.DATE_IN) AS DAYS
    FROM QUARTO Q
             JOIN CHECKIN CI ON Q.ID = CI.ID_ROOM
             JOIN RESERVATION R ON CI.ID_RESERVATION = R.ID
    WHERE (EXTRACT(YEAR FROM R.DATE_IN) = EXTRACT(YEAR FROM SYSDATE))
      AND (ISROOMAVAILABLE(Q.ID, SYSDATE))
    GROUP BY Q.ID
    ORDER BY DAYS;

I am using sysdate in the function because I am still missing some data on the database but still the error I get is "Relational Operator Invalid".

Any way to use the function I have in the "WHERE" clause?

Please excuse my bad translations from portuguese, I don't know the exact terms in english.

@OldProgrammer - that is not exactly the same question. The thread you are pointing to is about a harder question, where the function requires a Boolean input. In this thread, the question is simpler - only the return data type is a problem, and that is easier to address. There are many other similar questions here on SO, I'll find one and close this thread. — , Dec 11 '20 at 18:49

GMB · Accepted Answer · 2020-12-11T18:48:47.553

I think Oracle cannot evaluate a PL/SQL boolean in the WHERE clause of a SQL query.

The simplest approach would be to modify the function, so it returns something that can be evaluated. We could, for example, return 1 for true and 0 for false:

CREATE FUNCTION ISROOMAVAILABLE(P_ID_ROOM INT, P_DATE DATE) RETURN INT
    IS
    V_COUNT INT;
BEGIN
    SELECT count(*)
    INTO V_COUNT
    FROM CHECKIN A
             LEFT JOIN CHECKOUT B ON (A.ID_RESERVATION = B.ID_RESERVATION)
    WHERE A.ID_ROOM = P_ID_ROOM
      AND ((A.DATE >= trunc(P_DATE) AND B.DATE IS NULL)
        OR
           (A.DATE <= trunc(P_DATE) AND B.DATE >= trunc(P_DATE)));
    RETURN LEAST(V_COUNT, 1);
END;

And then:

WHERE ...
  AND ISROOMAVAILABLE(Q.ID, SYSDATE) = 1

If you can't modifiy the function, then you would need to wrap it in another function, that returns a datatype that can be evaluated in SQL, and invoke the wrapper function instead the original.

Unrelated notes about your query:

EXTRACT(YEAR FROM R.DATE_IN) = EXTRACT(YEAR FROM SYSDATE) is suboptimal. This would be more efficiently expressed with direct filtering:

    R.DATE_IN >= TRUNC(SYSDATE, 'YEAR') 
AND R.DATE_IN < ADD_MONTHS(TRUNC(SYSDATE, 'YEAR'), 12)

I wonder whether COUNT(R.DATE_OUT - R.DATE_IN) does what you want. What it does is count all rows where both dates are not NULL. Maybe you meant: SUM(R.DATE_OUT - R.DATE_IN)?

Boolean is a proper data type; the issue is that Oracle SQL does not support it. The more common workaround (instead of returning a count as you suggest) is to return 0 for false, 1 for true (return data type NUMBER), or perhaps return strings like 'Y'/'N' or 'true'/'false' — , Dec 11 '20 at 18:47
@mathguy: that's what I meant... I adapted the wording. I also change the return value for 0/1 (although the count would do the job fine here, with an inequality condition in the `WHERE` clause). — GMB, Dec 11 '20 at 18:52
Thank you for the tip on the date. I solved the problem by changing the return of the function as you suggested. — , Dec 11 '20 at 21:01

Using a Function inside a Select ORACLE SQL

1 Answers1