MapInfo Pro

I am having problems using nested functions while also using 'group by' within a simple SQL statement.

The following SQL statement works ok:

Select
TABLE_A.REGION,
TABLE_A.sort,
sum(TABLE_B.COLA) "_2019_20",
sum(TABLE_B.COLB) "_2020_21",
sum(TABLE_B.COLC) "_2021_22"
from TABLE_A, TABLE_B
where TABLE_A.Obj contains TABLE_B.Obj
group by TABLE_A.REGION
order by TABLE_A.sort
into OUTPUT

But when I try to round the results - eg

Select
TABLE_A.REGION,
TABLE_A.sort,
round(sum(TABLE_B.COLA),1) "_2019_20",
etc

...I get this error:

Found [(] while searching for [)]. Invalid or missing argument list.

Is it possible to have nested functions within an SQL statement like this? If not, what is the alternative in the above example?

------------------------------
Matthew Lee
Deep End Services
South Yarra VIC
------------------------------

Hi Matthew

The limitation is with the aggregates. You can't pass an aggregate to a function, for example Round it like you try to:
Select
TABLE_A.REGION,
TABLE_A.sort,
Round(Sum(TABLE_B.COLA),1) "_2019_20",
etc


------------------------------
Peter Horsbøll Møller
Distinguished Engineer
Precisely | Trust in Data
------------------------------

Original Message:
Sent: 08-23-2020 20:11
From: Matthew Lee
Subject: Nested functions in SQL

I am having problems using nested functions while also using 'group by' within a simple SQL statement.

The following SQL statement works ok:

Select
TABLE_A.REGION,
TABLE_A.sort,
sum(TABLE_B.COLA) "_2019_20",
sum(TABLE_B.COLB) "_2020_21",
sum(TABLE_B.COLC) "_2021_22"
from TABLE_A, TABLE_B
where TABLE_A.Obj contains TABLE_B.Obj
group by TABLE_A.REGION
order by TABLE_A.sort
into OUTPUT

But when I try to round the results - eg

Select
TABLE_A.REGION,
TABLE_A.sort,
round(sum(TABLE_B.COLA),1) "_2019_20",
etc

...I get this error:

Found [(] while searching for [)]. Invalid or missing argument list.

Is it possible to have nested functions within an SQL statement like this? If not, what is the alternative in the above example?

------------------------------
Matthew Lee
Deep End Services
South Yarra VIC
------------------------------

Hi Matthew,

that is unfortunately a limitation to Mapinfo SQL since the common databases allow aggregate results in functions without any restrictions.

For MapInfo the only solution is to query your result once again:
Select sum(col1) "c1" From tab1 Into q1
Select round(c1,10) From q1 Into q2​

------------------------------
Gisbert Schnell
AGIS GmbH
Frankfurt Am Main
Germany
------------------------------

Original Message:
Sent: 08-23-2020 20:11
From: Matthew Lee
Subject: Nested functions in SQL

I am having problems using nested functions while also using 'group by' within a simple SQL statement.

The following SQL statement works ok:

Select
TABLE_A.REGION,
TABLE_A.sort,
sum(TABLE_B.COLA) "_2019_20",
sum(TABLE_B.COLB) "_2020_21",
sum(TABLE_B.COLC) "_2021_22"
from TABLE_A, TABLE_B
where TABLE_A.Obj contains TABLE_B.Obj
group by TABLE_A.REGION
order by TABLE_A.sort
into OUTPUT

But when I try to round the results - eg

Select
TABLE_A.REGION,
TABLE_A.sort,
round(sum(TABLE_B.COLA),1) "_2019_20",
etc

...I get this error:

Found [(] while searching for [)]. Invalid or missing argument list.

Is it possible to have nested functions within an SQL statement like this? If not, what is the alternative in the above example?

------------------------------
Matthew Lee
Deep End Services
South Yarra VIC
------------------------------

Thanks for the advice Peter and Gisbert. That's a shame that you can't easily do it within MapInfo. I will probably leave as is and clean the data outside the MI environment.

Would perhaps be something the Precisely team may consider in a future update...?

------------------------------
Matthew Lee
Deep End Services
South Yarra VIC
------------------------------

Original Message:
Sent: 08-24-2020 08:58
From: Gisbert Schnell
Subject: Nested functions in SQL

Hi Matthew,

that is unfortunately a limitation to Mapinfo SQL since the common databases allow aggregate results in functions without any restrictions.

For MapInfo the only solution is to query your result once again:
Select sum(col1) "c1" From tab1 Into q1
Select round(c1,10) From q1 Into q2​

------------------------------
Gisbert Schnell
AGIS GmbH
Frankfurt Am Main
Germany

Original Message:
Sent: 08-23-2020 20:11
From: Matthew Lee
Subject: Nested functions in SQL

I am having problems using nested functions while also using 'group by' within a simple SQL statement.

The following SQL statement works ok:

Select
TABLE_A.REGION,
TABLE_A.sort,
sum(TABLE_B.COLA) "_2019_20",
sum(TABLE_B.COLB) "_2020_21",
sum(TABLE_B.COLC) "_2021_22"
from TABLE_A, TABLE_B
where TABLE_A.Obj contains TABLE_B.Obj
group by TABLE_A.REGION
order by TABLE_A.sort
into OUTPUT

But when I try to round the results - eg

Select
TABLE_A.REGION,
TABLE_A.sort,
round(sum(TABLE_B.COLA),1) "_2019_20",
etc

...I get this error:

Found [(] while searching for [)]. Invalid or missing argument list.

Is it possible to have nested functions within an SQL statement like this? If not, what is the alternative in the above example?

------------------------------
Matthew Lee
Deep End Services
South Yarra VIC
------------------------------