Group_Concat() when is null, I get error 1064

Question

I have the following query in MySQL 8.0:

set 
  @userid = 'XXX';
set 
  @lastdate = '2024-09-30';
SET 
  @sql = null;
SELECT 
  GROUP_CONCAT(
    DISTINCT CONCAT(
      'Count(CASE WHEN Checkedby = ''', 
      Checkedby, 
      ''' THEN Checkedby ELSE 0 END) AS ''', 
      CONCAT(
        IFNULL(FirstName, ''), 
        ' ', 
        IFNULL(LastName, '')
      ), 
      ''''
    )
  ) into @sql 
FROM 
  aspnetusers A 
  join userinfo U on A.Id = U.id 
  left join bookings B ON A.Id = B.CheckedBy 
where 
  (
    ParentId = @userid 
    OR A.Id = @userid
  ) 
  and year(Checkin)= year(@lastdate) 
  and B.UserID = @userid 
  and month(Checkin)= month(@lastdate);
SET 
  @sql = CONCAT (
    'SELECT  Y.*, R.* 
FROM 
(SELECT day(Checkin) as DayX, 
IFNULL(count(IFNULL(Checkin,0)),0) as Total, ', 
    @sql, '
FROM aspnetusers A join userinfo U on A.Id=U.id left join bookings B ON A.Id=B.CheckedBy 
where year(Checkin)= year(@lastdate) and B.UserID= @userid and month(Checkin)= month(@lastdate) 
group by day(Checkin) 
) R right JOIN 
 (select day(last_day(@lastdate) - INTERVAL (a.a + (10 * b.a) + (100 * c.a)) DAY) as Day
    from (select 0 as a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as a
    cross join (select 0 as a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as b
    cross join (select 0 as a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as c
   where year(last_day(@lastdate) - INTERVAL (a.a + (10 * b.a) + (100 * c.a)) DAY)=year(@lastdate) and month(last_day(@lastdate) - INTERVAL (a.a + (10 * b.a) + (100 * c.a)) DAY)=month(@lastdate)
    order by Day) Y on R.DayX = Y.Day 
    GROUP BY Y.Day, R.Total order by Y.Day'
  );
PREPARE stmt 
FROM 
  @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

the result is the following: result wo error

if I change the date, I get the following error:

11:29:17 PREPARE stmt FROM @sql Error Code: 1064. You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'NULL' at line 1 0.000 sec

It seems that when the first query becomes null, I get the error, but I don't know how to correct it.

If someone has a simpler method, it would be great.

PS. I will use this query in a chart and would be also nice to show all users and their values (if its null then 0). I wrote the above script in MySQL, but in the feature I think I will convert all the queries to MariaDB.

Thanks,

Steve

I tried IFNULL and COALESCE but don't seem to work.

EDIT:

WORKING: set @lastdate = '2024-09-30';

-- 0 @sql value
    Count(CASE WHEN Checkedby = '0bb63911-cc7b-4db0-bd33-b30edbc27a27' THEN Checkedby ELSE 0 END) AS 'Meggie1 Simpson',Count(CASE WHEN Checkedby = '97c73a4d-11b2-4492-a234-90b4cbff86c5' THEN Checkedby ELSE 0 END) AS 'MOLNAR ISTVAN'
    -- 1 after exec
    SELECT  Y.*, R.* 
    FROM 
    (SELECT day(Checkin) as DayX, 
    IFNULL(count(IFNULL(Checkin,0)),0) as Total, Count(CASE WHEN Checkedby = '0bb63911-cc7b-4db0-bd33-b30edbc27a27' THEN Checkedby ELSE 0 END) AS 'Meggie1 Simpson',Count(CASE WHEN Checkedby = '97c73a4d-11b2-4492-a234-90b4cbff86c5' THEN Checkedby ELSE 0 END) AS 'MOLNAR ISTVAN'
    FROM aspnetusers A join userinfo U on A.Id=U.id left join bookings B ON A.Id=B.CheckedBy 
    where year(Checkin)= year(@lastdate) and B.UserID= @userid and month(Checkin)= month(@lastdate) 
    group by day(Checkin) 
    ) R right JOIN 
     (select day(last_day(@lastdate) - INTERVAL (a.a + (10 * b.a) + (100 * c.a)) DAY) as Day
        from (select 0 as a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as a
        cross join (select 0 as a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as b
        cross join (select 0 as a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as c
       where year(last_day(@lastdate) - INTERVAL (a.a + (10 * b.a) + (100 * c.a)) DAY)=year(@lastdate) and month(last_day(@lastdate) - INTERVAL (a.a + (10 * b.a) + (100 * c.a)) DAY)=month(@lastdate)
        order by Day) Y on R.DayX = Y.Day 
        GROUP BY Y.Day, R.Total order by Y.Day
        -- 2 table name:
        GROUP_CONCAT(
        DISTINCT CONCAT(
          'Count(CASE WHEN Checkedby = ''', 
          Checkedby, 
          ''' THEN Checkedby ELSE 0 END) AS ''', 
          CONCAT(
            IFNULL(FirstName, ''), 
            ' ', 
            IFNULL(LastName, '')
          ), 
          ''''
        ): 
-- 3 result
Count(CASE WHEN Checkedby = '0bb63911-cc7b-4db0-bd33-b30edbc27a27' THEN Checkedby ELSE 0 END) AS 'Meggie1 Simpson',Count(CASE WHEN Checkedby = '97c73a4d-11b2-4492-a234-90b4cbff86c5' THEN Checkedby ELSE 0 END) AS 'MOLNAR ISTVAN'

NOT WORKING: set @lastdate = '2024-10-31';

-- 2 table name:
    GROUP_CONCAT(
        DISTINCT CONCAT(
          'Count(CASE WHEN Checkedby = ''', 
          Checkedby, 
          ''' THEN Checkedby ELSE 0 END) AS ''', 
          CONCAT(
            IFNULL(FirstName, ''), 
            ' ', 
            IFNULL(LastName, '')
          ), 
          ''''
        ): 
-- 3 result
Null

Edit:

set  @userid = '97c73a4d-11b2-4492-a234-90b4cbXXXX';
set @lastdate = '2024-09-30';//OK - 2024-10-31 //NOK
SET @sql=null;

SELECT 
  GROUP_CONCAT(
    DISTINCT CONCAT(
      'Count(CASE WHEN Checkedby = ''', 
      Checkedby, 
      ''' THEN Checkedby END) AS ''', 
      CONCAT(IFNULL(FirstName,''), ' ', IFNULL(LastName,'')), 
      ''''
    )
  ) into @sql
FROM aspnetusers A left join userinfo U on A.Id=U.id left join bookings B ON A.Id=B.CheckedBy
where  year(Checkin)= year(@lastdate) and month(Checkin)= month(@lastdate);

select @sql;

SET @sql = CONCAT ('SELECT  Y.*, R.* 
FROM 
(SELECT day(Checkin) as DayX, 
IFNULL(count(IFNULL(Checkin,0)),0) as Total, ',@sql,'
FROM aspnetusers A join userinfo U on A.Id=U.id left join bookings B ON A.Id=B.CheckedBy 
where year(Checkin)= year(@lastdate) and month(Checkin)= month(@lastdate) 
group by day(Checkin) 
) R right JOIN 
 (select day(last_day(@lastdate) - INTERVAL (a.a + (10 * b.a) + (100 * c.a)) DAY) as Day
    from (select 0 as a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as a
    cross join (select 0 as a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as b
    cross join (select 0 as a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as c
   where year(last_day(@lastdate) - INTERVAL (a.a + (10 * b.a) + (100 * c.a)) DAY)=year(@lastdate) and month(last_day(@lastdate) - INTERVAL (a.a + (10 * b.a) + (100 * c.a)) DAY)=month(@lastdate)
    order by Day) Y on R.DayX = Y.Day 
    GROUP BY Y.Day, R.Total order by Y.Day');

select @sql;
PREPARE stmt FROM @sql;
select @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

On "2024-09-30" - Working: result 1 img

Result 1:

Count(CASE WHEN Checkedby = '0bb63911-cc7b-4db0-bd33-b30edbc27a07' THEN Checkedby END) AS 'Meggie1 Simpson',Count(CASE WHEN Checkedby = '97c73a4d-11b2-4492-a234-90b4cbff36c5' THEN Checkedby END) AS 'MOLNAR ISTVAN'

Result2:

SELECT  Y.*, R.* 
FROM 
(SELECT day(Checkin) as DayX, 
IFNULL(count(IFNULL(Checkin,0)),0) as Total, Count(CASE WHEN Checkedby = '0bb63911-cc7b-4db0-bd33-b30edbc27a07' THEN Checkedby END) AS 'Meggie1 Simpson',Count(CASE WHEN Checkedby = '97c73a4d-11b2-4492-a234-90b4cbff36c5' THEN Checkedby END) AS 'MOLNAR ISTVAN'
FROM aspnetusers A join userinfo U on A.Id=U.id left join bookings B ON A.Id=B.CheckedBy 
where year(Checkin)= year(@lastdate) and month(Checkin)= month(@lastdate) 
group by day(Checkin) 
) R right JOIN 
 (select day(last_day(@lastdate) - INTERVAL (a.a + (10 * b.a) + (100 * c.a)) DAY) as Day
    from (select 0 as a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as a
    cross join (select 0 as a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as b
    cross join (select 0 as a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as c
   where year(last_day(@lastdate) - INTERVAL (a.a + (10 * b.a) + (100 * c.a)) DAY)=year(@lastdate) and month(last_day(@lastdate) - INTERVAL (a.a + (10 * b.a) + (100 * c.a)) DAY)=month(@lastdate)
    order by Day) Y on R.DayX = Y.Day 
    GROUP BY Y.Day, R.Total order by Y.Day

Result 3:

SELECT  Y.*, R.* 
FROM 
(SELECT day(Checkin) as DayX, 
IFNULL(count(IFNULL(Checkin,0)),0) as Total, Count(CASE WHEN Checkedby = '0bb63911-cc7b-4db0-bd33-b30edbc27a07' THEN Checkedby END) AS 'Meggie1 Simpson',Count(CASE WHEN Checkedby = '97c73a4d-11b2-4492-a234-90b4cbff36c5' THEN Checkedby END) AS 'MOLNAR ISTVAN'
FROM aspnetusers A join userinfo U on A.Id=U.id left join bookings B ON A.Id=B.CheckedBy 
where year(Checkin)= year(@lastdate) and month(Checkin)= month(@lastdate) 
group by day(Checkin) 
) R right JOIN 
 (select day(last_day(@lastdate) - INTERVAL (a.a + (10 * b.a) + (100 * c.a)) DAY) as Day
    from (select 0 as a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as a
    cross join (select 0 as a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as b
    cross join (select 0 as a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as c
   where year(last_day(@lastdate) - INTERVAL (a.a + (10 * b.a) + (100 * c.a)) DAY)=year(@lastdate) and month(last_day(@lastdate) - INTERVAL (a.a + (10 * b.a) + (100 * c.a)) DAY)=month(@lastdate)
    order by Day) Y on R.DayX = Y.Day 
    GROUP BY Y.Day, R.Total order by Y.Day

Result 4: The working table (image above)

On "2024-10-31" - Not Working:

Result 1: NULL result1 img

Result2: NULL result2 img

Provide the value of @sql variable when it causes an error. — Akina
– Akina, Commented Sep 25, 2024 at 9:37
@Akina I added code when the code is working and also from not working — TemNyilv
– TemNyilv, Commented Sep 25, 2024 at 10:17
?? None shown value is valid SQL code which can be processed by PREPARE.. — Akina
– Akina, Commented Sep 25, 2024 at 10:41
We need to see the final value of @sql after you concatenate it into the SELECT query. — Barmar
– Barmar, Commented Sep 25, 2024 at 15:34
@Barmar I attached results and images for working and not working query. — TemNyilv
– TemNyilv, Commented Sep 25, 2024 at 21:33

Collectives™ on Stack Overflow

Group_Concat() when is null, I get error 1064

Edit:

0

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

Edit:

0

Know someone who can answer? Share a link to this question via email, Twitter, or Facebook.

Your Answer

Sign up or log in

Post as a guest