Always have SQL query return atleast one record-CodePudding

SELECT column_a,
       CASE WHEN column_a > 10 THEN 0 ELSE 1 END AS column_b
FROM   table_a;

This does not always returns record, my requirement is to always have at least 1 row returned.

Following a solution here I tried couple of workaround such as union but none of it works.

SELECT 2 as column_a,
       CASE WHEN column_a > 10 THEN 0 ELSE 1 END AS column_b
FROM   table_a
UNION
SELECT -1,-1 FROM table_a;

Is there a way I can make this query return a record even when none is found?

CodePudding user response：

Well, query won't return anything if TABLE_A is empty (as there's no WHERE clause). In any other case, it'll return something.

Though, perhaps you meant to union it with a "dummy" select statement that selects from DUAL, not table_a (see line #6). Line #4 is here just to make the first select return no rows.

SQL> WITH table_a (column_a) AS (SELECT 3 FROM DUAL)
  2  SELECT 2 AS column_a, CASE WHEN column_a > 10 THEN 0 ELSE 1 END AS column_b
  3    FROM table_a
  4   WHERE 1 = 2
  5  UNION
  6  SELECT -1, -1 FROM DUAL;         --> DUAL here, not TABLE_A

  COLUMN_A   COLUMN_B
---------- ----------
        -1         -1

SQL>

CodePudding user response：

What is the problem you're trying to solve? Your title says "PL/SQL" which implies that this is happening in a PL/SQL block. In which case the standard answer would be just to catch the no_data_found exception and populate whatever variables you're fetching the data into with some appropriate default values.

BEGIN
    SELECT column_a, 
           CASE WHEN column_a > 10 THEN 0 ELSE 1 END AS column_b 
      INTO local_variable1, local_variable2
      FROM table_a;
EXCEPTION
  WHEN no_data_found
  THEN
    local_variable1 := 1;
    local_variable2 := -1;
END;

Likely, it would make sense to encapsulate this in a stored function.

If you really want to ensure that the query returns 1 row if there is no data in table_a, you could do something like this

SELECT column_a, 
       CASE WHEN column_a > 10 THEN 0 ELSE 1 END AS column_b 
  FROM table_a
UNION ALL
SELECT 1, -1
  FROM dual
 WHERE NOT EXISTS( SELECT 1 FROM table_a );

CodePudding user response：

You can use:

SELECT *
FROM   (
  SELECT column_a,
         CASE WHEN column_a > 10 THEN 0 ELSE 1 END AS column_b
  FROM   table_a
  UNION ALL              -- Use UNION ALL to keep duplicates from table_a.
  SELECT -1,-1 FROM DUAL -- Use the DUAL table which always has exactly 1 row.
)
WHERE  column_b >= 0     -- All the rows from table_a, if any exist.
OR     ROWNUM = 1        -- Or, just the first row.
;

Which, for the empty table:

CREATE TABLE table_a (column_a INT);

Outputs:

COLUMN_A COLUMN_B

-1 -1

COLUMN_A	COLUMN_B
-1	-1

And if you insert data:

INSERT INTO table_a (column_a)
SELECT 1 FROM DUAL UNION ALL
SELECT 2 FROM DUAL UNION ALL
SELECT 6 FROM DUAL UNION ALL
SELECT 10 FROM DUAL UNION ALL
SELECT 11 FROM DUAL UNION ALL
SELECT 11 FROM DUAL UNION ALL
SELECT 15 FROM DUAL;

Then the query outputs:

COLUMN_A COLUMN_B

1 1

2 1

6 1

10 1

11 0

11 0

15 0

COLUMN_A	COLUMN_B
1	1
2	1
6	1
10	1
11	0
11	0
15	0

db<>fiddle here