Oracle中不支持使用with子句

这是我SQL语句：
WITH  T  AS(SELECT ENTERPRISE_NAME AS "板块公司",PERCENTAGE AS "整改完成率",V_PERCENTAGE AS "验证率"  FROM (

WITH BASE(BASE_REC_ID,VLD_ENTERPRISE_ID) AS
(
    SELECT RB.BASE_REC_ID,VE.VLD_ENTERPRISE_ID
    FROM C_SUPERVISE_CHKSAFTY_RECB_TB RB
    INNER JOIN V_VLD_SITE_ENTERPRISE VE ON RB.VLD_SITE_ID = VE.VLD_SITE_ID
    INNER JOIN VLD_SITE ON RB.VLD_SITE_ID = VLD_SITE.VLD_SITE_ID
    WHERE VLD_SITE.ORG_STATUS = 'A'
),
TBL1 AS
(
    SELECT RB.VLD_ENTERPRISE_ID,
           COUNT(*) AS NTB
    FROM C_SUPERVISE_CHK_PROBLEMLIST_TB                 P
    INNER JOIN C_SUPERVISE_CHK_TREATBILL_TB             B
          ON P.TREAT_INFORM_ID = B.TREAT_INFORM_ID AND B.CHK_CLASSIFY='3'
    INNER JOIN C_SUPERVISE_CHKSAFTY_REC_TB              R
          ON B.REC_ID = R.REC_ID
    INNER JOIN BASE                                     RB
          ON R.BASE_REC_ID = RB.BASE_REC_ID
    WHERE P.TREAT_STATUS='1' OR P.TREAT_STATUS='2' AND P.RESTRIC_TREAT_DATE<SYSDATE
    GROUP BY RB.VLD_ENTERPRISE_ID
),
TBL2 AS
(
    SELECT RB.VLD_ENTERPRISE_ID,
           COUNT(*) AS TBNV
    FROM C_SUPERVISE_CHK_PROBLEMLIST_TB             P
    INNER JOIN C_SUPERVISE_CHK_TREATBILL_TB         B
          ON P.TREAT_INFORM_ID = B.TREAT_INFORM_ID AND B.CHK_CLASSIFY='3'
    INNER JOIN C_SUPERVISE_CHKSAFTY_REC_TB          R
          ON B.REC_ID = R.REC_ID
    INNER JOIN BASE                                 RB
          ON R.BASE_REC_ID = RB.BASE_REC_ID
    WHERE P.TREAT_STATUS='4' AND P.RESTRIC_TREAT_DATE<SYSDATE
    GROUP BY RB.VLD_ENTERPRISE_ID
),
TBL3 AS
(
    SELECT RB.VLD_ENTERPRISE_ID,
           COUNT(*) AS V
    FROM C_SUPERVISE_CHK_PROBLEMLIST_TB             P
    INNER JOIN C_SUPERVISE_CHK_TREATBILL_TB         B
          ON P.TREAT_INFORM_ID = B.TREAT_INFORM_ID AND B.CHK_CLASSIFY='3'
    INNER JOIN C_SUPERVISE_CHKSAFTY_REC_TB          R
          ON B.REC_ID = R.REC_ID
    INNER JOIN BASE                                 RB
          ON R.BASE_REC_ID = RB.BASE_REC_ID
    WHERE P.TREAT_STATUS='5' AND P.RESTRIC_TREAT_DATE<SYSDATE
    GROUP BY RB.VLD_ENTERPRISE_ID
),
TBL4 AS
(
    SELECT RB.VLD_ENTERPRISE_ID,
           COUNT(*) AS TBING
    FROM C_SUPERVISE_CHK_PROBLEMLIST_TB             P
    INNER JOIN C_SUPERVISE_CHK_TREATBILL_TB         B
          ON P.TREAT_INFORM_ID = B.TREAT_INFORM_ID AND B.CHK_CLASSIFY='3'
    INNER JOIN C_SUPERVISE_CHKSAFTY_REC_TB          R
          ON B.REC_ID = R.REC_ID
    INNER JOIN BASE                                 RB
          ON R.BASE_REC_ID = RB.BASE_REC_ID
    WHERE P.TREAT_STATUS='6' AND P.RESTRIC_TREAT_DATE<SYSDATE
    GROUP BY RB.VLD_ENTERPRISE_ID
),
TBL5 AS
(
    SELECT RB.VLD_ENTERPRISE_ID,
           COUNT(*) AS TBALL
    FROM C_SUPERVISE_CHK_PROBLEMLIST_TB             P
    INNER JOIN C_SUPERVISE_CHK_TREATBILL_TB         B
          ON P.TREAT_INFORM_ID = B.TREAT_INFORM_ID AND B.CHK_CLASSIFY='3'
    INNER JOIN C_SUPERVISE_CHKSAFTY_REC_TB          R
          ON B.REC_ID = R.REC_ID
    INNER JOIN BASE                                 RB
          ON R.BASE_REC_ID = RB.BASE_REC_ID
    WHERE P.TREAT_STATUS<>'1' AND P.RESTRIC_TREAT_DATE<SYSDATE
    GROUP BY RB.VLD_ENTERPRISE_ID
),
TBL6 AS
(
    SELECT RB.VLD_ENTERPRISE_ID,
           COUNT(*) AS TBO
    FROM C_SUPERVISE_CHK_PROBLEMLIST_TB             P
    INNER JOIN C_SUPERVISE_CHK_TREATBILL_TB         B
          ON P.TREAT_INFORM_ID = B.TREAT_INFORM_ID AND B.CHK_CLASSIFY='3'
    INNER JOIN C_SUPERVISE_CHKSAFTY_REC_TB          R
          ON B.REC_ID = R.REC_ID
    LEFT JOIN C_SUPERVISE_CHK_TREATBACK_TB          BK
          ON P.PROBLEM_ID = BK.PROBLEM_ID AND B.TREAT_INFORM_ID = BK.TREAT_INFORM_ID
    INNER JOIN BASE                                 RB
          ON R.BASE_REC_ID = RB.BASE_REC_ID
    WHERE P.TREAT_STATUS='2' OR P.TREAT_STATUS='6' AND (CASE WHEN BK.IS_DELAY='1' THEN BK.DELAYTREATCOMLELTEDATE ELSE P.RESTRIC_TREAT_DATE END)<SYSDATE
    GROUP BY RB.VLD_ENTERPRISE_ID
)
SELECT VS.VLD_SITE_ID AS SITEID,
       VS.SITE_PIC_NAME AS ENTERPRISE_NAME,
       nvl(TBL1.NTB,0) AS ALLNOT_CORRECT,
       nvl(TBL2.TBNV,0) AS PROBLEM_NOT_VERIFIED,
       nvl(TBL3.V,0) AS PROBLEM_VERIFIED,
       nvl(TBL4.TBING,0) AS PROBLEM_ING,
       nvl(TBL6.TBO,0) AS NOT_CORRECT,
       CASE WHEN (nvl(TBL5.TBALL,0)=0 OR (nvl(TBL2.TBNV,0)+nvl(TBL3.V,0))=0) THEN 0 ELSE
       round(((nvl(TBL2.TBNV,0)+nvl(TBL3.V,0)) / (nvl(TBL5.TBALL,0))*100),1)
       END AS PERCENTAGE,
       CASE WHEN nvl(TBL5.TBALL,0)=0 OR nvl(TBL3.V,0)=0 THEN 0 ELSE
       round(((nvl(TBL3.V,0) / nvl(TBL5.TBALL,0))*100),1)
       END AS V_PERCENTAGE
FROM VLD_SITE VS
LEFT JOIN TBL1 ON VS.VLD_SITE_ID=TBL1.VLD_ENTERPRISE_ID
LEFT JOIN TBL2 ON VS.VLD_SITE_ID=TBL2.VLD_ENTERPRISE_ID
LEFT JOIN TBL3 ON VS.VLD_SITE_ID=TBL3.VLD_ENTERPRISE_ID
LEFT JOIN TBL4 ON VS.VLD_SITE_ID=TBL4.VLD_ENTERPRISE_ID
LEFT JOIN TBL5 ON VS.VLD_SITE_ID=TBL5.VLD_ENTERPRISE_ID
LEFT JOIN TBL6 ON VS.VLD_SITE_ID=TBL6.VLD_ENTERPRISE_ID
WHERE VS.ORG_STATUS = 'A' AND VS.RENDER_ORDER < 999 AND VS.VLD_ENTITY_TYPE_ID=20) )
SELECT '整改完成率' as 类别
MAX(DECODE(板块公司,'销售',整改完成率)) AS 销售,
MAX(DECODE(板块公司,'勘探',整改完成率)) AS 勘探,
MAX(DECODE(板块公司,'炼化',整改完成率)) AS 炼化,
MAX(DECODE(板块公司,'工建',整改完成率)) AS 工建,
MAX(DECODE(板块公司,'管道',整改完成率)) AS 管道,
MAX(DECODE(板块公司,'工技',整改完成率)) AS 工技,
MAX(DECODE(板块公司,'海外',整改完成率)) AS 海外,
MAX(DECODE(板块公司,'222',整改完成率)) AS "222",
MAX(DECODE(板块公司,'其他',整改完成率)) AS 其他,
MAX(DECODE(板块公司,'科研',整改完成率)) AS 科研,
MAX(DECODE(板块公司,'1231',整改完成率)) AS "1231",
MAX(DECODE(板块公司,'装备',(整改完成率)) AS 装备
FROM T
UNION ALL
SELECT '整改完成率' as 类别
MAX(DECODE(板块公司,'销售',整改完成率)) AS 销售,
MAX(DECODE(板块公司,'勘探',整改完成率)) AS 勘探,
MAX(DECODE(板块公司,'炼化',整改完成率)) AS 炼化,
MAX(DECODE(板块公司,'工建',整改完成率)) AS 工建,
MAX(DECODE(板块公司,'管道',整改完成率)) AS 管道,
MAX(DECODE(板块公司,'工技',整改完成率)) AS 工技,
MAX(DECODE(板块公司,'海外',整改完成率)) AS 海外,
MAX(DECODE(板块公司,'222',整改完成率)) AS "222",
MAX(DECODE(板块公司,'其他',整改完成率)) AS 其他,
MAX(DECODE(板块公司,'科研',整改完成率)) AS 科研,
MAX(DECODE(板块公司,'1231',整改完成率)) AS "1231",
MAX(DECODE(板块公司,'装备',整改完成率)) AS 装备
FROM T
我将查询出来的结果作为一个数据集，然后对查询出来的结果进行行列转换，但是当我执行这条SQL语句的时候提示不支持with子句，请问怎么办，标红部分是我作为数据集的SQL语句

7 个解决方案

#1

with base as --在较早的版本中，不支持在base后面定义字段名
(
.....
),
tbl1 as(
),
tbl2 as(
),
...
tbl6 as(
),
t as( --把t的定义写到后面来，别括在tbl1...6外面
......
)
select ...
from t...
......

#2

请问是这样吗：

WITH BASE(BASE_REC_ID,VLD_ENTERPRISE_ID) AS
(
    SELECT RB.BASE_REC_ID,VE.VLD_ENTERPRISE_ID
    FROM C_SUPERVISE_CHKSAFTY_RECB_TB RB
    INNER JOIN V_VLD_SITE_ENTERPRISE VE ON RB.VLD_SITE_ID = VE.VLD_SITE_ID
    INNER JOIN VLD_SITE ON RB.VLD_SITE_ID = VLD_SITE.VLD_SITE_ID
    WHERE VLD_SITE.ORG_STATUS = 'A'
),
TBL1 AS
(
    SELECT RB.VLD_ENTERPRISE_ID,
           COUNT(*) AS NTB
    FROM C_SUPERVISE_CHK_PROBLEMLIST_TB                 P
    INNER JOIN C_SUPERVISE_CHK_TREATBILL_TB             B
          ON P.TREAT_INFORM_ID = B.TREAT_INFORM_ID AND B.CHK_CLASSIFY='3'
    INNER JOIN C_SUPERVISE_CHKSAFTY_REC_TB              R
          ON B.REC_ID = R.REC_ID
    INNER JOIN BASE                                     RB
          ON R.BASE_REC_ID = RB.BASE_REC_ID
    WHERE P.TREAT_STATUS='1' OR P.TREAT_STATUS='2' AND P.RESTRIC_TREAT_DATE<SYSDATE
    GROUP BY RB.VLD_ENTERPRISE_ID
),
TBL2 AS
(
    SELECT RB.VLD_ENTERPRISE_ID,
           COUNT(*) AS TBNV
    FROM C_SUPERVISE_CHK_PROBLEMLIST_TB             P
    INNER JOIN C_SUPERVISE_CHK_TREATBILL_TB         B
          ON P.TREAT_INFORM_ID = B.TREAT_INFORM_ID AND B.CHK_CLASSIFY='3'
    INNER JOIN C_SUPERVISE_CHKSAFTY_REC_TB          R
          ON B.REC_ID = R.REC_ID
    INNER JOIN BASE                                 RB
          ON R.BASE_REC_ID = RB.BASE_REC_ID
    WHERE P.TREAT_STATUS='4' AND P.RESTRIC_TREAT_DATE<SYSDATE
    GROUP BY RB.VLD_ENTERPRISE_ID
),
TBL3 AS
(
    SELECT RB.VLD_ENTERPRISE_ID,
           COUNT(*) AS V
    FROM C_SUPERVISE_CHK_PROBLEMLIST_TB             P
    INNER JOIN C_SUPERVISE_CHK_TREATBILL_TB         B
          ON P.TREAT_INFORM_ID = B.TREAT_INFORM_ID AND B.CHK_CLASSIFY='3'
    INNER JOIN C_SUPERVISE_CHKSAFTY_REC_TB          R
          ON B.REC_ID = R.REC_ID
    INNER JOIN BASE                                 RB
          ON R.BASE_REC_ID = RB.BASE_REC_ID
    WHERE P.TREAT_STATUS='5' AND P.RESTRIC_TREAT_DATE<SYSDATE
    GROUP BY RB.VLD_ENTERPRISE_ID
),
TBL4 AS
(
    SELECT RB.VLD_ENTERPRISE_ID,
           COUNT(*) AS TBING
    FROM C_SUPERVISE_CHK_PROBLEMLIST_TB             P
    INNER JOIN C_SUPERVISE_CHK_TREATBILL_TB         B
          ON P.TREAT_INFORM_ID = B.TREAT_INFORM_ID AND B.CHK_CLASSIFY='3'
    INNER JOIN C_SUPERVISE_CHKSAFTY_REC_TB          R
          ON B.REC_ID = R.REC_ID
    INNER JOIN BASE                                 RB
          ON R.BASE_REC_ID = RB.BASE_REC_ID
    WHERE P.TREAT_STATUS='6' AND P.RESTRIC_TREAT_DATE<SYSDATE
    GROUP BY RB.VLD_ENTERPRISE_ID
),
TBL5 AS
(
    SELECT RB.VLD_ENTERPRISE_ID,
           COUNT(*) AS TBALL
    FROM C_SUPERVISE_CHK_PROBLEMLIST_TB             P
    INNER JOIN C_SUPERVISE_CHK_TREATBILL_TB         B
          ON P.TREAT_INFORM_ID = B.TREAT_INFORM_ID AND B.CHK_CLASSIFY='3'
    INNER JOIN C_SUPERVISE_CHKSAFTY_REC_TB          R
          ON B.REC_ID = R.REC_ID
    INNER JOIN BASE                                 RB
          ON R.BASE_REC_ID = RB.BASE_REC_ID
    WHERE P.TREAT_STATUS<>'1' AND P.RESTRIC_TREAT_DATE<SYSDATE
    GROUP BY RB.VLD_ENTERPRISE_ID
),
TBL6 AS
(
    SELECT RB.VLD_ENTERPRISE_ID,
           COUNT(*) AS TBO
    FROM C_SUPERVISE_CHK_PROBLEMLIST_TB             P
    INNER JOIN C_SUPERVISE_CHK_TREATBILL_TB         B
          ON P.TREAT_INFORM_ID = B.TREAT_INFORM_ID AND B.CHK_CLASSIFY='3'
    INNER JOIN C_SUPERVISE_CHKSAFTY_REC_TB          R
          ON B.REC_ID = R.REC_ID
    LEFT JOIN C_SUPERVISE_CHK_TREATBACK_TB          BK
          ON P.PROBLEM_ID = BK.PROBLEM_ID AND B.TREAT_INFORM_ID = BK.TREAT_INFORM_ID
    INNER JOIN BASE                                 RB
          ON R.BASE_REC_ID = RB.BASE_REC_ID
    WHERE P.TREAT_STATUS='2' OR P.TREAT_STATUS='6' AND (CASE WHEN BK.IS_DELAY='1' THEN BK.DELAYTREATCOMLELTEDATE ELSE P.RESTRIC_TREAT_DATE END)<SYSDATE
    GROUP BY RB.VLD_ENTERPRISE_ID
),
T  AS (SELECT VS.VLD_SITE_ID AS SITEID,
       VS.SITE_PIC_NAME AS ENTERPRISE_NAME,
       nvl(TBL1.NTB,0) AS ALLNOT_CORRECT,
       nvl(TBL2.TBNV,0) AS PROBLEM_NOT_VERIFIED,
       nvl(TBL3.V,0) AS PROBLEM_VERIFIED,
       nvl(TBL4.TBING,0) AS PROBLEM_ING,
       nvl(TBL6.TBO,0) AS NOT_CORRECT,
       CASE WHEN (nvl(TBL5.TBALL,0)=0 OR (nvl(TBL2.TBNV,0)+nvl(TBL3.V,0))=0) THEN 0 ELSE
       round(((nvl(TBL2.TBNV,0)+nvl(TBL3.V,0)) / (nvl(TBL5.TBALL,0))*100),1)
       END AS PERCENTAGE,
       CASE WHEN nvl(TBL5.TBALL,0)=0 OR nvl(TBL3.V,0)=0 THEN 0 ELSE
       round(((nvl(TBL3.V,0) / nvl(TBL5.TBALL,0))*100),1)
       END AS V_PERCENTAGE
FROM VLD_SITE VS
LEFT JOIN TBL1 ON VS.VLD_SITE_ID=TBL1.VLD_ENTERPRISE_ID
LEFT JOIN TBL2 ON VS.VLD_SITE_ID=TBL2.VLD_ENTERPRISE_ID
LEFT JOIN TBL3 ON VS.VLD_SITE_ID=TBL3.VLD_ENTERPRISE_ID
LEFT JOIN TBL4 ON VS.VLD_SITE_ID=TBL4.VLD_ENTERPRISE_ID
LEFT JOIN TBL5 ON VS.VLD_SITE_ID=TBL5.VLD_ENTERPRISE_ID
LEFT JOIN TBL6 ON VS.VLD_SITE_ID=TBL6.VLD_ENTERPRISE_ID
WHERE VS.ORG_STATUS = 'A' AND VS.RENDER_ORDER < 999 AND VS.VLD_ENTITY_TYPE_ID=20)
SELECT ENTERPRISE_NAME AS "板块公司",PERCENTAGE AS "整改完成率",V_PERCENTAGE AS "验证率"  FROM T

SELECT '整改完成率' as 类别
MAX(DECODE(板块公司,'销售',整改完成率)) AS 销售,
MAX(DECODE(板块公司,'勘探',整改完成率)) AS 勘探,
MAX(DECODE(板块公司,'炼化',整改完成率)) AS 炼化,
MAX(DECODE(板块公司,'工建',整改完成率)) AS 工建,
MAX(DECODE(板块公司,'管道',整改完成率)) AS 管道,
MAX(DECODE(板块公司,'工技',整改完成率)) AS 工技,
MAX(DECODE(板块公司,'海外',整改完成率)) AS 海外,
MAX(DECODE(板块公司,'222',整改完成率)) AS "222",
MAX(DECODE(板块公司,'其他',整改完成率)) AS 其他,
MAX(DECODE(板块公司,'科研',整改完成率)) AS 科研,
MAX(DECODE(板块公司,'1231',整改完成率)) AS "1231",
MAX(DECODE(板块公司,'装备',(整改完成率)) AS 装备
FROM T
UNION ALL
SELECT '整改完成率' as 类别
MAX(DECODE(板块公司,'销售',整改完成率)) AS 销售,
MAX(DECODE(板块公司,'勘探',整改完成率)) AS 勘探,
MAX(DECODE(板块公司,'炼化',整改完成率)) AS 炼化,
MAX(DECODE(板块公司,'工建',整改完成率)) AS 工建,
MAX(DECODE(板块公司,'管道',整改完成率)) AS 管道,
MAX(DECODE(板块公司,'工技',整改完成率)) AS 工技,
MAX(DECODE(板块公司,'海外',整改完成率)) AS 海外,
MAX(DECODE(板块公司,'222',整改完成率)) AS "222",
MAX(DECODE(板块公司,'其他',整改完成率)) AS 其他,
MAX(DECODE(板块公司,'科研',整改完成率)) AS 科研,
MAX(DECODE(板块公司,'1231',整改完成率)) AS "1231",
MAX(DECODE(板块公司,'装备',整改完成率)) AS 装备
FROM T

那这样写的话那我后面的行列转换应该怎么写，请指点一下，谢谢

#3

这是我查询出来结果：
Oracle中不支持使用with子句

这是需要的结果：

请问我使用上述查询语句查询出来结果之后怎么样进行行列转换，谢谢

#4

一样的啊，把SELECT ENTERPRISE_NAME AS "板块公司",PERCENTAGE AS "整改完成率",V_PERCENTAGE AS "验证率" FROM T
这句换成你那下面的行列转换的语句就可以了，对应的字段名稍微调整下

#5

这是我查询出来的结果：
Oracle中不支持使用with子句

这是我写的行列传换：
SELECT 'PERCENTAGE' as 类别
MAX(DECODE(ENTERPRISE_NAME,'销售',PERCENTAGE)) AS 销售,
MAX(DECODE(ENTERPRISE_NAME,'勘探',PERCENTAGE)) AS 勘探,
MAX(DECODE(ENTERPRISE_NAME,'炼化',PERCENTAGE)) AS 炼化,
MAX(DECODE(ENTERPRISE_NAME,'工建',PERCENTAGE)) AS 工建,
MAX(DECODE(ENTERPRISE_NAME,'管道',PERCENTAGE)) AS 管道,
MAX(DECODE(ENTERPRISE_NAME,'工技',PERCENTAGE)) AS 工技,
MAX(DECODE(ENTERPRISE_NAME,'海外',PERCENTAGE)) AS 海外,
MAX(DECODE(ENTERPRISE_NAME,'222',PERCENTAGE)) AS "222",
MAX(DECODE(ENTERPRISE_NAME,'其他',PERCENTAGE)) AS 其他,
MAX(DECODE(ENTERPRISE_NAME,'科研',PERCENTAGE)) AS 科研,
MAX(DECODE(ENTERPRISE_NAME,'1231',PERCENTAGE)) AS "1231",
MAX(DECODE(ENTERPRISE_NAME,'装备',(PERCENTAGE)) AS 装备
FROM T
但是为什么说找不到from关键字呢

#6

第一行
SELECT 'PERCENTAGE' as 类别
最后少个逗号 Oracle中不支持使用with子句

#7

，额，谢谢你们了

#1

#2

#3