CASE Statement in Generated Column

Data Service
1

I have a situation where an occasionally blank value in the database propagates forward and prevents other generated columns from calculating. Ideally the subsequent columns would treat a blank value as a zero but they do not.

I am trying to setup a CASE statement that will make sure the field takes the value of 0 if another field is blank.

I have tried various permutations of the statement below, including changing the ELSE to another WHEN statement, using IS NULL, and a variety of other things. In some cases the statement will at least evaluate but it still doesn’t work. Can you help me identify the error in my syntax or point me toward another solution to make a blank value in a generated column (where I can’t set a default) be treated as a zero?

CASE
WHEN mileageEndOfDay = ‘’
THEN ‘0’
ELSE
THEN mileageEndOfDay - mileageDropoff
END

Resources used:

https://dev.mysql.com/doc/refman/5.7/en/case.html

2

here’s what ChatGPT suggests, I have not tried, but have a feeling it will work:

ChatGPT 2024-07-18 18-29-52
ChatGPT 2024-07-18 18-29-52840×820 199 KB

Here’s the text version of the statement:

    CASE
        WHEN mileageEndOfDay = '' THEN 0
        ELSE mileageEndOfDay - mileageDropoff
    END
3

Thanks for the quick reply Mark! It didn’t occur to me to work with chatGPT on this one. I think I thought the expressions were limited to some subset of standard SQL.

Do I truly have access to all of SQL in these statements?

4

Generally, yes. Keep in mind that generated column expressions must be deterministic; this means you cannot use functions that produce different results every time they are called (i.e., NOW(), UUID(), etc.).

Regards,
Mark

5

Yeah I do recall the deterministic constraint. Thanks for the heads up on full SQL though. That’ll open up some doors. You got me going again though. Thanks!

Maybe last couple questions while I’ve got you here

  • Is the best way to allow a user to export data from a view to use the CSV/Excel Plugin API service or is there another more direct way to allow them to export data?
  • If I setup a View Data Grid to allow them to preview the data with some preset where clauses or other filters, can I then access the data within that View Data Grid directly or should I just ensure that the where clause in the API Service is the same clause that was used to preview the data?

Thanks again!

6

Could you please open separate topics for each question? This will help us keeping things more organized and others others finding relevant information.

Thank you!

Mark

7

Will do!