“using”子句在postgresql中不起作用？

e0bqpujr  于 2021-07-29  发布在  Java
关注(0)|答案(0)|浏览(231)

正在将oracle迁移到postgres 12，但不知道为什么在“using”子句中出现错误。在查询片段下面共享：

(SELECT
  'NPOS_PPV_SUCC' AS TOTAL_TYPE,
  DONOR AS RNO,
  SENDER AS DNO,
  AREA_CODE AS AREA_CODE,
  COUNT(*) AS TOTAL
FROM
  (
    SELECT
      DONOR,
      SENDER,
      AREA_CODE
    FROM
      TBL_PORTORDER PORT,
      TBL_PORTSUBSCRIBER SUB
    WHERE
      PORT.RID = SUB.NPO_RID
      AND NPO_TYPE = 'TER'
      AND PORT.CREATED_ON BETWEEN ':START_TIME'
      AND ':END_TIME'
      AND NPOS_PPV_SUCC_TIME IS NOT NULL
    UNION ALL
    SELECT
      DONOR,
      SENDER,
      AREA_CODE
    FROM
      TBL_PORTORDER_HISTORY PORT,
      TBL_PORTSUBSCRIBER_HISTORY SUB
    WHERE
      PORT.RID = SUB.NPO_RID
      AND NPO_TYPE = 'TER'
      AND PORT.CREATED_ON BETWEEN ':START_TIME'
      AND ':END_TIME'
      AND NPOS_PPV_SUCC_TIME IS NOT NULL
      AND PORT.PART_KEY IN (15, 16)
      AND SUB.PART_KEY IN (15, 16)
  ) as test1
GROUP BY
  TOTAL_TYPE,
  DONOR,
  SENDER,
  AREA_CODE)
UNION
(SELECT
  'NPOS_PPV_FAIL' AS TOTAL_TYPE,
  DONOR AS RNO,
  SENDER AS DNO,
  AREA_CODE AS AREA_CODE,
  COUNT(*) AS TOTAL
FROM
  (
    SELECT
      DONOR,
      SENDER,
      AREA_CODE
    FROM
      TBL_PORTORDER PORT,
      TBL_PORTSUBSCRIBER SUB
    WHERE
      PORT.RID = SUB.NPO_RID
      AND NPO_TYPE = 'TER'
      AND NPO_SUBMIT_TIME BETWEEN ':START_TIME'
      AND ':END_TIME'
      AND NPOS_PPV_FAIL_TIME IS NOT NULL
    UNION ALL
    SELECT
      DONOR,
      SENDER,
      AREA_CODE
    FROM
      TBL_PORTORDER_HISTORY PORT,
      TBL_PORTSUBSCRIBER_HISTORY SUB
    WHERE
      PORT.RID = SUB.NPO_RID
      AND NPO_TYPE = 'TER'
      AND NPO_SUBMIT_TIME BETWEEN ':START_TIME'
      AND ':END_TIME'
      AND NPOS_PPV_FAIL_TIME IS NOT NULL
      AND PORT.PART_KEY IN (15, 16)
      AND SUB.PART_KEY IN (15, 16)
  ) as test
GROUP BY
  TOTAL_TYPE,
  DONOR,
  SENDER,
  AREA_CODE)
UNION
(SELECT
  'NPOS_APP_SUCC' AS TOTAL_TYPE,
  DONOR AS RNO,
  SENDER AS DNO,
  AREA_CODE AS AREA_CODE,
  COUNT(*) AS TOTAL
FROM
  (
    SELECT
      DONOR,
      SENDER,
      AREA_CODE
    FROM
      TBL_PORTORDER PORT,
      TBL_PORTSUBSCRIBER SUB
    WHERE
      PORT.RID = SUB.NPO_RID
      AND NPO_TYPE = 'TER'
      AND PORT.CREATED_ON BETWEEN ':START_TIME'
      AND ':END_TIME'
      AND NPOS_ANSWER_TIME IS NOT NULL
      AND NOT (
        IS_START_FROM_NPOS = '2'
        AND NPO_STATUS = 'F_REJ'
      )
    UNION ALL
    SELECT
      DONOR,
      SENDER,
      AREA_CODE
    FROM
      TBL_PORTORDER_HISTORY PORT,
      TBL_PORTSUBSCRIBER_HISTORY SUB
    WHERE
      PORT.RID = SUB.NPO_RID
      AND NPO_TYPE = 'TER'
      AND PORT.CREATED_ON BETWEEN ':START_TIME'
      AND ':END_TIME'
      AND NPOS_ANSWER_TIME IS NOT NULL
      AND NOT (
        IS_START_FROM_NPOS = '2'
        AND NPO_STATUS = 'F_REJ'
      )
      AND PORT.PART_KEY IN (15, 16)
      AND SUB.PART_KEY IN (15, 16)
  ) as test2
GROUP BY
  TOTAL_TYPE,
  DONOR,
  SENDER,
  AREA_CODE)
UNION
(SELECT
  'NPOT_NPOS_TRIG' AS TOTAL_TYPE,
  RECIPIENT AS RNO,
  DONOR AS DNO,
  AREA_CODE AS AREA_CODE,
  COUNT(*) AS TOTAL
FROM
  (
    SELECT
      DONOR,
      RECIPIENT,
      AREA_CODE
    FROM
      TBL_PORTORDER PORT,
      TBL_PORTSUBSCRIBER SUB
    WHERE
      PORT.RID = SUB.NPO_RID
      AND NPO_TYPE = 'TER'
      AND NPO_SUBMIT_TIME BETWEEN ':START_TIME'
      AND ':END_TIME'
      AND ORD_PPV_SUCC_TIME IS NOT NULL
      AND IS_START_FROM_NPOS = '1'
    UNION ALL
    SELECT
      DONOR,
      RECIPIENT,
      AREA_CODE
    FROM
      TBL_PORTORDER_HISTORY PORT,
      TBL_PORTSUBSCRIBER_HISTORY SUB
    WHERE
      PORT.RID = SUB.NPO_RID
      AND NPO_TYPE = 'TER'
      AND NPO_SUBMIT_TIME BETWEEN ':START_TIME'
      AND ':END_TIME'
      AND ORD_PPV_SUCC_TIME IS NOT NULL
      AND IS_START_FROM_NPOS = '1'
      AND PORT.PART_KEY IN (15, 16)
      AND SUB.PART_KEY IN (15, 16)
  ) as test3
GROUP BY
  TOTAL_TYPE,
  RECIPIENT,
  DONOR,
  AREA_CODE)
UNION ALL
(SELECT
  'NPOT_NPOS_TRIG' AS TOTAL_TYPE,
  RECIPIENT AS RNO,
  DONOR AS DNO,
  AREA_CODE AS AREA_CODE,
  COUNT(*) AS TOTAL
FROM
  (
    SELECT
      DONOR,
      RECIPIENT,
      AREA_CODE
    FROM
      TBL_PORTORDER PORT,
      TBL_PORTSUBSCRIBER SUB
    WHERE
      PORT.RID = SUB.NPO_RID
      AND NPO_TYPE = 'TER'
      AND PORT.UPDATED_ON BETWEEN ':START_TIME'
      AND ':END_TIME'
      AND IS_START_FROM_NPOS = '2'
      AND NPO_STATUS IN ('F_CAN')
    UNION ALL
    SELECT
      DONOR,
      RECIPIENT,
      AREA_CODE
    FROM
      TBL_PORTORDER_HISTORY PORT,
      TBL_PORTSUBSCRIBER_HISTORY SUB
    WHERE
      PORT.RID = SUB.NPO_RID
      AND NPO_TYPE = 'TER'
      AND PORT.UPDATED_ON BETWEEN ':START_TIME'
      AND ':END_TIME'
      AND IS_START_FROM_NPOS = '2'
      AND NPO_STATUS IN ('F_CAN')
      AND PORT.PART_KEY IN (15, 16)
      AND SUB.PART_KEY IN (15, 16)
  ) as test4
GROUP BY
  TOTAL_TYPE,
  RECIPIENT,
  DONOR,
  AREA_CODE)
UNION
(SELECT
  'NPOT_PPV_SUCC' AS TOTAL_TYPE,
  RECIPIENT AS RNO,
  DONOR AS DNO,
  AREA_CODE AS AREA_CODE,
  COUNT(*) AS TOTAL
FROM
  (
    SELECT
      DONOR,
      RECIPIENT,
      AREA_CODE
    FROM
      TBL_PORTORDER PORT,
      TBL_PORTSUBSCRIBER SUB
    WHERE
      PORT.RID = SUB.NPO_RID
      AND NPO_TYPE = 'TER'
      AND NPO_SUBMIT_TIME BETWEEN ':START_TIME'
      AND ':END_TIME'
      AND ORD_PPV_SUCC_TIME IS NOT NULL
    UNION ALL
    SELECT
      DONOR,
      RECIPIENT,
      AREA_CODE
    FROM
      TBL_PORTORDER_HISTORY PORT,
      TBL_PORTSUBSCRIBER_HISTORY SUB
    WHERE
      PORT.RID = SUB.NPO_RID
      AND NPO_TYPE = 'TER'
      AND NPO_SUBMIT_TIME BETWEEN ':START_TIME'
      AND ':END_TIME'
      AND ORD_PPV_SUCC_TIME IS NOT NULL
      AND PORT.PART_KEY IN (15, 16)
      AND SUB.PART_KEY IN (15, 16)
  ) as test5
GROUP BY
  TOTAL_TYPE,
  RECIPIENT,
  DONOR,
  AREA_CODE)
UNION ALL
(SELECT
  'NPOT_PPV_FAIL' AS TOTAL_TYPE,
  RECIPIENT AS RNO,
  DONOR AS DNO,
  AREA_CODE AS AREA_CODE,
  COUNT(*) AS TOTAL
FROM
  (
    SELECT
      DONOR,
      RECIPIENT,
      AREA_CODE
    FROM
      TBL_PORTORDER PORT,
      TBL_PORTSUBSCRIBER SUB
    WHERE
      PORT.RID = SUB.NPO_RID
      AND NPO_TYPE = 'TER'
      AND NPO_SUBMIT_TIME BETWEEN ':START_TIME'
      AND ':END_TIME'
      AND ORD_PPV_FAIL_TIME IS NOT NULL
    UNION ALL
    SELECT
      DONOR,
      RECIPIENT,
      AREA_CODE
    FROM
      TBL_PORTORDER_HISTORY PORT,
      TBL_PORTSUBSCRIBER_HISTORY SUB
    WHERE
      PORT.RID = SUB.NPO_RID
      AND NPO_TYPE = 'TER'
      AND NPO_SUBMIT_TIME BETWEEN ':START_TIME'
      AND ':END_TIME'
      AND ORD_PPV_FAIL_TIME IS NOT NULL
      AND PORT.PART_KEY IN (15, 16)
      AND SUB.PART_KEY IN (15, 16)
  ) as test7
GROUP BY
  TOTAL_TYPE,
  RECIPIENT,
  DONOR,
  AREA_CODE)
UNION
(SELECT
  'NPOT_APP_SUCC' AS TOTAL_TYPE,
  RECIPIENT AS RNO,
  DONOR AS DNO,
  AREA_CODE AS AREA_CODE,
  COUNT(*) AS TOTAL
FROM
  (
    SELECT
      DONOR,
      RECIPIENT,
      AREA_CODE
    FROM
      TBL_PORTORDER PORT,
      TBL_PORTSUBSCRIBER SUB
    WHERE
      PORT.RID = SUB.NPO_RID
      AND NPO_TYPE = 'TER'
      AND (
        '2' = '2'
        OR ORG_TRANSFER_TIME BETWEEN ':START_TIME'
        AND ':END_TIME'
      )
      AND (
        '2' = '1'
        OR NPO_SUBMIT_TIME BETWEEN ':START_TIME'
        AND ':END_TIME'
      )
      AND ORD_APPROVED_TIME IS NOT NULL
    UNION ALL
    SELECT
      DONOR,
      RECIPIENT,
      AREA_CODE
    FROM
      TBL_PORTORDER_HISTORY PORT,
      TBL_PORTSUBSCRIBER_HISTORY SUB
    WHERE
      PORT.RID = SUB.NPO_RID
      AND NPO_TYPE = 'TER'
      AND (
        '2' = '2'
        OR ORG_TRANSFER_TIME BETWEEN ':START_TIME'
        AND ':END_TIME'
      )
      AND (
        '2' = '1'
        OR NPO_SUBMIT_TIME BETWEEN ':START_TIME'
        AND ':END_TIME'
      )
      AND ORD_APPROVED_TIME IS NOT NULL
      AND PORT.PART_KEY IN (15, 16)
      AND SUB.PART_KEY IN (15, 16)
  ) as test6
GROUP BY
  TOTAL_TYPE,
  RECIPIENT,
  DONOR,
  AREA_CODE)
UNION
(SELECT
  'NPOT_BIP' AS TOTAL_TYPE,
  RECIPIENT AS RNO,
  DONOR AS DNO,
  AREA_CODE AS AREA_CODE,
  COUNT(*) AS TOTAL
FROM
  (
    SELECT
      DONOR,
      RECIPIENT,
      AREA_CODE
    FROM
      TBL_PORTORDER PORT,
      TBL_PORTSUBSCRIBER SUB
    WHERE
      PORT.RID = SUB.NPO_RID
      AND NPO_TYPE = 'TER'
      AND (
        '2' = '2'
        OR ORG_TRANSFER_TIME BETWEEN ':START_TIME'
        AND ':END_TIME'
      )
      AND (
        '2' = '1'
        OR NPO_SUBMIT_TIME BETWEEN ':START_TIME'
        AND ':END_TIME'
      )
      AND NPO_STATUS IN ('BIP')
    UNION ALL
    SELECT
      DONOR,
      RECIPIENT,
      AREA_CODE
    FROM
      TBL_PORTORDER_HISTORY PORT,
      TBL_PORTSUBSCRIBER_HISTORY SUB
    WHERE
      PORT.RID = SUB.NPO_RID
      AND NPO_TYPE = 'TER'
      AND (
        '2' = '2'
        OR ORG_TRANSFER_TIME BETWEEN ':START_TIME'
        AND ':END_TIME'
      )
      AND (
        '2' = '1'
        OR NPO_SUBMIT_TIME BETWEEN ':START_TIME'
        AND ':END_TIME'
      )
      AND NPO_STATUS IN ('BIP')
      AND PORT.PART_KEY IN (15, 16)
      AND SUB.PART_KEY IN (15, 16)
  ) as test8
GROUP BY
  TOTAL_TYPE,
  RECIPIENT,
  DONOR,
  AREA_CODE)
UNION ALL
(SELECT
  'NPOT_SUCC' AS TOTAL_TYPE,
  RECIPIENT AS RNO,
  DONOR AS DNO,
  AREA_CODE AS AREA_CODE,
  COUNT(*) AS TOTAL
FROM
  (
    SELECT
      DONOR,
      RECIPIENT,
      AREA_CODE
    FROM
      TBL_PORTORDER PORT,
      TBL_PORTSUBSCRIBER SUB
    WHERE
      PORT.RID = SUB.NPO_RID
      AND NPO_TYPE = 'TER'
      AND (
        '2' = '2'
        OR ORG_TRANSFER_TIME BETWEEN ':START_TIME'
        AND ':END_TIME'
      )
      AND (
        '2' = '1'
        OR NPO_SUBMIT_TIME BETWEEN ':START_TIME'
        AND ':END_TIME'
      )
      AND NPO_STATUS IN ('SUCC')
    UNION ALL
    SELECT
      DONOR,
      RECIPIENT,
      AREA_CODE
    FROM
      TBL_PORTORDER_HISTORY PORT,
      TBL_PORTSUBSCRIBER_HISTORY SUB
    WHERE
      PORT.RID = SUB.NPO_RID
      AND NPO_TYPE = 'TER'
      AND (
        '2' = '2'
        OR ORG_TRANSFER_TIME BETWEEN ':START_TIME'
        AND ':END_TIME'
      )
      AND (
        '2' = '1'
        OR NPO_SUBMIT_TIME BETWEEN ':START_TIME'
        AND ':END_TIME'
      )
      AND NPO_STATUS IN ('SUCC')
      AND PORT.PART_KEY IN (15, 16)
      AND SUB.PART_KEY IN (15, 16)
  ) as test9
GROUP BY
  TOTAL_TYPE,
  RECIPIENT,
  DONOR,
  AREA_CODE)
UNION
(SELECT
  'NPOT_SUCCP' AS TOTAL_TYPE,
  RECIPIENT AS RNO,
  DONOR AS DNO,
  AREA_CODE AS AREA_CODE,
  COUNT(*) AS TOTAL
FROM
  (
    SELECT
      DONOR,
      RECIPIENT,
      AREA_CODE
    FROM
      TBL_PORTORDER PORT,
      TBL_PORTSUBSCRIBER SUB
    WHERE
      PORT.RID = SUB.NPO_RID
      AND NPO_TYPE = 'TER'
      AND (
        '2' = '2'
        OR ORG_TRANSFER_TIME BETWEEN ':START_TIME'
        AND ':END_TIME'
      )
      AND (
        '2' = '1'
        OR NPO_SUBMIT_TIME BETWEEN ':START_TIME'
        AND ':END_TIME'
      )
      AND NPO_STATUS IN ('SUCCP')
    UNION ALL
    SELECT
      DONOR,
      RECIPIENT,
      AREA_CODE
    FROM
      TBL_PORTORDER_HISTORY PORT,
      TBL_PORTSUBSCRIBER_HISTORY SUB
    WHERE
      PORT.RID = SUB.NPO_RID
      AND NPO_TYPE = 'TER'
      AND (
        '2' = '2'
        OR ORG_TRANSFER_TIME BETWEEN ':START_TIME'
        AND ':END_TIME'
      )
      AND (
        '2' = '1'
        OR NPO_SUBMIT_TIME BETWEEN ':START_TIME'
        AND ':END_TIME'
      )
      AND NPO_STATUS IN ('SUCCP')
      AND PORT.PART_KEY IN (15, 16)
      AND SUB.PART_KEY IN (15, 16)
  ) as test7
GROUP BY
  TOTAL_TYPE,
  RECIPIENT,
  DONOR,
  AREA_CODE)
USING
        l_START_TIME, l_END_TIME, l_START_TIME, l_END_TIME,
        l_START_TIME, l_END_TIME, l_START_TIME, l_END_TIME,
        l_START_TIME, l_END_TIME, l_START_TIME, l_END_TIME,
        l_START_TIME, l_END_TIME, l_START_TIME, l_END_TIME,
        l_START_TIME, l_END_TIME, l_START_TIME, l_END_TIME,
        l_START_TIME, l_END_TIME, l_START_TIME, l_END_TIME,
        l_START_TIME, l_END_TIME, l_START_TIME, l_END_TIME,
        l_START_TIME, l_END_TIME, l_START_TIME, l_END_TIME,
        l_START_TIME, l_END_TIME, l_START_TIME, l_END_TIME,
        l_START_TIME, l_END_TIME, l_START_TIME, l_END_TIME,
        l_START_TIME, l_END_TIME, l_START_TIME, l_END_TIME,
        l_START_TIME, l_END_TIME, l_START_TIME, l_END_TIME,
        l_START_TIME, l_END_TIME, l_START_TIME, l_END_TIME,
        l_START_TIME, l_END_TIME, l_START_TIME, l_END_TIME,
        l_START_TIME, l_END_TIME, l_START_TIME, l_END_TIME
        ;

获取错误：

ERROR:  syntax error at or near "USING"
LINE 44:   USING TOTAL_TYPE

有人能帮我吗？这两天我一直在坚持。如果不使用using子句，查询就可以正常工作，但是使用using，我会得到一个语法错误。

sqlpostgresql

暂无答案！

目前还没有任何答案，快来回答吧！

我来回答

相关问题

    查看更多

    热门标签

    更多
    JavaquerypythonNode开发语言requestUtil数据库Table后端算法LoggerMessageElementParser

    最新问答

    更多
    • 蜀ICP备13028337号-1 大数据知识库 https://www.saoniuhuo.com © All rights reserved
    • 本站内容来源互联网,如果侵犯您的权益请联系我们删除, 联系方式：448109455@qq.com