Incorrect syntax near ')' SQL SERVER

Question

I am trying to find data of One Table where Weeks column is greater than result of Subquery.But somehow that is not running due to Incorrect syntax

SELECT * FROM Table1 a WHERE CONVERT(DATE,SUBSTRING(a.WEEKS,CHARINDEX('W/E',WEEKS)+4,12),1)>
(SELECT MAX(x.WEEKS) AS MONTHLY_MAX_WEEKS FROM
(
(SELECT MAX(CONVERT(DATE,SUBSTRING(WEEKS,CHARINDEX('W/E',WEEKS)+4,12),1)) AS WEEKS  FROM Table2 
UNION ALL
SELECT MAX(CONVERT(DATE,SUBSTRING(WEEKS,CHARINDEX('W/E',WEEKS)+4,12),1)) AS WEEKS FROM Table3 
) AS x))

You already have a solution, but for situations like this, try breaking down and running each part of your query separately first to see if they run. That can help track down the error. Then start combining them slowly to find the error. This approach works good for testing logic as well, test each part FIRST then combine them. — Brad
– Brad, Commented Feb 16, 2021 at 13:53

Olgu Dulger · Accepted Answer · 2021-02-16 13:48:54Z

2

You should change like this

SELECT * FROM Table1 a WHERE CONVERT(DATE,SUBSTRING(a.WEEKS,CHARINDEX('W/E',WEEKS)+4,12),1)>
(
SELECT MAX(x.WEEKS) AS MONTHLY_MAX_WEEKS 
FROM
(
  (SELECT MAX(CONVERT(DATE,SUBSTRING(WEEKS,CHARINDEX('W/E',WEEKS)+4,12),1)) AS WEEKS  FROM Table2 
  UNION ALL
  SELECT MAX(CONVERT(DATE,SUBSTRING(WEEKS,CHARINDEX('W/E',WEEKS)+4,12),1)) AS WEEKS FROM Table3 
  ) 
)as x
)

answered Feb 16, 2021 at 13:48

Olgu Dulger

603 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

ScaisEdge · Accepted Answer · 2021-02-16 13:51:34Z

2

You should remove some AS

SELECT * 
FROM Table1 a 
WHERE CONVERT(DATE,SUBSTRING(a.WEEKS,CHARINDEX('W/E',WEEKS)+4,12),1) >
(SELECT MAX(x.WEEKS) AS MONTHLY_MAX_WEEKS 
    FROM (
    SELECT MAX(CONVERT(DATE,SUBSTRING(WEEKS,CHARINDEX('W/E',WEEKS)+4,12),1)) AS WEEKS  FROM Table2 
    UNION ALL
    SELECT MAX(CONVERT(DATE,SUBSTRING(WEEKS,CHARINDEX('W/E',WEEKS)+4,12),1)) AS WEEKS FROM Table3 
) t  )

answered Feb 16, 2021 at 13:51

ScaisEdge

133k10 gold badges98 silver badges111 bronze badges

Collectives™ on Stack Overflow

Incorrect syntax near ')' SQL SERVER

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related