Recent

Author Topic: Lazarus problem with function round on PostgreSQL 9.4  (Read 2751 times)

lublinkermi

  • New Member
  • *
  • Posts: 46
Lazarus problem with function round on PostgreSQL 9.4
« on: February 22, 2017, 07:53:27 pm »
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][+][-]
  1. SQL Error: ERROR: syntax error at or near ":"
  2. LINE 1: select round(bruttos:numeric,2) from diagnostyka_brutto
  3.                             ^
  4.  

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

  • Hero Member
  • *****
  • Posts: 1594
    • http://wiki.lazarus.freepascal.org/User:Zeljan
Re: Lazarus problem with function round on PostgreSQL 9.4
« Reply #1 on: February 22, 2017, 08:01:53 pm »
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][+][-]
  1. SQL Error: ERROR: syntax error at or near ":"
  2. LINE 1: select round(bruttos:numeric,2) from diagnostyka_brutto
  3.                             ^
  4.  

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

  • Newbie
  • Posts: 2
Re: Lazarus problem with function round on PostgreSQL 9.4
« Reply #2 on: February 22, 2017, 08:05:51 pm »
Hello,

for cast with zeos use  ::::

and correct sql:
select bruttos::::numeric(12,2) from diagnostyka_brutto;
« Last Edit: February 22, 2017, 08:08:32 pm by offline »

 

TinyPortal © 2005-2018