Oracle SQL How to execute the second condition only if the first one has not been accepted?-CodePudding

I hope someone can help me. The case:

I have a table COMPANY_ACCESS with these columns:

ID NUMBER,
COMPANY_ID NUMBER,
AREA_ID NUMBER,
TEAM_ID NUMBER,
CAN_ACCESS BOOLEAN

And I have my query (doesn't work):

SELECT * 
FROM COMPANY_ACCESS ca
WHERE 
    ca.CAN_ACCESS = true
    AND ((ca.COMPANY_ID = ?1 AND ca.AREA_ID = ?2 AND ca.TEAM_ID = ?3)
         OR (ca.COMPANY_ID = ?1 AND ca.AREA_ID = ?2 AND ca.TEAM_ID IS NULL)
         OR (ca.COMPANY_ID = ?1 AND ca.AREA_ID IS NULL AND ca.TEAM_ID IS NULL))

What is my intention here. Search from the more specific access (with all parameter) to the less specific. If I have one register in the first condition :

(COMPANY_ID = ?1 AND AREA_ID = ?2 AND TEAM_ID = ?3)

I don't need check the others and return only this register. If I don't have. I will check without the TEAM_ID (an access without the TEAM_ID is valid for all teams inside the AREA_ID). But if I execute this query I received registers in the first condition and in the second condition and in the third condition. And isn't my intention. If I found one in the first condition (more specific) I don't need the others return only this one register. I will only check the second and the third condition if I don't find anything in the condition before.

Please can someone help me?

CodePudding user response：

From Oracle 12, You can filter the rows using your bind variables and then order the result set so that your preferred rows are first and then filter out the other rows using FETCH FIRST ROW WITH TIES:

SELECT * 
FROM   COMPANY_ACCESS ca
WHERE  ca.CAN_ACCESS = true
AND    ca.COMPANY_ID = ?
AND    (   (ca.AREA_ID = ? AND (ca.TEAM_ID = ? OR ca.TEAM_ID IS NULL))
        OR (ca.AREA_ID IS NULL AND ca.TEAM_ID IS NULL))
ORDER BY
       ca.AREA_ID NULLS LAST,
       ca.TEAM_ID NULLS LAST
FETCH FIRST ROW WITH TIES;

In earlier versions, you can use analytic functions to do the filtering:

SELECT *
FROM   (
  SELECT ca.*,
         RANK() OVER (
           ORDER BY ca.AREA_ID NULLS LAST, ca.TEAM_ID NULLS LAST
         ) AS rnk
  FROM   COMPANY_ACCESS ca
  WHERE  ca.CAN_ACCESS = true
  AND    ca.COMPANY_ID = ?
  AND    (   (ca.AREA_ID = ? AND (ca.TEAM_ID = ? OR ca.TEAM_ID IS NULL))
          OR (ca.AREA_ID IS NULL AND ca.TEAM_ID IS NULL))
)
WHERE  rnk = 1;

If there will only ever be a single matching row then you could use ROWNUM to do the filtering instead:

SELECT *
FROM   (
  SELECT *
  FROM   COMPANY_ACCESS ca
  WHERE  ca.CAN_ACCESS = true
  AND    ca.COMPANY_ID = ?
  AND    (   (ca.AREA_ID = ? AND (ca.TEAM_ID = ? OR ca.TEAM_ID IS NULL))
          OR (ca.AREA_ID IS NULL AND ca.TEAM_ID IS NULL))
  ORDER BY
         ca.AREA_ID NULLS LAST,
         ca.TEAM_ID NULLS LAST
)
WHERE  ROWNUM = 1;