[Dbix-class] Howto create Oracle WITH statements

[Darren Duncan]

Jorge Gonzalez wrote:
> WITH DATOS_MESES AS
>       ( SELECT NOMBRE,EXTRACT(MONTH FROM FECHA) AS
>       MES,EXTRACT(YEAR FROM FECHA) AS ANYO FROM LINEAS WHERE
>       FECHA >= '01-JUN-10' AND FECHA < '01-MAR-11' )
> SELECT COUNT(NOMBRE) AS ALTAS_MES,MES,ANYO FROM DATOS_MESES
> GROUP BY ANYO,MES ORDER BY ANYO,MES ASC;

Besides letting you more clearly name and format parts of your queries, the main 
advantage of WITH is when you have the same subquery you want to use *multiple* 
times in your main query (or another subquery), so you benefit from the 
refactoring out common SQL code, or if it is recursive.

Since you appear to only use the subquery in one place, you can alternately 
write it like this:

   SELECT COUNT(NOMBRE) AS ALTAS_MES, MES, ANYO
     FROM (
         SELECT NOMBRE, EXTRACT(MONTH FROM FECHA) AS MES,
             EXTRACT(YEAR FROM FECHA) AS ANYO
           FROM LINEAS
           WHERE FECHA >= '01-JUN-10' AND FECHA < '01-MAR-11'
       ) AS DATOS_MESES
     GROUP BY ANYO, MES
     ORDER BY ANYO, MES ASC;

Of course, the WITH version is easier to read (and I prefer it where it is 
supported), even if you only use the subquery in one place, but the above 
alternative will work with more or older ORMs or DBMSs.

-- Darren Duncan