Forums

I tried to use the IFNULL, IF, and CASE functions in the Fields to retrieve property of a Read Data to no avail. COUNT works, probably other mysql functions work but not the one I need.
Is it possible to use functions other than COUNT?
Is there a particular syntax?
​
Thank you
maxx

Hi maxx,
​
Please provide an example of what you are trying to achieve, SUM and any simple function should work, but complex syntax may not.
​
Best regards

Please provide an example of what you are trying to achieve, SUM and any
simple function should work, but complex syntax may not.

As an example I want the created value if the modifed value of a record is null. So I tried:

IFNULL(modified,created):alias_name

or

IFNULL(Model.modified,Model.created):Model.alias_name

But I got a joomla error:

1582 Incorrect parameter count in the call to native function 'IFNULL'

Similar errors with IF or CASE.
​
maxx

Me too!
Looks like comma in function parameters messes things up:
​

FORMAT(Credappl.amount,2):amount_formatted
DATE_FORMAT(Credappl.date_in,'%d-%m-%Y'):date_alias 

- ERROR, but

DATE(Credappl.date_in):date_alias

- OK.
​
Is there any workaround posible?
​
Dmitry

I've got it fixed! Just need to add spaces:
​

format(Credappl.amount,2):amount_formatted

- ERROR, but
​

format(Credappl.amount , 2):amount_formatted

works OK. The same with more complex expressions.

This doesn't appear to work with COALESCE
​

COALESCE(earnings_rate , template.ordinary_earnings_rate_id):LogicRate

Gives me
​[pre]1064
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 '`entry`.`earnings_rate`_id) AS `entry.LogicRate` FROM `#__employee_timesheet_' at line 1[/pre]
​
I get the same thing from IFNULL

[br]EDIT: NEVERMIND: It works but you have to specify the model for both parameters.