Buy Now
Sign in

MySQL functions in Fields to retrieve in Read Data

E
emmexx
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
admin
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
Max
If your main question got answered then please mark the answer using the button!​
​Please let us know if you have any problems with the new forums text editor, we appreciate your feedback!
E
emmexx
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
2
2D77rus
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
2
2D77rus
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.
H
healyhatman
This doesn't appear to work with COALESCE
COALESCE(earnings_rate , template.ordinary_earnings_rate_id):LogicRate
Gives me
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
I get the same thing from IFNULL​
​​
​​
​EDIT: NEVERMIND: It works but you have to specify the model for both parameters.
Web developer at SkySpider.com.au