SQL & PL/SQL

I will include the query below my question as it is very long.

I am using Microsoft SQL Server Reporting Services to connect to an Oracle Database (9i v 2). When I put in the query below I get the following error coming from the Oracle Server -

"TITLE: Microsoft Report Designer
------------------------------

An error occurred while executing the query.
ORA-32036: unsupported case for inlining of query name in WITH clause"

I found an explanation for this error - " There is at least one query name which is inlined more than once because it's definition query is too simple and references another query name. This is currently unsupported yet." - and taking out the sub-queries one by one it seems to be COMBINE_TWO sub-query that causes the issue. Apparently it doesn't like the Select * from BLAHBLAH and finds it to be unecessary. I have made this query using nested selects - but the code gets redundant and I end up having to use the same query two or three times....the whole statement takes far too long to execute.

Any coding suggestions or workaround suggestions would be appreciated - I have been looking at this problem so long that I have tunnel vision. There has to be a fix or a better way...

Thanks!

Brett

Here is the query....

WITH  
  /*This query finds all pending WO's and caculates all changing service 
  levels for limited, basic, internet and phone.  Any values that do not 
  change are kept at null, any services being disconnected are dropped to a negative
  and service being upgrades and/or downgraded but still kept are valued
  at their to_quantity value. */
  STEP_1 AS
  (
    SELECT
      WO.ACCOUNT_NUMBER,
      WO.WORK_ORDER_NUMBER,
      WO.WO_TYPE,
      WOD.SERVICE_CODE,
      CASE WHEN WOD.SERVICE_CODE = '101' AND WOD.FROM_QUANTITY != WOD.TO_QUANTITY THEN WOD.TO_QUANTITY - WOD.FROM_QUANTITY ELSE NULL END LIM,
      CASE WHEN WOD.SERVICE_CODE = '111' AND WOD.FROM_QUANTITY != WOD.TO_QUANTITY THEN WOD.TO_QUANTITY - WOD.FROM_QUANTITY ELSE NULL END EXP,
      CASE WHEN WOD.SERVICE_CODE = '30001' AND WOD.FROM_QUANTITY != WOD.TO_QUANTITY THEN WOD.TO_QUANTITY - WOD.FROM_QUANTITY ELSE NULL END HSI,
      CASE WHEN WOD.SERVICE_CODE IN ('LINE FL', 'ULTS FL') AND WOD.FROM_QUANTITY != WOD.TO_QUANTITY THEN WOD.TO_QUANTITY - WOD.FROM_QUANTITY ELSE NULL END PHONE
    FROM
      KAN_WORK_ORDER_MASTER WO INNER JOIN KAN_WORK_ORDER_DETAIL WOD ON WO.WORK_ORDER_NUMBER = WOD.WORK_ORDER_NUMBER
    WHERE
      WO.WO_TYPE IN ('DI', 'DW', 'IN', 'SR', 'UP')
      AND WOD.SERVICE_CODE IN ('101', '111', '30001', 'LINE FL', 'ULTS FL')
      AND POOL = 'A'
      AND WO_STATUS = ' '),
 
 /* This adds all values from step 1 and condenses all work orders
 into one row, a value for each service. */
 STEP_2 AS
 (
    SELECT
      ACCOUNT_NUMBER,
      WORK_ORDER_NUMBER,
      WO_TYPE,
      SUM(LIM) LIM,
      SUM(EXP) EXP,
      SUM(HSI) HSI,
      SUM(PHONE) PHONE
    FROM
      STEP_1
    GROUP BY
      ACCOUNT_NUMBER,
      WORK_ORDER_NUMBER,
      WO_TYPE
    ORDER BY
      ACCOUNT_NUMBER),
  
  /* This query takes the results from Step 2 and then looks at all service
  levels that were not included or changed in the work order detail file 
  and makes sure that unchanged services are included in the final total. 
  FOR EXAMPLE : Coming from step 2, if LIM is NULL we know that no changes 
  were done to LIM in the work order - so, if the customer has Limited then
  the service quantity is added to the total.  Else - if LIM is not null, then
  we know the service level was effected by the work order - so do not
  include the quantity from the services file because it will be innacurate
  once the work order is complete*/
  STEP_3 AS
  (
   SELECT
      WO.ACCOUNT_NUMBER,
      WO.WORK_ORDER_NUMBER,
      WO.WO_TYPE,
      CASE WHEN LIM IS NULL AND CS.SERVICE_CODE = '101' THEN (CS.SERVICE_QUANTITY + CS.SERVICE_QUANTITY_TO_FREE) ELSE NULL END LIM,
      CASE WHEN EXP IS NULL AND CS.SERVICE_CODE = '111' THEN (CS.SERVICE_QUANTITY + CS.SERVICE_QUANTITY_TO_FREE) ELSE NULL END EXP,
      CASE WHEN HSI IS NULL AND CS.SERVICE_CODE = '30001' THEN (CS.SERVICE_QUANTITY + CS.SERVICE_QUANTITY_TO_FREE) ELSE NULL END HSI,
      CASE WHEN PHONE IS NULL AND CS.SERVICE_CODE IN ('LINE FL', 'ULTS FL') THEN (CS.SERVICE_QUANTITY + CS.SERVICE_QUANTITY_TO_FREE) ELSE NULL END PHONE
    FROM
      STEP_2 WO INNER JOIN KAN_CUSTOMER_SERVICES CS ON WO.ACCOUNT_NUMBER = CS.ACCOUNT_NUMBER
    WHERE
      CS.SERVICE_CODE IN ('101', '111', '30001', 'LINE FL', 'ULTS FL')
      AND CS.SERVICE_STATUS = 'A'),

  /* This step calculated the current service level. */
  STEP_4 AS
  (
    SELECT
          ACCOUNT_NUMBER,
          WORK_ORDER_NUMBER,
          WO_TYPE,
          SUM(LIM) LIM,
          SUM(EXP) EXP,
          SUM(HSI) HSI,
          SUM(PHONE) PHONE
        FROM
          STEP_3
        GROUP BY
          ACCOUNT_NUMBER,
          WORK_ORDER_NUMBER,
          WO_TYPE
      ),
  
  COMBINE_TWO AS
  (
    SELECT 
      *
    FROM
      STEP_2
    UNION ALL
    SELECT
      *    
    FROM
      STEP_4
  )
  

    SELECT
      WOM.SCHEDULE_DATE,
      COMBINE_TWO.ACCOUNT_NUMBER,
      COMBINE_TWO.WORK_ORDER_NUMBER,
      COMBINE_TWO.WO_TYPE,
      HM.ADDRESS_LINE_1,
      HM.ADDRESS_LINE_2,
      HM.ADDRESS_LINE_3,
      HM.ADDRESS_LINE_4,
      SUM(LIM) LIM,
      SUM(EXP) EXP,
      SUM(HSI) HSI,
      SUM(PHONE) PHONE
    FROM
        COMBINE_TWO
        INNER JOIN KAN_CUSTOMER_MASTER CM ON COMBINE_TWO.ACCOUNT_NUMBER = CM.ACCOUNT_NUMBER
        INNER JOIN KAN_HOUSE_MASTER HM ON CM.HOUSE_NUMBER = HM.HOUSE_NUMBER
        INNER JOIN KAN_WORK_ORDER_MASTER WOM ON COMBINE_TWO.WORK_ORDER_NUMBER = WOM.WORK_ORDER_NUMBER
    GROUP BY
      WOM.SCHEDULE_DATE,
      COMBINE_TWO.ACCOUNT_NUMBER,
      COMBINE_TWO.WORK_ORDER_NUMBER,
      COMBINE_TWO.WO_TYPE,
      HM.ADDRESS_LINE_1,
      HM.ADDRESS_LINE_2,
      HM.ADDRESS_LINE_3,
      HM.ADDRESS_LINE_4
    HAVING
      (
        SUM(LIM) <= 0
        AND SUM(EXP) <= 0
      )
      AND
      (
        SUM(HSI) >= 1
        OR SUM(PHONE) >= 1
      )
    ORDER BY
      ACCOUNT_NUMBER