Buy Now
Sign in

MySQL functions in Fields to retrieve in Read Data

E
emmexx 422
March 03 2018 #375824
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 26.0
March 03 2018 #375827
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 422
March 03 2018 #375831
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 9
April 17 2018 #377074
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 9
April 19 2018 #377103
1
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.
healyhatman 5.2
April 27 2018 #377325
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
I don't work for ChronoEngine but I do accept donations paypal.me/healyhatman

You can now copy+paste code from forums