With Clause . . .

[kpmac]

Does anyone know if you can do an insert statement while using a WITH CLAUSE?

Here is my code:

I would like to replace the final select with an insert but the precompiler keeps barking at me.

If not I will try using a couple global temps or some messy select sub qry's in the LEFT OUTERS.

WITH
   sum_all_sales 
AS 
(
  SELECT 
    PQ5STORE as Div,
    TRIM(PQ5IP) || TRIM(PQ5I1) || TRIM(PQ5I4) as product, 
    PQ5I2 as Color,
    SUM(PQ5QTY) as slsstd,
    SUM(CASE WHEN PQ5TDATE = ( CurDate() - (DayOfWeek(CurDate()) Days ) ) THEN PQ5QTY ELSE 0 END ) As SLS,
    SUM(CASE WHEN PQ5TDATE = ( CurDate() - (DayOfWeek(CurDate()) Days )) - 7 Days  THEN PQ5QTY ELSE 0 END ) As SLS2WK,
    SUM(CASE WHEN PQ5TDATE = ( CurDate() - (DayOfWeek(CurDate()) Days )) - 14 Days  THEN PQ5QTY ELSE 0 END ) As SLS3WK,
    SUM(CASE WHEN PQ5TDATE = ( CurDate() - (DayOfWeek(CurDate()) Days )) - 21 Days  THEN PQ5QTY ELSE 0 END ) As SLS4WK,
    SUM(CASE WHEN PQ5TDATE = ( CurDate() - (DayOfWeek(CurDate()) Days )) - 28 Days  THEN PQ5QTY ELSE 0 END ) As SLS5WK
  FROM 
    APPCUST30.PQ5000
  WHERE 
    PQ5C = 'EK' 
    and PQ5D = '01' 
  Group By 
    PQ5STORE,
    TRIM(PQ5IP) || TRIM(PQ5I1) || TRIM(PQ5I4), 
    PQ5I2     ),

   sum_all_inv
 AS 
 (
   SELECT 
      PQ6STORE as Div,
      TRIM(PQ6IP) || TRIM(PQ6I1) || TRIM(PQ6I4) as product, 
      PQ6I2 as Color,
      SUM(PQ6QTY) as onhand
   FROM 
      APPCUST30.PQ6000
   WHERE 
      PQ6C = 'EK' 
      and PQ6D = '01' 
      and PQ6TDATE = '07/22/2006'   
   GROUP BY 
      PQ6STORE,
      TRIM(PQ6IP) || TRIM(PQ6I1) || TRIM(PQ6I4)  ,
       PQ6I2       ) ,
       
   first_rcpt_date
 AS 
 (
   SELECT 
     PQ6STORE as Div,
     TRIM(PQ6IP) || TRIM(PQ6I1) || TRIM(PQ6I4) as product, 
     PQ6I2 as Color,
     Min(PQ6TDATE)  as first_rcpt
   FROM 
     APPCUST30.PQ6000
   WHERE 
     PQ6C = 'EK' 
     and PQ6D = '01' 
   GROUP BY   
    PQ6STORE,
    TRIM(PQ6IP) || TRIM(PQ6I1) || TRIM(PQ6I4), 
    PQ6I2      )





SELECT 
  sales.Div,
  sales.product, 
  sales.color, 
  Coalesce(first.first_rcpt,
  Date('01/07/2006')) as first_rcpt,
  sales.Sls,
  sales.sls2wk,
  sales.sls3wk,
  sales.sls4wk,
  sales.sls5wk,
  sales.slsstd,
  Coalesce(inv.onhand,0) as OnHand
FROM
  sum_all_sales sales
  LEFT OUTER JOIN sum_all_inv inv
  ON 
   sales.Div = inv.Div
   and sales.Product = inv.Product
   and sales.Color = inv.Color
  LEFT OUTER JOIN first_rcpt_date first
  ON 
   first.Div = sales.Div
   and first.Product = sales.Product
   and first.Color = sales.Color

Order by 2,3, 1 

[kpmac]

Re: With Clause . . .

OK, I modified the code to the following. However, I would much rather use a WITH Clause if possible.

Delete FROM KENPROD.PID06R;

INSERT INTO KENPROD.PID06R 

SELECT 
  sales.Div,
  sales.product, 
  ' ',
  0,
  sales.Sls,
  0,
  sales.sls2wk,
  sales.sls3wk,
  sales.sls4wk,
  sales.sls5wk,
  sales.slsstd,
  0,
  0,
  0,
  0,
  0,
  0,
  0,
  sales.Color,
  ' ',
  ' ',
  ' '
  --Coalesce(inv.onhand,0) as OnHand
FROM  
  (     SELECT 
        PQ5STORE as Div,
        TRIM(PQ5IP) || TRIM(PQ5I1) || TRIM(PQ5I4) as product, 
        PQ5I2 as Color,
        SUM(PQ5QTY) as slsstd,
        SUM(CASE WHEN PQ5TDATE = ( CurDate() - (DayOfWeek(CurDate()) Days ) ) THEN PQ5QTY ELSE 0 END ) As SLS,
        SUM(CASE WHEN PQ5TDATE = ( CurDate() - (DayOfWeek(CurDate()) Days )) - 7 Days  THEN PQ5QTY ELSE 0 END ) As SLS2WK,
        SUM(CASE WHEN PQ5TDATE = ( CurDate() - (DayOfWeek(CurDate()) Days )) - 14 Days  THEN PQ5QTY ELSE 0 END ) As SLS3WK,
        SUM(CASE WHEN PQ5TDATE = ( CurDate() - (DayOfWeek(CurDate()) Days )) - 21 Days  THEN PQ5QTY ELSE 0 END ) As SLS4WK,
        SUM(CASE WHEN PQ5TDATE = ( CurDate() - (DayOfWeek(CurDate()) Days )) - 28 Days  THEN PQ5QTY ELSE 0 END ) As SLS5WK
      FROM 
        APPCUST30.PQ5000
      WHERE 
        PQ5C = 'EK' 
        and PQ5D = '01' 
      GROUP BY 
        PQ5STORE,
        TRIM(PQ5IP) || TRIM(PQ5I1) || TRIM(PQ5I4), 
        PQ5I2     
   )  sales
   
   LEFT OUTER JOIN      (         SELECT 
                                    PQ6STORE as Div,
                                    TRIM(PQ6IP) || TRIM(PQ6I1) || TRIM(PQ6I4) as product, 
                                    PQ6I2 as Color,
                                    SUM(PQ6QTY) as onhand
                                  FROM 
                                    APPCUST30.PQ6000
                                  WHERE 
                                    PQ6C = 'EK' 
                                    and PQ6D = '01' 
                                    and PQ6TDATE = '07/22/2006'   
                                  GROUP BY 
                                    PQ6STORE,
                                    TRIM(PQ6IP) || TRIM(PQ6I1) || TRIM(PQ6I4)  ,
                                  PQ6I2 
                          )  inv
     ON 
       sales.Div = inv.Div
       and sales.Product = inv.Product
       and sales.Color = inv.Color
       
   LEFT OUTER JOIN      (         SELECT
                                    PQ6STORE as Div,
                    TRIM(PQ6IP) || TRIM(PQ6I1) || TRIM(PQ6I4) as product,
                    PQ6I2 as Color,
                    MIN(PQ6TDATE)  as first_rcpt
                  FROM
                    APPCUST30.PQ6000
                  WHERE
                    PQ6C = 'EK'
                    and PQ6D = '01'
                  GROUP BY
                    PQ6STORE,
                    TRIM(PQ6IP) || TRIM(PQ6I1) || TRIM(PQ6I4),
                    PQ6I2
                     ) first
   ON
     first.Div = sales.Div
     and first.Product = sales.Product
     and first.Color = sales.Color

;