mysql - SQL 选择语法错误

Question

谢谢你昨天的帮助。我现在正尝试将昨天的查询合并到现有查询中，以便我可以在现有查询中显示最高项目代码的报告组。但是我的 Select 语句中某处存在语法错误。错误:关键字 SELECT 不是预期的。

我试过在每个可能的地方都加上括号，但还是不行..你能帮忙吗？ (ps-整个查询让我做噩梦!)

WITH CALC1 AS (SELECT OTQUOT, OTIT01 AS ITEMS, ROUND(OQCQ01 * OVRC01,2) AS COST
FROM  @LIB@.RTQOTA
WHERE OTIT01 <> ''

UNION ALL

SELECT OTQUOT, OTIT02 AS ITEMS, ROUND(OQCQ02 * OVRC02,2) AS COST
FROM  @LIB@.RTQOTA
WHERE OTIT02 <> ''

UNION ALL

SELECT OTQUOT, OTIT03 AS ITEMS, ROUND(OQCQ03 * OVRC03,2) AS COST
FROM @LIB@.RTQOTA
WHERE OTIT03 <> ''

UNION ALL

SELECT OTQUOT, OTIT04 AS ITEMS, ROUND(OQCQ04 * OVRC04,2) AS COST
FROM @LIB@.RTQOTA
WHERE OTIT04 <> ''

UNION ALL

SELECT OTQUOT, OTIT05 AS ITEMS, ROUND(OQCQ05 * OVRC05,2) AS COST
FROM @LIB@.RTQOTA
WHERE OTIT05 <> ''

UNION ALL

SELECT OTQUOT, OTIT06 AS ITEMS, ROUND(OQCQ06 * OVRC06,2) AS COST
FROM @LIB@.RTQOTA
WHERE OTIT06 <> ''

UNION ALL

SELECT OTQUOT, OTIT07 AS ITEMS, ROUND(OQCQ07 * OVRC07,2) AS COST
FROM @LIB@.RTQOTA
WHERE OTIT07 <> ''

UNION ALL

SELECT OTQUOT, OTIT08 AS ITEMS, ROUND(OQCQ08 * OVRC08,2) AS COST
FROM @LIB@.RTQOTA
WHERE OTIT08 <> ''

UNION ALL

SELECT OTQUOT, OTIT09 AS ITEMS, ROUND(OQCQ09 * OVRC09,2) AS COST
FROM @LIB@.RTQOTA
WHERE OTIT09 <> ''

UNION ALL

SELECT OTQUOT, OTIT10 AS ITEMS, ROUND(OQCQ10 * OVRC10,2) AS COST
FROM @LIB@.RTQOTA
WHERE OTIT10 <> ''


)

(SELECT OTQUOT, DESC

FROM ( 
  SELECT OTQUOT, ITEMS, B.IXRPGP AS GROUP, C.OTRDSC AS DESC, COST, ROW_NUMBER() OVER 

(ORDER BY COST DESC) AS RN 

  FROM CALC1 AS A INNER JOIN @LIB@.ITMCON AS B ON (A.ITEMS = B.IKITMC) INNER JOIN 

DATAGRP.GDSGRP AS C ON (B.IXRPGP = C.OKRPGP)


) T 

WHERE T.RN >= 1)

SELECT 

A.OKPBRN,

A.OCAREA,

A.OTCCDE,

A.OTCNAM,

A.OTSMAN,

A.OKPBRN||A.OAPNUM AS OTQUOT,

A.OTONUM,

A.OTCAD1,

A.OTCAD2,

A.OTCAD3,

A.OTPCDE,

A.OTDEL1,

A.OTDEL2,

A.OTDEL3,

CHAR(DATE(CASE WHEN SUBSTR(A.ODOQDT,5,4) = '0000' THEN '0001' ELSE SUBSTR(A.ODOQDT,5,4) 

END ||'-'||

CASE WHEN SUBSTR(A.ODOQDT,4,2) = '00' THEN '01' ELSE SUBSTR(A.ODOQDT,3,2) END ||'-'||

CASE WHEN SUBSTR(A.ODOQDT,1,2) = '00' THEN '01' ELSE SUBSTR(A.ODOQDT,1,2) END), ISO) AS 

ODOQDT_CCYYMMDD,

CHAR(DATE(CASE WHEN SUBSTR(A.ODDELD,7,2) = '' THEN '0001' ELSE '20'||SUBSTR(A.ODDELD,7,2) 

END ||'-'||

CASE WHEN SUBSTR(A.ODDELD,4,2) = '' THEN '01' ELSE SUBSTR(A.ODDELD,4,2) END ||'-'||

CASE WHEN SUBSTR(A.ODDELD,1,2) = '' THEN '01' ELSE SUBSTR(A.ODDELD,1,2) END), ISO) AS 

ODDELD_CCYYMMDD,

B.DESC,

A.OVQTVL

FROM 

@LIB@.RTQCTL AS A INNER JOIN CALC1 AS B ON (A.OKPBRN||A.OAPNUM = B.OTQUOT)

WHERE 

A.OKPBRN = '@OKPBRN@'

AND A.OTCCDE NOT LIKE '*DEP%'

AND CHAR(DATE(CASE WHEN SUBSTR(A.ODOQDT,5,4) = '0000' THEN '0001' ELSE SUBSTR

(A.ODOQDT,5,4) END ||'-'||

CASE WHEN SUBSTR(A.ODOQDT,4,2) = '00' THEN '01' ELSE SUBSTR(A.ODOQDT,3,2) END ||'-'||

CASE WHEN SUBSTR(A.ODOQDT,1,2) = '00' THEN '01' ELSE SUBSTR(A.ODOQDT,1,2) END), ISO) >=

CHAR(CURDATE() - 3 MONTH, ISO)

AND A.OCQF01 = '0'
AND A.OCQF02 = '0'
AND A.OCQF04 = '0'
AND A.OCQF05 = '0'
AND A.OCQF06 = '0'
AND A.OCQF07 = '0'
AND A.OCQF08 = '0'
AND A.OCQF09 = '0'
AND A.OCQF10 = '1'
AND A.OTCGRP LIKE 'S/%'

ORDER BY 
A.OTSMAN ASC,
A.OVQTVL DESC,
CHAR(DATE(CASE WHEN SUBSTR(A.ODDELD,7,2) = '' THEN '0001' ELSE '20'||SUBSTR(A.ODDELD,7,2) END ||'-'||
CASE WHEN SUBSTR(A.ODDELD,4,2) = '' THEN '01' ELSE SUBSTR(A.ODDELD,4,2) END ||'-'||
CASE WHEN SUBSTR(A.ODDELD,1,2) = '' THEN '01' ELSE SUBSTR(A.ODDELD,1,2) END),ISO) ASC

Answer 1

使用 UNION 时，将您的 SELECT 语句括在括号中。

例如

(SELECT * FROM foo)
UNION ALL
(SELECT * FROM bar)

编辑:仔细检查这个异常的查询后，其中可能还有许多其他错误。我建议撕掉它的一部分，并为我们提供仍然会导致语法错误的尽可能小的示例。