Lazarus problem with function round on PostgreSQL 9.4
lazarus / 16-Databases

----------------------------------------------------------------------
lublinkermi · 2017-02-22 18:53

I have a next problem.
If I use query in PgAdmin then query is running. If I use Zeos
7.1.3-stable+ Lazarus 1.0.12 then I read message :
Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i SQL Error:
ERROR: syntax error at or near ":"LINE 1: select
round(bruttos:numeric,2) from diagnostyka_brutto                     
     ^ 
Sql query is very simple: select round(bruttos::numeric,2) from
diagnostyka_brutto 
How I can rounding by SQL? Is it possible?

Besides the strange thing it is that the characters are '::' and
displays one character ':'

----------------------------------------------------------------------
zeljko · 2017-02-22 19:01

Quote from: lublinkermi on February 22, 2017, 07:53:27 pmI have a next
problem.
If I use query in PgAdmin then query is running. If I use Zeos
7.1.3-stable+ Lazarus 1.0.12 then I read message :
Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i SQL Error:
ERROR: syntax error at or near ":"LINE 1: select
round(bruttos:numeric,2) from diagnostyka_brutto                     
     ^ 
Sql query is very simple: select round(bruttos::numeric,2) from
diagnostyka_brutto 
How I can rounding by SQL? Is it possible?

Besides the strange thing it is that the characters are '::' and
displays one character ':'

If U use ZEOS then you must disable ParamCheck because :: means param
and it's name.
So:
RQRY: TZReadOnlyQuery;
...
RQRY.ParamCheck := False;
RQRY.SQL.Add('SELECT round(something::numeric(12,2)) FROM mytable');
RQRY.ExecSQL;
.....

OR if you do not want to play with ParamCheck use CAST eg:
RQRY.SQL.Add('SELECT CAST(round(something) AS numeric(12,2) ) FROM
mytable');
RQRY.ExecSQL;

Also, postgresql function round() supports param for num decimals, so
you do not need cast to numeric at all:
SELECT round(myvalue,2) FROM mytable;

----------------------------------------------------------------------
offline · 2017-02-22 19:05

Hello,

for cast with zeos use  ::::

and correct sql:
select bruttos::::numeric(12,2) from diagnostyka_brutto;