How to use data table functions in read data?

healyhatman 27 Apr, 2018
I want to be able to use COALESCE to get one of two values.

I have timesheet.earnings_rate , and template.ordinary_earnings_rate_id
COALESCE(timesheet.earnings_rate, template.ordinary_earnings_rate_id):timesheet.earnings_rate
gives me
You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'template.ordinary_earnings_rate_id) AS `timesheet.earnings_rate`, `employee`.`va' at line 1
I assume it's breaking on the comma between the two fields, but looking at the code read_data_output.php isn't helping me😟
healyhatman 27 Apr, 2018
Answer
https://www.chronoengine.com/forums/posts/t105174/mysql-functions-in-fields-to-retrieve-in-read-data?keywords=database%20functions

1. Commas in parameters need to be surrounded by spaces as in [param1 , param2]
2. Need to use model.field, can't use field on its own

In this way, more complicated queries can be used, like
if(entry.earnings_rate is null or entry.earnings_rate = '' , template.ordinary_earnings_rate_id , entry.earnings_rate):entry.LogicRate
This topic is locked and no more replies can be posted.