MySQL replace values in SELECT CASE query-CodePudding

I have problem with CASE WHEN.

I wrote a query which works fine without CASE WHEN and it looks like:

SELECT r.data,
       r.data1,
       r.godzina,
       r.zmiana,
       r.nrpartii,
       r.nazwadzianiny,
       r.nazwamaszyny,
       r.rzad,
       r.nrmaszyny,
       r.starynrmaszyny,
       r.nruiglenia,
       r.wydzigla,
       r.waga,
       r.uwagipredkoscmaszyny,
       u1.user_nicename,
       u1.ID,
       u1.display_name 
    FROM jawe_wpdatatable_8 AS r
INNER JOIN jawe_users as u1 on u1.ID = r.uzytkownik

What I want to do is to change r.zmiana value to '1' or '2' or '3' depending on r.godzina

I'm trying something like this:

SELECT r.data,
       r.data1,
       r.godzina,
       CASE r.zmiana
        WHEN (r.godzina BETWEEN '08:00:00' AND '15:59:99') THEN r.zmiana = 1
        WHEN (r.godzina BETWEEN '16:00:00' AND '23:59:99') THEN r.zmiana = 2
        ELSE r.zmiana = 3
       END,
       r.nrpartii,
       r.nazwadzianiny,
       r.nazwamaszyny,
       r.rzad,
       r.nrmaszyny,
       r.starynrmaszyny,
       r.nruiglenia,
       r.wydzigla,
       r.waga,
       r.uwagipredkoscmaszyny,
       u1.user_nicename,
       u1.ID,
       u1.display_name 
    FROM jawe_wpdatatable_8 AS r
INNER JOIN jawe_users as u1 on u1.ID = r.uzytkownik

I frogot, the begining value for r.zmiana is NULL I was trying many variations but all I get (in best case) is new column with NULL values and name which is whole 'CASE.....END' code.

How to get this CASE ... ENG replace (for display puposes only) values from NULL to '1' or '2' or '3' in r.zmiana ?

Thanks in advance.

---EDITED ---

'Asgar' is the one with right answer.

Yes You are right Asgar . But that works partly (instead of '1' it give me '3' :) ). So I change BETWEEN to range and it looks like this and works fine now:

SELECT 
r.data, 
r.data1, 
r.godzina, 
CASE 
WHEN r.godzina >= '08:00:00' AND r.godzina <'16:00:00' THEN 1 
WHEN r.godzina >= '16:00:00' AND r.godzina <='23:59:99' THEN 2
WHEN r.godzina >= '00:00:00' AND r.godzina <'08:00:00' THEN 3
END, 
r.nrpartii, 
r.nazwadzianiny, 
r.nazwamaszyny, 
r.rzad, 
r.nrmaszyny, 
r.starynrmaszyny, 
r.nruiglenia, 
r.wydzigla, 
r.waga, 
r.uwagipredkoscmaszyny, 
u1.user_nicename, 
u1.ID, u1.display_name 
FROM jawe_wpdatatable_8 AS r 
INNER JOIN jawe_users as u1 on u1.ID = r.uzytkownik

I have only one question. Yes it works but the column name is stil whole CASE ... END code. how change it to 'zmiana' is there such possibility?

CodePudding user response：

Seems like your CASE WHEN syntax is wrong.

A simple illustration of CASE WHEN Query is:

SELECT
  CASE level
    WHEN 'A' THEN 'Beginner'
    WHEN 'B' THEN 'Junior'
    WHEN 'C' THEN 'Senior'
    ELSE 'Level doesn`t exist!'
  END AS experience_levels
FROM company;

So your query needs to be somthing like:

SELECT r.data,
       r.data1,
       r.godzina,
       CASE 
        WHEN r.godzina BETWEEN '08:00:00' AND '15:59:99' THEN  1
        WHEN r.godzina BETWEEN '16:00:00' AND '23:59:99' THEN 2
        ELSE 3
       END as zmiana,
       r.nrpartii,
       r.nazwadzianiny,
       r.nazwamaszyny,
       r.rzad,
       r.nrmaszyny,
       r.starynrmaszyny,
       r.nruiglenia,
       r.wydzigla,
       r.waga,
       r.uwagipredkoscmaszyny,
       u1.user_nicename,
       u1.ID,
       u1.display_name 
    FROM jawe_wpdatatable_8 AS r
INNER JOIN jawe_users as u1 on u1.ID = r.uzytkownik