Oracle SQL Return True If Exists Question

Oracle sql return true if exists question

There is no Boolean type in Oracle SQL. You will need to return a 1 or 0, or some such and act accordingly:

SELECT CASE WHEN MAX(user_id) IS NULL THEN 'NO' ELSE 'YES' END User_exists
  FROM user_id_table
 WHERE user_id = 'some_user';

Returning true or false in a SQL function

You can use the COUNT aggregate function to check the existence of the record and return the true or false as follows:

CREATE OR REPLACE FUNCTION FUN_TIENE_CITA (
    P_ID_PACIENTE NUMBER,
    P_FECHA_CITA DATE
) RETURN VARCHAR2 IS
    P_RESULT   VARCHAR2(5);
BEGIN
    SELECT
        CASE
            WHEN COUNT(1) > 0 THEN 'true'
            ELSE 'false'
        END
    INTO P_RESULT
    FROM CITAS
   WHERE ID_PACIENTE = P_ID_PACIENTE 
     AND FECHA_CITA = P_FECHA_CITA ;

RETURN P_RESULT;

END FUN_TIENE_CITA;
/

Cheers!!

How to return boolean if record exists for multiple tables

You should phrase this as exists:

select (case when exists (select 1 from table1 where componentid = 'GetAccountBalance')
             then 'YES' else 'NO'
        end) as flagTable1,
       (case when exists (select 1 from table2 where componentid = 'GetAccountBalance')
             then 'YES' else 'NO'
        end) as flagTable2,
       (case when exists (select 1 from table3 where componentid = 'GetAccountBalance')
             then 'YES' else 'NO'
        end) as flagTable3
from dual;

The overhead of doing joins is simply unnecessary. The above should also make optimal use of indexes on the tables.

EDIT:

For multiple components, you can use correlated subqueries:

select (case when exists (select 1 from table1 t1 where t1.componentid = c.componentid)
             then 'YES' else 'NO'
        end) as flagTable1,
       (case when exists (select 1 from table2 t2 where t2.componentid = c.componentid)
             then 'YES' else 'NO'
        end) as flagTable2,
       (case when exists (select 1 from table3 t3.where t3.componentid = c.componentid)
             then 'YES' else 'NO'
        end) as flagTable3
from (select 'GetAccountBalance' as componentid from dual union all
      select 'GetCreditBalance' from dual
     ) c

Oracle SQL query to return 1 or 0 based on if value exists

A simple LEFT JOIN will do:

SELECT  Q.*, 
        CASE WHEN A.Question_ID IS NULL THEN 0 ELSE 1 END Answered
FROM Question Q
LEFT JOIN ( SELECT DISTINCT Question_ID
            FROM Answer) A
    ON Q.ID = A.Question_ID;

SQL: return true/false if a related record is presented

You are looking for the exists statement:

select 
    id,
    case when exists (
        select 1 from assignment_reviews where assignment_reviews.assignment_id = assignments.id
    ) then 1 else 0 end as hasReview
from
    assignments

SQL: Return true if list of records exists?

Given your updated question, these are the simplest forms:

If ProductID is unique you want

SELECT COUNT(*) FROM Products WHERE ProductID IN (1, 10, 100)

and then check that result against 3, the number of products you're querying (this last part can be done in SQL, but it may be easier to do it in C# unless you're doing even more in SQL).

If ProductID is not unique it is

SELECT COUNT(DISTINCT ProductID) FROM Products WHERE ProductID IN (1, 10, 100)

---

When the question was thought to require returning rows when all ProductIds are present and none otherwise:

SELECT ProductId FROM Products WHERE ProductID IN (1, 10, 100) AND ((SELECT COUNT(*) FROM Products WHERE ProductID IN (1, 10, 100))=3)

or

SELECT ProductId FROM Products WHERE ProductID IN (1, 10, 100) AND ((SELECT COUNT(DISTINCT ProductID) FROM Products WHERE ProductID IN (1, 10, 100))=3)

if you actually intend to do something with the results. Otherwise the simple SELECT 1 WHERE (SELECT ...)=3 will do as other answers have stated or implied.

---

Related Topics