Archives of the TeradataForum

Message Posted: Wed, 07 Nov 2007 @ 19:43:22 GMT

	<Prev	Next>		<<First	<Prev	Next>	Last>>

<-- Anonymously Posted: Wed, 7 Nov 2007 11:46 -->

Teradata supports "scalar subquery" only in extremely limited situations.

Try something like this instead:

     SELECT A1.OFFICE, SUM(A1.HOURS) AS HOURS,
       SUM(A1.HOURS) /A2.TOT_HOURS AS HRSPCT
     FROM TABLE AS A1
       CROSS JOIN
     (SELECT SUM(HOURS) AS TOT_HOURS FROM TABLE) AS A2
     WHERE A1.OFFICE LIKE 'AD%'
     GROUP BY A1.OFFICE;

Or since it's the same table:

     SELECT A2.OFFICE, A2.HOURS,
       A2.HOURS/(SUM(A2.HOURS) OVER (ROWS BETWEEN UNBOUNDED PRECEDING AND
     UNBOUNDED FOLLOWING)) AS HRSPCT
     FROM
     (SELECT A1.OFFICE, SUM(A1.HOURS) AS HOURS
       FROM TABLE AS A1
     WHERE A1.OFFICE LIKE 'AD%'
     GROUP BY A1.OFFICE) A2;

	<Prev	Next>		<<First	<Prev	Next>	Last>>