Problem while trying to aggregate in subquery

[sameer]

This is a exact sample of a query which I have been trying to run...

Code: Select all

SELECT  'abc'                                  AS ctx,
             SUM(A.TID)                       AS TID,
             SUM(A.AMT)                      AS AMT,
             SUM(A.BMT)                      AS BMT,
            (SELECT SUM(rzrm)
             FROM   abcd o)                  AS rzrm
FROM   xyz a
GROUP  BY 1  

This version works well in oracle but when trying it in vertica I get following error

[Vertica][VJDBC](4818) ERROR: Subqueries in the SELECT or ORDER BY are not supported if the subquery is not part of the GROUP BY

Any suggestions...

[JimKnicely]

Maybe something like this?

Code: Select all

dbadmin=> select * from xyz;
 tid | amt | bmt
-----+-----+-----
   1 |   1 |   1
   1 |   1 |   1
(2 rows)

dbadmin=> select * from abcd;
 rzrm
------
    2
    2
(2 rows)

Code: Select all

dbadmin=> SELECT ctx,
dbadmin->        tid,
dbadmin->        amt,
dbadmin->        bmt,
dbadmin->        (SELECT SUM(rzrm)
dbadmin(>           FROM abcd o) AS rzrm
dbadmin->   FROM (SELECT 'abc'      AS ctx,
dbadmin(>                SUM(A.TID) AS TID,
dbadmin(>                SUM(A.AMT) AS AMT,
dbadmin(>                SUM(A.BMT) AS BMT
dbadmin(>           FROM xyz a
dbadmin(>          GROUP BY 1) foo;
 ctx | tid | amt | bmt | rzrm
-----+-----+-----+-----+------
 abc |   2 |   2 |   2 |    4
(1 row)