SQL Select syntax error
Posted
by Odette
on Stack Overflow
See other posts from Stack Overflow
or by Odette
Published on 2010-04-20T00:12:10Z
Indexed on
2010/04/20
0:13 UTC
Read the original article
Hit count: 495
hi guys thanks for your help yesterday. I am now trying to incorporate the query from yesterday into an existing query so I can show the highest itemcode's reporting group in the existing query..but I have a syntax error somewhere at my Select statement. ERROR: Keyword SELECT not expected.
I have tried putting brackets at every possible place but still no go..can you please help? (ps-this whole query has been giving me nightmares!)
WITH CALC1 AS (SELECT OTQUOT, OTIT01 AS ITEMS, ROUND(OQCQ01 * OVRC01,2) AS COST
FROM
WHERE OTIT01 <> ''
UNION ALL
SELECT OTQUOT, OTIT02 AS ITEMS, ROUND(OQCQ02 * OVRC02,2) AS COST
FROM
WHERE OTIT02 <> ''
UNION ALL
SELECT OTQUOT, OTIT03 AS ITEMS, ROUND(OQCQ03 * OVRC03,2) AS COST
FROM
WHERE OTIT03 <> ''
UNION ALL
SELECT OTQUOT, OTIT04 AS ITEMS, ROUND(OQCQ04 * OVRC04,2) AS COST
FROM
WHERE OTIT04 <> ''
UNION ALL
SELECT OTQUOT, OTIT05 AS ITEMS, ROUND(OQCQ05 * OVRC05,2) AS COST
FROM
WHERE OTIT05 <> ''
UNION ALL
SELECT OTQUOT, OTIT06 AS ITEMS, ROUND(OQCQ06 * OVRC06,2) AS COST
FROM
WHERE OTIT06 <> ''
UNION ALL
SELECT OTQUOT, OTIT07 AS ITEMS, ROUND(OQCQ07 * OVRC07,2) AS COST
FROM
WHERE OTIT07 <> ''
UNION ALL
SELECT OTQUOT, OTIT08 AS ITEMS, ROUND(OQCQ08 * OVRC08,2) AS COST
FROM
WHERE OTIT08 <> ''
UNION ALL
SELECT OTQUOT, OTIT09 AS ITEMS, ROUND(OQCQ09 * OVRC09,2) AS COST
FROM
WHERE OTIT09 <> ''
UNION ALL
SELECT OTQUOT, OTIT10 AS ITEMS, ROUND(OQCQ10 * OVRC10,2) AS COST
FROM
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 @[email protected] 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
@[email protected] 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
© Stack Overflow or respective owner
