Replace Mysql null field with a custom string

Question

I am working on an application build on Laravel on the back in with Mysql. I have the below Mysql query doest quite work as expected yet. I am not a Mysql expert but basically what I am trying to do is for every AptStatus = 3 I have a Null field for Operatory. Id like to convert that Null field to the Operatory name for each Operatory in the location. (hopefully that makes sense).

I am storing this in php as a variable to store my list of locations.

$locs = (below query in Eloquent)

Locations query:

select
    op.Location, op.Operatory 
from rpt_officeschedules as op 
left join apptwidget_locations al on op.Location = al.location
where 
    (op.Operatory LIKE '%NP%' or op.Operatory LIKE '%OPEN%') and
    al.isOfficeClosed = 0
group by Location, Operatory;

Query Results:

Location | Operatory | 
---------  ----------
Addison.    2 NP
Addison.    3 NP/TX

MySql Query:

select
    Location,
    AptDate,
    IF(AptStatus = 3, '08:00:00', AptTime) AptTime,
    IF(AptStatus = 3, 540, AptLength) AptLength,
    AptStatus,
    OperatoryNum,
    Operatory
from rpt_officeschedules
where
    AptTime between '07:59:00' and '17:30:00' and 
    AptDate between '2020-11-07' and '2020-11-08' and 
    (
        Operatory LIKE '%NP%' or 
        Operatory LIKE '%OPEN%' or 
        Operatory is null
    )
order by Location, AptDate, OperatoryNum, AptTime, Operatory;

Query results

Location | AptDate   | AptTime | AptLength | Operatory | AptStatus | OperatoryNum |
--------- ---------   --------- ----------- ----------- ----------- --------------
Addison.  2020-11-07   08:00:00.  540        null            3           0
Addison   2020-11-07   08:00:00.  540        null            3           0
Addison.  2020-11-08   08:00:00.  540        null            3           0
Addison   2020-11-08   08:00:00.  540        null            3           0

What id like to do is when a location shows an AptStatus = 3, to convert that null field for each Operatory name ($locs)

Expected results:

Location | AptDate   | AptTime | AptLength | Operatory | AptStatus | OperatoryNum |
--------- ---------   --------- ----------- ----------- ----------- --------------
Addison.  2020-11-07   08:00:00.  540        2 NP            3           0
Addison   2020-11-07   08:00:00.  540        3 NP/TX         3           0
Addison.  2020-11-08   08:00:00.  540        2 NP            3           0
Addison   2020-11-08   08:00:00.  540        3 NP/TX         3           0

Any Ideas?

Use `COALESCE(column, 'literal')` instead of `column` (or expresion) in output list. If column is NULL then `'literal'` will be returned. — Akina, Nov 05 '20 at 10:22
could you attach the table's structure to understand the relations between these tables? — V-K, Nov 05 '20 at 10:33
I think using [case when](https://stackoverflow.com/questions/9588015/how-do-i-use-properly-case-when-in-mysql) statement may help you for this approach — Tohid Dadashnezhad, Nov 05 '20 at 10:35
@V-K that is the issue, there is no relation in tables. This is a pretty dirty way to reuse the same table do the same thing. In other words, there is only ONE table. — , Nov 05 '20 at 10:35
Do you wanna get `Operatory` value from the same table (if status = 3 and operatory is not null) and apply it to other rows with status=3, where operatory is null? Or what's the task? — V-K, Nov 05 '20 at 10:39
@V-K The Field AptStatus represents an 'office close' with a null operatory. I am trying to convert that null Operatory value when status is 3 to the Operatory name. — , Nov 05 '20 at 10:40
@V-K I am having to create my Operatories list first in a $locs variable (from the same table). — , Nov 05 '20 at 10:42
i realy don't get the difference of Operatory between 2 NP and 3 NP/TX how do we determine which line should be 2 NP or other? — Santa's helper, Nov 05 '20 at 11:16
@Santa'shelper the order doesn’t matter, just as long as the string gets replace — , Nov 05 '20 at 20:50

Alexander Burov · Answer 1 · 2020-11-06T00:07:17.210

Something like this if I've got your inputs correctly:

select
    os.Location,
    os.AptDate,
    IF(os.AptStatus = 3, '08:00:00', os.AptTime) AptTime,
    IF(os.AptStatus = 3, 540, os.AptLength) AptLength,
    os.AptStatus,
    os.OperatoryNum,
    COALESCE(os.Operatory, op.Operatory)
from rpt_officeschedules os
left join apptwidget_locations al on al.location = os.Location and al.isOfficeClosed = 0
left join rpt_officeschedules op on op.Location = os.Location and (op.Operatory LIKE '%NP%' or op.Operatory LIKE '%OPEN%')
where
    os.AptTime between '07:59:00' and '17:30:00' and 
    os.AptDate between '2020-11-07' and '2020-11-08' and 
    (
        os.Operatory LIKE '%NP%' or 
        os.Operatory LIKE '%OPEN%' or 
        os.Operatory is null
    )
order by os.Location, os.AptDate, os.OperatoryNum, os.AptTime, COALESCE(os.Operatory, op.Operatory);

getting an error on left join `Unknown column 'rpt_officeschedules.Location' in 'on clause` — , Nov 05 '20 at 20:29
Yeah, forget to update join clauses after assigning alias to main table. Updated it, check now (obviously I don't have your tables, so can't check on my own) — Alexander Burov, Nov 06 '20 at 00:08

Replace Mysql null field with a custom string

1 Answers1