SQL Select syntax error

Question

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  @[email protected]
WHERE OTIT01 <> ''

UNION ALL

SELECT OTQUOT, OTIT02 AS ITEMS, ROUND(OQCQ02 * OVRC02,2) AS COST
FROM  @[email protected]
WHERE OTIT02 <> ''

UNION ALL

SELECT OTQUOT, OTIT03 AS ITEMS, ROUND(OQCQ03 * OVRC03,2) AS COST
FROM @[email protected]
WHERE OTIT03 <> ''

UNION ALL

SELECT OTQUOT, OTIT04 AS ITEMS, ROUND(OQCQ04 * OVRC04,2) AS COST
FROM @[email protected]
WHERE OTIT04 <> ''

UNION ALL

SELECT OTQUOT, OTIT05 AS ITEMS, ROUND(OQCQ05 * OVRC05,2) AS COST
FROM @[email protected]
WHERE OTIT05 <> ''

UNION ALL

SELECT OTQUOT, OTIT06 AS ITEMS, ROUND(OQCQ06 * OVRC06,2) AS COST
FROM @[email protected]
WHERE OTIT06 <> ''

UNION ALL

SELECT OTQUOT, OTIT07 AS ITEMS, ROUND(OQCQ07 * OVRC07,2) AS COST
FROM @[email protected]
WHERE OTIT07 <> ''

UNION ALL

SELECT OTQUOT, OTIT08 AS ITEMS, ROUND(OQCQ08 * OVRC08,2) AS COST
FROM @[email protected]
WHERE OTIT08 <> ''

UNION ALL

SELECT OTQUOT, OTIT09 AS ITEMS, ROUND(OQCQ09 * OVRC09,2) AS COST
FROM @[email protected]
WHERE OTIT09 <> ''

UNION ALL

SELECT OTQUOT, OTIT10 AS ITEMS, ROUND(OQCQ10 * OVRC10,2) AS COST
FROM @[email protected]
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

Answer 1

When using UNION, wrap your SELECT statements in parentheses.

e.g.

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

Edit: Upon closer inspection of that monstrosity of a query, there are likely numerous other errors within it. I suggest ripping parts of it out and provide us with the smallest possible example that still causes the syntax error.

Answer 2

It looks like you have two totally separate queries, with no semicolon between them.

This is where I think the problem is:

. . .
WHERE T.RN >= 1)

SELECT A.OKPBRN, A.OCAREA, A.OTCCDE, A.OTCNAM, A.OTSMAN,
. . .

---

It would really help when you ask questions to identify the brand and version of the database you're using. Different databases support different SQL language features, so the right solution depends strongly on the technology you're using. Tag your question with the brand you use.

There are many things wrong with your query:

• MySQL does not support WITH common table expressions. This is what made me think you were using Oracle or Microsoft SQL Server when you asked another question related to this query yesterday.

• MySQL does not support the ROW_NUMBER() windowing function. My apologies for suggesting you use this function, I had assumed you were using a database that supports WITH so I assumed ROW_NUMBER() would be supported too. You should learn to use the LIMIT clause if you use MySQL.

• Trying to put a subquery that returns multiple columns and multiple rows into a single column of your select-list.

• If you're trying to make the subquery part of the select-list, then SELECT belongs in front of the subquery. You should give it a column alias too.

• Complex date-conversion expressions. I would recommend storing dates in a canonical ISO form.

• The original design of OVRC01, OVRC02, OVRC03, etc. This is a repeating group, which violates First Normal Form. The same is true for OCQF01, OCQF02, OCQF03, etc.

• I'm not sure you're using LIKE wildcards correctly. * is not a standard wildcard for LIKE.

• Indecipherable table naming and column naming conventions. It looks like an Oracle database from the 1980's. Without knowing anything about your database or the problem you're trying to solve, it's impossible to recommend another solution.

Answer 3

It looks you have two CTE tables but only one is named. The second starts (SELECT OTQUOT, DESC(. You could name it (i.e., change it to , CALC2 As (SELECT OTQUOT, DESC(... but I haven't the foggiest idea where you are actually wanting to use this table in the rest of your query.