主页 / dba / 问题 / 186487
Accepted
Ebony Maw
Asked: 2017-09-21 17:00:28 +0800 CST

如何将 SQL 视图创建脚本转换为查询？

  • 772

我有以下 SQL 视图创建脚本。我只想把它变成一个查询。我试图用 SELECT 语句替换初始行，但这没有用。有人可以告诉我我做错了什么吗？

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;
oracle view

1 个回答

  1. Best Answer

    您的查询使用公用表表达式，因此用 替换第一行将SELECT不起作用。注释掉脚本的第一行，您应该有一个SELECT返回与视图相同结果的查询。完整复制您的代码：

    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;
    • 3

相关问题