Question 275700 in Mysql Question posted in
The official MySQL documentation can be found here.

set a header row value even when no records/result in mysql query (both CASE and WHERE clausules)

I’ve been searching and couldn’t find any post like the problem I have:

I need to set an specific value in a "header", but since there’s no record, I can’t make it possible.

create table Det_TMZF_Authorizations 
(id int, id_Det_TMZF int, authorization_format varchar(1));

Insert into Det_TMZF_Authorizations (id, id_Det_TMZF, authorization_format) values (1,1,'E');
Insert into Det_TMZF_Authorizations (id, id_Det_TMZF, authorization_format) values (2,2,'M');

SELECT 
     CASE   dda.Authorization_Format
       WHEN COALESCE(sum(dda.Authorization_Format), '0') THEN 'N'   
       ELSE 'Y'
     END                        declare_certificate,
    -- 'Y'                          declare_certificate,
        dda.Authorization_Format    authorization_format
FROM    Det_TMZF_Authorizations dda
WHERE   dda.id_Det_TMZF = 3

(Edit: tables with data and expected)

If I ask for id_Det_TMZF = 1 or 2, I have no problem setting "declare_certificate" = ‘Y’.

declare_certificate id_Det_TMZF authorization_format
Y 1 E
Y 2 M

but I need to set declare_certificate = ‘N’ when there’s no record.
I already tried "IFNULL(dda.Authorization_Format), ‘0’) THEN ‘N’" but got more errors than results.

I want this result:

declare_certificate id_Det_TMZF authorization_format
N NULL NULL
Tags:

2

Answers


  1. 0

    You can use an UNION operation plus an aggregation with a little trick to solve this problem:

    SELECT 
     case when max(a.authorization_format) = '0' then 'N' else 'Y' end declare_certificate
from (
  SELECT dda.authorization_format
  FROM    Det_TMZF_Authorizations dda
  WHERE   dda.id_Det_TMZF = 3
  UNION all
  SELECT '0'
) a;

    The trick is to rely on the ASCII table ordering. Since [0-9] are always smaller in the ASCII table the max operation will always get the rows with an ALPHA char on it. The query will always returns two rows one with the result of the original query and one from the Union operation, then we apply the aggregation which will take advantage of the ASCII ordering. If the original query has no rows on it, it will process the row from the UNION.

    See it working in here: https://www.db-fiddle.com/f/teQhRGvnSh4vc1jAevRT8v/1

    Login or Signup to reply.
  2. 0

    A LEFT JOIN to a single (dummy) row will ensure the result set to have at least one row.

    select 
  case when dda.id is not null
    then 'Y'
    else 'N'
  end as declare_certificate,
  dda.id_Det_TMZF, 
  dda.authorization_format
from (select 1) as dummy
left join Det_TMZF_Authorizations dda 
  on dda.id_Det_TMZF = 3

    Your conditions must be in the ON clause in this case. If there are no rows matching the conditions, all rows from the right table (including primary key) will be NULL. Check the PK for NULL and you will know if the result has any rows at all.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search