Results 1 to 4 of 4
  1. #1
    Join Date
    Mar 2010
    Posts
    1

    Unanswered: Help me convert this to Oracle

    INSERT INTO PS_AF_PRWK_CNT_T(PROCESS_INSTANCE
    , AF_STORE_NUM
    ,EMPLID
    ,WEEK_END_DT
    ,DAYOFWEEK
    ,COUNTER ) WITH n(PROCESS_INSTANCE
    ,AF_STORE_NUM
    ,EMPLID
    ,WEEK_END_DT
    ,DAYOFWEEK) AS (
    SELECT PROCESS_INSTANCE,AF_STORE_NUM
    ,EMPLID
    ,WEEK_END_DT
    ,DAYOFWEEK
    FROM PS_AF_PRWK_SCH_T WHERE EMPLID <> 'UNFILL'
    UNION ALL
    SELECT PROCESS_INSTANCE
    ,nplus1.AF_STORE_NUM
    ,nplus1.EMPLID
    ,nplus1.WEEK_END_DT
    ,nplus1.DAYOFWEEK
    FROM PS_AF_PRWK_SCH_T nplus1
    ,n
    WHERE n.DAYOFWEEK = nplus1.DAYOFWEEK-1
    AND n.EMPLID=nplus1.EMPLID
    AND n.AF_STORE_NUM=nplus1.AF_STORE_NUM
    AND n.WEEK_END_DT=nplus1.WEEK_END_DT )
    SELECT PROCESS_INSTANCE
    ,AF_STORE_NUM
    ,EMPLID
    ,WEEK_END_DT
    ,DAYOFWEEK
    , COUNT(*)
    FROM n
    WHERE DAYOFWEEK=7
    GROUP BY AF_STORE_NUM ,EMPLID,WEEK_END_DT,DAYOFWEEK

  2. #2
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    You can ask on Oracle forum.
    Oracle - dBforums

  3. #3
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Convert the common table expression into a subselect.
    ---
    "It does not work" is not a valid problem statement.

  4. #4
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    The common table expression "n" is a recursive CTE.
    So, it might be converted to Oracle by using CONNECT BY syntax.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •