SQL Server Subquery causing error

Question

I am used to Firebird queries and now I need to make one in SQL Server, but I am getting an error with the subquery.

Error:

Msg 102, Level 15, State 1, Line 14
Incorrect syntax near ')'

Query:

SELECT 
    CVE_ART, IIF (CANTIDAD < 0, 0, CANTIDAD) AS CANTIDAD    
FROM
    (SELECT     
         CVE_ART,    
         SUM((CANT * SIGNO * -1) / 6) AS CANTIDAD    
     FROM   
         MINVE05    
     WHERE     
         DATEDIFF(MONTH, FECHA_DOCU, CURRENT_TIMESTAMP) >  0    
         AND DATEDIFF(MONTH, FECHA_DOCU, CURRENT_TIMESTAMP) <= 6    
         AND CVE_CPTO IN (2, 4, 51)    
         AND ALMACEN = 1    
     GROUP BY 
         CVE_ART)

Msg 102, Level 15, State 1, Line 14 Incorrect syntax near ')'. — Carlos
– Carlos, Commented May 16, 2018 at 0:11
You tagged 3 different version of SQL Server. Be sure to only tag the relevant one. This error would be the same on all versions, though. — S3S
– S3S, Commented May 16, 2018 at 0:27

Squirrel · Accepted Answer · 2018-05-16 00:13:06Z

5

You need to give a alias name to the derived table

SELECT CVE_ART, IIF (CANTIDAD<0,0,CANTIDAD) AS CANTIDAD   
FROM
(
    . . . 
) AS something

answered May 16, 2018 at 0:13

Squirrel

24.8k5 gold badges41 silver badges37 bronze badges

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

Comments

Gordon Linoff · Accepted Answer · 2018-05-16 02:19:32Z

Why not write the query like this?

SELECT CVE_ART,    
       (CASE WHEN SUM((CANT*SIGNO*-1)/6) < 0 THEN 0
             ELSE SUM((CANT*SIGNO*-1)/6)
        END) AS CANTIDAD    
FROM   MINVE05    
WHERE DATEDIFF(MONTH, FECHA_DOCU, CURRENT_TIMESTAMP) > 0  AND  
      DATEDIFF(MONTH, FECHA_DOCU, CURRENT_TIMESTAMP) <= 6    
      CVE_CPTO IN (2, 4, 51) AND  
      ALMACEN = 1    
GROUP BY CVE_ART ;

The subquery actually does not do much for the query, except take up space. The use of DATEDIFF() is curious. It counts boundaries between two dates. So, the first is saying that the calendar months of the two dates are different. So, 2018-01-31 and 2018-02-01 meet the condition, but 2018-01-01 and 2018-01-31 do not.

Collectives™ on Stack Overflow

SQL Server Subquery causing error

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