How to check query with empty return?

Asked

Viewed 467 times

0

I have a query with many columns and when I return empty I want it to show 1. However, I cannot do the check due to the many columns and the GROUP BY.

A simple example follows below : 

SELECT ISNULL(
    (SELECT SUM(amount), p.staff_id from payment p
INNER JOIN staff s on s.staff_id=p.staff_id
GROUP BY p.staff_id),1)

However I get the following error :

Only one Expression can be specified in the select list when the subquery is not introduced with EXISTS.

How can I check if a query is null and assign a value to it when it has multiple columns?

sql

  • IS NULL is used after the search, it would be something like this, SELECT * FROM table Where NAME IS NULL, the way it was used may be causing this error

  • can be used this way but do not think accept SUM or in case I only used isnull up to hj with a field in subselect

  • IFNULL(...), NOT ISNULL(...), or IF(campo != null, true, false) as valor

  • SUM will never return null, is a sum, you can do SUM(campo) > 0

2 answers

0

Look I read the documentation and really the ISNULL hopes that the subquery has only one field in select.

in case it would work thus

SELECT ISNULL(
    (SELECT p.staff_id from payment p
INNER JOIN staff s on s.staff_id=p.staff_id
GROUP BY p.staff_id),1)

0

How can I check if a query is null and assign a value to when it has multiple columns?

You can check multiple columns using the CASE WHEN as an example below.

Let us consider that the outworking of the consultation involving the group by be it:

product value stockpile
Tyre 150.25 10
AIR FILTER NULL 5
Drums NULL NULL

According to a test performed on SQL Fiddle, when executing the query based on the above result, we would have:

select *, 
       case when (valor is NULL) or (Produto is NULL) or (estoque is NULL) then 1 else 0 end as Algum_campo_eh_nulo,
       case when (valor is NULL) and (estoque is NULL)  then 1 else 0 end as Valor_e_Estoque_sao_nulos
  from 
        (
            Select 'Bateria' as produto, NULL as valor,  NULL as estoque
            union
            Select 'Pneu' as produto, 150.25 as valor, 10 as estoque
            union
            Select  'Filtro de AR' as produto, NULL as valor, 5 as estoque
        ) as x

The consultation is checking:

  1. If any of the fields is null by means of CASE WHEN together with the OR generating the column algum_campo_eh_nulo;
  2. If valor And estoque are void by means of CASE WHEN together with the AND generating the column Valor_e_Estoque_sao_nulos;

The Result:

product value stockpile somethings value_estoque_sao_nulos
Tyre 150.25 10 0 0
AIR FILTER NULL 5 1 0
Drums NULL NULL 1 1

Browser other questions tagged sql

You are not signed in. Login or sign up in order to post.