How to use data table functions in read data?

Forums

Please write a review for the extension you are using on the Joomla extensions directory before posting a new question as we are being spammed by many negative reviews.

Thank you for your support!

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.