How to Convert SQL View Creation Script into a Query in Oracle

oracleview

I have the following SQL view creation script. I would simply like to turn it into a query. I tried to replace the initial line with a SELECT statement, but this did not work. Can someone please show me what I'm doing incorrectly?

CREATE OR REPLACE FORCE VIEW "RMS"."VW_CR_ADDITIONAL_INFO" ("COMMIT_REG_ID", "FUND_CERTIFIED", "FUND_CERTIFIED_DATE", "CANCELED", "APPROVED", "AMOUNT", "LAST_STATE_ID") AS 
  WITH FUND_CERTIFIED AS (
  SELECT REQUEST_ID, MIN(TIME_STAMP) AS FUND_CERTIFIED_DATE
  FROM REQFLOW_STATE
  WHERE STATUS_ID = 6
  GROUP BY REQUEST_ID
),
APPROVED AS (
  SELECT DISTINCT REQUEST_ID
  FROM REQFLOW_STATE
  WHERE STATUS_ID NOT IN ( 5, 8 )
),
CANCELED AS (
  SELECT DISTINCT REQUEST_ID
  FROM REQFLOW_STATE
  WHERE STATUS_ID = 4
),
LAST_STATE AS (
  SELECT MAX(ID) AS LAST_STATE_ID, REQUEST_ID
  FROM REQFLOW_STATE
  WHERE STATUS_ID <> 8
  GROUP BY REQUEST_ID
)
SELECT CR.COMMIT_REG_ID,
NVL2( FC.FUND_CERTIFIED_DATE, 'Y', 'N' ) AS FUND_CERTIFIED,
FC.FUND_CERTIFIED_DATE,
NVL2( C.REQUEST_ID, 'Y', 'N' ) AS CANCELED,
NVL2( A.REQUEST_ID, 'Y', 'N' ) AS APPROVED,
SUM( NVL( CRD.AMOUNT, 0 ) ) AS AMOUNT,
LS.LAST_STATE_ID
FROM COMMITMENT_REGISTER CR
LEFT OUTER JOIN FUND_CERTIFIED FC ON ( FC.REQUEST_ID = CR.COMMIT_REG_ID )
LEFT OUTER JOIN APPROVED A ON ( A.REQUEST_ID = CR.COMMIT_REG_ID )
LEFT OUTER JOIN CANCELED C ON ( C.REQUEST_ID = CR.COMMIT_REG_ID )
LEFT OUTER JOIN LAST_STATE LS ON ( LS.REQUEST_ID = CR.COMMIT_REG_ID )
LEFT OUTER JOIN COMMITMENT_REGISTER_DETAIL CRD ON ( CRD.COMMIT_REG_ID = CR.COMMIT_REG_ID )
GROUP BY CR.COMMIT_REG_ID,
NVL2( FC.FUND_CERTIFIED_DATE, 'Y', 'N' ),
FC.FUND_CERTIFIED_DATE,
NVL2( C.REQUEST_ID, 'Y', 'N' ),
NVL2( A.REQUEST_ID, 'Y', 'N' ),
LS.LAST_STATE_ID;

Best Answer

Your query uses a common table expression, so replacing the first line with SELECT won't work. Comment out the first line of your script and you should have a SELECT query that returns the same results as the view. Reproducing your code in full:

WITH FUND_CERTIFIED AS (
  SELECT REQUEST_ID, MIN(TIME_STAMP) AS FUND_CERTIFIED_DATE
  FROM REQFLOW_STATE
  WHERE STATUS_ID = 6
  GROUP BY REQUEST_ID
),
APPROVED AS (
  SELECT DISTINCT REQUEST_ID
  FROM REQFLOW_STATE
  WHERE STATUS_ID NOT IN ( 5, 8 )
),
CANCELED AS (
  SELECT DISTINCT REQUEST_ID
  FROM REQFLOW_STATE
  WHERE STATUS_ID = 4
),
LAST_STATE AS (
  SELECT MAX(ID) AS LAST_STATE_ID, REQUEST_ID
  FROM REQFLOW_STATE
  WHERE STATUS_ID <> 8
  GROUP BY REQUEST_ID
)
SELECT CR.COMMIT_REG_ID,
NVL2( FC.FUND_CERTIFIED_DATE, 'Y', 'N' ) AS FUND_CERTIFIED,
FC.FUND_CERTIFIED_DATE,
NVL2( C.REQUEST_ID, 'Y', 'N' ) AS CANCELED,
NVL2( A.REQUEST_ID, 'Y', 'N' ) AS APPROVED,
SUM( NVL( CRD.AMOUNT, 0 ) ) AS AMOUNT,
LS.LAST_STATE_ID
FROM COMMITMENT_REGISTER CR
LEFT OUTER JOIN FUND_CERTIFIED FC ON ( FC.REQUEST_ID = CR.COMMIT_REG_ID )
LEFT OUTER JOIN APPROVED A ON ( A.REQUEST_ID = CR.COMMIT_REG_ID )
LEFT OUTER JOIN CANCELED C ON ( C.REQUEST_ID = CR.COMMIT_REG_ID )
LEFT OUTER JOIN LAST_STATE LS ON ( LS.REQUEST_ID = CR.COMMIT_REG_ID )
LEFT OUTER JOIN COMMITMENT_REGISTER_DETAIL CRD ON ( CRD.COMMIT_REG_ID = CR.COMMIT_REG_ID )
GROUP BY CR.COMMIT_REG_ID,
NVL2( FC.FUND_CERTIFIED_DATE, 'Y', 'N' ),
FC.FUND_CERTIFIED_DATE,
NVL2( C.REQUEST_ID, 'Y', 'N' ),
NVL2( A.REQUEST_ID, 'Y', 'N' ),
LS.LAST_STATE_ID;
Related Question