SQL Statement whether Entry exists

Question

i want to select a 1 when a special entry exists in one of my tables, otherwise a 0.

I thought of doing it like this:

SELECT 1 FROM dual .....

In PLSQL it would be like this:

SELECT CASE 
        WHEN EXISTS (
                SELECT 1
                FROM * TABLE *
                WHERE * requirements *
                )
            THEN 1
        ELSE 0
        END
INTO * variable *
FROM dual;

But I have no idea how to implement something like this in normal SQL.

I just want to get a 1 if the entry exists and otherwise a 0 (null would be also okay I guess).

Not sure exactly what you mean, something like this? `select count(1) from table where key = value;` That would return 0 or 1 — Train, Mar 10 '16 at 19:55
I did mean something else but the idea with count() works with my specific implementation of the following steps. So thanks a lot for the idea :) — Thomas Koch, Mar 10 '16 at 20:00
Not sure what you mean with "normal SQL". But if you want to rewrite it for ANSI SQL then you merely need to remove the last two lines `INTO * variable * FROM dual;` and the select should work fine. — Ralph, Mar 10 '16 at 20:03
This might help? http://stackoverflow.com/questions/14189216/case-in-select-statement — Juan Carlos Alvarez, Mar 10 '16 at 22:35

score 1 · Answer 1 · answered Mar 11 '16 at 19:28

If I get you right, you can use the COUNT aggregate function.

My setup (maybe not the best way):

Oracle XE 11g (OpenSuse, VirtualBox), Oracle SQL Developer 4.1.3.20 (Windows 10)

SET SERVEROUTPUT ON
SET FEEDBACK OFF
CLEAR SCREEN;

CREATE TABLE test1(
  id NUMBER, 
  text VARCHAR2(255)
) ;

INSERT INTO test1 VALUES (1, 'abc');
INSERT INTO test1 VALUES (2, 'def');
INSERT INTO test1 VALUES (3, 'ghi') ;
COMMIT;

-- False (Result = 0)
SELECT DECODE(COUNT(*), 0, 0, 1) 
FROM TEST1 
WHERE id IS NULL 
AND TEXT = 'adadas';

-- True  (Result = 1)
SELECT DECODE(COUNT(*), 0, 0, 1) 
FROM TEST1 
WHERE ID =2;

-- Anonymous PL/SQL Block Test
DECLARE
  V_TEST_VAR INTEGER;
BEGIN
    -- FALSE Case:
    SELECT DECODE(COUNT(*), 0, 0, 1) 
    INTO V_TEST_VAR
    FROM TEST1 
    WHERE ID = 4711;

    CASE V_TEST_VAR
      WHEN 0 THEN
         DBMS_OUTPUT.PUT_LINE('FALSE');
      WHEN 1 THEN
         DBMS_OUTPUT.PUT_LINE('FALSE');
    END CASE;

    -- TRUE Case:
    SELECT DECODE(COUNT(*), 0, 0, 1) 
    INTO V_TEST_VAR
    FROM TEST1 
    WHERE ID = 1;

    CASE V_TEST_VAR
      WHEN 1 THEN
         DBMS_OUTPUT.PUT_LINE('TRUE');
      WHEN 0 THEN
         DBMS_OUTPUT.PUT_LINE('FALSE');
    END CASE;

END;
/
DROP TABLE test1;

SQL Statement whether Entry exists

1 Answers1