Re: Temporary table concept available with the 'with'

Srinivasan, Sriram wrote:
> 0001.00 C/EXEC SQL
> 0002.00 C+ Declare CURS1 CURSOR FOR
> 0004.00 C+ WITH T1 AS (SELECT F1.SNAME, COUNT(F2.CNUM) AS CCUS
> 0010.01 C+             FROM SALPEP F1 JOIN CUST F2 ON F1.SNUM =
> F2.SNUM) 0010.04 C+ SELECT SNAME, CCUS FROM T1
> 0010.06 C+ ORDER BY SNAME
> 0018.00 C/END-EXEC
> 0019.00 C
> 0020.00 C/EXEC SQL
> 0021.00 C+ OPEN CURS1
> 0022.00 C/END-EXEC
> 0023.00 C
> 0024.00 C/EXEC SQL
> 0025.00 C+ CLOSE CURS1
> 0026.00 C/END-EXEC
> 
> The spool display of the error part is like this......
> 
> MSG ID  SEV  RECORD  TEXT
> SQL0122  30       3  Position 31 Column SNAME or expression in SELECT
>                      list not valid.

I would change this line:  
0004.00 C+ WITH T1 AS (SELECT F1.SNAME, COUNT(F2.CNUM) AS CCUS
to
0004.00 C+ WITH T1 AS (SELECT F1.SNAME as sname, COUNT(F2.CNUM) AS CCUS

Bill