CODEWITHSUNDEEP
sql
Bob
Bob

Reputation: 885

Unable to pinpoint the error on SQL statement

I have issued the SQL statement below :

SELECT GATNR    
      ,GITNO    
      ,GBANO    
      ,MAX (CASE WHEN GATID = 'GR' THEN GATVN ELSE NULL END) AS GR  
      ,MAX (CASE WHEN GATID = 'SZ' THEN GATVN ELSE NULL END) AS SZ  
FROM TABLEA AS X    
INNER JOIN  
(   
   ( 
     SELECT MAX(GATNR)AS GATNR,GBANO    
     FROM TABLEA    
     GROUP BY GBANO 
   )    
   INNER JOIN   
   (
     SELECT *   
     FROM TABLEB    
   ) AS B   
   ON B.TBANO = GBANO   
) AS Y  
ON Y.GATNR = X.GATNR    
WHERE   
  GITNO LIKE 'F%'   
GROUP BY X.GATNR, X.GITNO, X.GBANO

For some reason, it failed to run & generated the following error messages:

Msg 156, Level 15, State 1, Line 13 Incorrect syntax near the keyword 'INNER'.
Msg 156, Level 15, State 1, Line 16 Incorrect syntax near the keyword 'AS'.

I'm unable to resolve it. Can someone help me to correct the statement ?

Upvotes: 0

Views: 123

Answers (2)

Spike Gronim
Spike Gronim

Reputation: 6182

Try changing this:

 SELECT MAX(GATNR)AS GATNR,GBANO

To:

 SELECT MAX(GATNR) AS GATNR,GBANO

(I added a space between "MAX(GATNR)" and "AS"). I am not sure if this will fix the query.

Upvotes: 0

bucko
bucko

Reputation: 1188

I think you want something like this:

SELECT GATNR    
      ,GITNO    
      ,GBANO    
      ,MAX (CASE WHEN GATID = 'GR' THEN GATVN ELSE NULL END) AS GR  
      ,MAX (CASE WHEN GATID = 'SZ' THEN GATVN ELSE NULL END) AS SZ  
FROM TABLEA AS X    
INNER JOIN  
(SELECT MAX(GATNR)AS GATNR,GBANO    
FROM TABLEA 
GROUP BY GBANO  
) AS C
ON C.GBANO = X.GBANO AND C.GATNR = X.GATNR
INNER JOIN  
TABLEB AS B  
ON B.TBANO = X.GBANO 
WHERE   
  GITNO LIKE 'F%'   
GROUP BY X.GATNR, X.GITNO, X.GBANO

It's hard to know without seeing your schema, though.

Upvotes: 1

Related Questions