Show null or default value when bringing a query without results

Question

Show null or default value when bringing a query without results

Navigation

#1 by (1 votes)
#2 by (0 votes)

0

I would like to be able to bring a default value after executing a query in case it does not bring any rows. I have tried a bit of everything, COALESCE, NULLIF, NOT EXISTS, EXISTS, etc.

I do not know if someone has already tried it before to see if you could do me the favor of helping me with this.

SQL:

SELECT  CASE 
        WHEN  EXISTS(SELECT tabla1.tbl1_campo1 
                    FROM tabla1
                    WHERE tabla1.campo2 = 'EXAMPLE' 
                    AND tabla1.campo1 = tabla2.campo1_tbl1 )  THEN '1'::CHAR(1)
        ELSE '0'::CHAR(1)
    END
    FROM    tabla2, tabla3
    WHERE   tabla2.campo1 = 138239
    AND     tabla2.campo1_tbl3 = tabla3.campo1
    AND     (split_part(tabla3.campo2, '-', 1))::CHAR(2) IN ('16', '17', '18')

If I execute it as it is, it results in zero rows and I would like it to show at least one row / field, (defined by me), in order to make a comparison:

I thank you in advance for all the help you can give me with the matter, since I have been trying for several days to find an answer to this problem and I have not been able to find it yet.

sql postgresql

asked by Manuel Caicedo 01.06.2017 в 17:49

source

2 answers

Error bd Oracle and C # [closed] dynamically fill action sheet Swift 3

score 1 · Answer 1

It could be done with UNION ALL . Only, in the second SELECT there must be as many columns as in the first, otherwise it will not work.

EDIT

Now it should work ...

Watch DEMO on REXTESTER

CREATE TABLE TEST_NULL (
   ID_TEST     SERIAL PRIMARY KEY     NOT NULL,
   NOMBRE      CHAR (50)              NOT NULL
);

INSERT INTO TEST_NULL (NOMBRE) VALUES ('Test 1'), ('Test 2');

-- Prueba 1

SELECT ID_TEST, 
       NOMBRE, 
       1 AS TMP_ID 
FROM TEST_NULL 
WHERE ID_TEST>=0
UNION ALL 
SELECT NULL,
       NULL,
       1
WHERE NOT EXISTS (SELECT 1 FROM TEST_NULL WHERE ID_TEST >= 0);


-- Prueba 2

SELECT ID_TEST, 
       NOMBRE, 
       1 AS TMP_ID 
FROM TEST_NULL 
WHERE ID_TEST = 1
UNION ALL 
SELECT NULL,
       NULL,
       1
WHERE NOT EXISTS (SELECT 1 FROM TEST_NULL WHERE ID_TEST = 1);

-- Prueba 3

SELECT ID_TEST, 
       NOMBRE, 
       1 AS TMP_ID 
FROM TEST_NULL 
WHERE ID_TEST = 3
UNION ALL 
SELECT NULL,
       NULL,
       1
WHERE NOT EXISTS (SELECT 1 FROM TEST_NULL WHERE ID_TEST = 3);

Results

Test 1

    id_test       nombre    tmp_id
1   1             Test 1    1
2   2             Test 2    1

Test 2

    id_test       nombre    tmp_id
1   1             Test 1    1

Test 3

    id_test       nombre    tmp_id
1   NULL          NULL      1

score 0 · Answer 2

You can use the WITH statements.

-- ajustar los nombres de campos, tablas y condiciones a tus necesidades
with cte_datos as ( -- Consulta principal
  select id, valor from mitabla
  where cond = 999999
)

select id, valor from cte_datos  -- Obtiene los datos de la consulta principal
union all
select 0, 'N/A' -- Obtiene los valores por defecto
  where not exists (select * from cte_datos) -- siempre que no haya registros en la consulta principal