How to display a message when all columns values are null

Question

SELECT  NVL(T1.C1, 'NO RECORDS'), T1.C2, T2.C3, T1.C4  

FROM TEMP T1, TEMP_1 T2

WHERE
  T1.C2 = T2.C2
  AND T1 IN ( 'T001','T002')

  ;

I'm getting all null values for the columns, what can I do to display a message, I tried using Coalesce it did not work properly. NVL function does not return the desired result as well. Need some help

score 1 · Answer 1 · answered Jan 26 '15 at 17:14

We don't know the database you use but something like the following should work for you :

SELECT COALESCE(T1.C1,T1.C2,T2.C3,T1.C4 'NO RECORDS')
FROM TEMP T1, TEMP_1 T2
WHERE T1.C2 = T2.C2 AND T1 IN ( 'T001','T002')

The coalesce get a variable numbers of arguments and returns the first non-null :

COALESCE( expr1, expr2, ... expr_n )

so in you case if you get 'NO RECORDS' mans that all the 4 field are null ...

Allan · Answer 2 · 2015-01-26T18:58:35.310

I suspect that the problem is that you have no rows. When you return a row with a null column, coalesce or nvl will do exactly what you expect. If you need to return a value when when the result set is empty, you need to use a bit more trickery. You can do that like this:

SELECT NVL (t1.c1, 'NO RECORDS'),
       t1.c2,
       t2.c3,
       t1.c4
FROM   temp t1, temp_1 t2
WHERE  t1.c2 = t2.c2 AND t1 IN ('T001', 'T002')
UNION ALL
SELECT 'NO_RECORDS',
       NULL,
       NULL,
       NULL
FROM   DUAL
WHERE  NOT EXISTS
          (SELECT *
           FROM   temp t1, temp_1 t2
           WHERE  t1.c2 = t2.c2 AND t1 IN ('T001', 'T002'));

However, if this is intended to be interpreted by code somewhere, you'd be better off determining that no rows were returned via the available metadata rather than from a magic value.

If c1 is a number or date, then you'll need to use either CAST or TO_CHAR to convert it to a varchar2 in order to make this work. Queries merged via UNION must have the same data type for each column.

yes, u r right, I've no rows and that's the expected result of my query. However; I've to display "No Recods Found" in this case. I tried using your trick but it throws error message,"expression must have same datatype as corresponding expression" — aajmee, Jan 26 '15 at 18:38

score 0 · Answer 3 · answered Jan 26 '15 at 17:22

Maybe try using CASE ?

SELECT CASE 
        WHEN T1.C1 IS NULL
            THEN 'NO RECORDS'
        ELSE T1.C1
        END,
    T1.C2,
    T2.C3,
    T1.C4
FROM TEMP T1
INNER JOIN TEMP_1 T2
    ON T1.C2 = T2.C2
WHERE T1 IN (
        'T001',
        'T002'
        );

Also, not sure if I understand this statement T1 IN ( 'T001','T002')

score 0 · Answer 4 · answered Jan 26 '15 at 18:44

I assume because of your use of the NVL() function that you're using Oracle. I also noticed from your comments that you are not trying to find rows where all columns are NULL but rather find records in one table for which there are no corresponding records in another table. That can be done in a couple of ways, one, using NOT EXISTS:

SELECT t1.c1
  FROM t1
 WHERE t1.c1 IN ('T001','T002')
   AND NOT EXISTS ( SELECT 1 FROM t2
                     WHERE t2.c2 = t1.c1 );

or you could use an outer join:

-- ANSI syntax
SELECT t1.c1
  FROM t1 LEFT JOIN t2
    ON t1.c1 = t2.c2
 WHERE t1.c1 IN ('T001','T002')
   AND t2.c2 IS NULL;

-- Old Oracle syntax
SELECT t1.c1
  FROM t1, t2
 WHERE t1.c1 IN ('T001','T002')
   AND t1.c1 = t2.c2(+)
   AND t2.c2 IS NULL;

How to display a message when all columns values are null

4 Answers4