MapInfo Pro

Expand all | Collapse all

Nested functions in SQL

  • 1.  Nested functions in SQL

    Posted 08-23-2020 20:11
    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
    ------------------------------


  • 2.  RE: Nested functions in SQL

    Posted 08-24-2020 07:36
    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
    ------------------------------



  • 3.  RE: Nested functions in SQL

    Posted 08-24-2020 08:58
    Edited by Gisbert Schnell 08-24-2020 08:59
    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
    ------------------------------



  • 4.  RE: Nested functions in SQL

    Posted 08-24-2020 19:11
    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
    ------------------------------