locked
Incorrect syntax near 'AS' RRS feed

  • Question

  • Belove is the query which showing me as error in all the “AS”… Incorrect syntax near 'AS'


    declare @A int = 12;

                              declare @yr int = 13;

                              declare @S int = 3;

                             

                              (SELECT     Ctm, - SUM(Aammt) AS D

                                FROM          DBname.Schmaname.Tab1

                                WHERE      (Ayr = @yr + 1) AND (Vtn = 51)

                                GROUP BY Ctm) AS T ON @A = T.Ctm LEFT OUTER JOIN

                              (SELECT     Ctm, - SUM(Aammt) AS DDeduction

                                FROM          DBname.Schmaname.Tab1 AS Tab1_1

                                WHERE      (Ayr = @yr + 1) AND (Vtn = 45)

                                GROUP BY Ctm) AS DD ON @S= DD.Ctm LEFT OUTER JOIN

                              (SELECT     Ctm, - SUM(Aammt) AS Bbbl

                                FROM          DBname.Schmaname.DebLBbbl

                                WHERE      (@yr <= @yr)

                                GROUP BY Ctm) AS B ON @S= B.Ctm

    Wednesday, July 10, 2013 6:05 AM

All replies

  • SELECT     Ctm, - SUM(Aammt) AS D
    This line has issue. Before SUM, there is a - sign.

    Srinivasan

    Wednesday, July 10, 2013 6:06 AM
  • SELECT     Ctm, - SUM(Aammt) AS D
    This line has issue. Before SUM, there is a - sign.

    Srinivasan

       That – SUM(columnName) means it will return -100 value to 100


    Wednesday, July 10, 2013 6:24 AM
  • Please post DDL. You need to write something like this. (not tested)

    declare @A int = 12;
    declare @yr int = 13;
    declare @S int = 3;
    SELECT * FROM (
    	(SELECT     Ctm, - SUM(Aammt) AS D   FROM  DBname.Schmaname.Tab1
    		WHERE      (Ayr = @yr + 1) AND (Vtn = 51) AND Ctm = @A
    		GROUP BY Ctm
    	 ) T LEFT OUTER JOIN 
    	(SELECT Ctm, - SUM(Aammt) AS DDeduction  FROM DBname.Schmaname.Tab1 AS Tab1_1
            WHERE      (Ayr = @yr + 1) AND (Vtn = 45) AND Ctm = @S
    		GROUP BY Ctm) AS DD 
    	ON DD.Ctm = T.Ctm LEFT OUTER JOIN
        (SELECT     Ctm, - SUM(Aammt) AS Bbbl   FROM  DBname.Schmaname.DebLBbbl
             WHERE      (@yr <= @yr) AND Ctm = @S
             GROUP BY Ctm) AS B ON DD.Ctm=B.Ctm
    ) X


    Regards, RSingh

    Wednesday, July 10, 2013 6:42 AM
  • Also you can try the below code, (not tested)

    ;WITH CTE1 AS (
    	SELECT     Ctm, - SUM(Aammt) AS D   FROM  DBname.Schmaname.Tab1
    		WHERE      (Ayr = @yr + 1) AND (Vtn = 51) AND Ctm = @A
    		GROUP BY Ctm
    ),CTE2 AS (
    	SELECT * FROM CTE1 T LEFT OUTER JOIN (
    	SELECT Ctm, - SUM(Aammt) AS DDeduction  FROM DBname.Schmaname.Tab1 AS Tab1_1
            WHERE      (Ayr = @yr + 1) AND (Vtn = 45) AND Ctm = @S
    		GROUP BY Ctm
    	) DD ON DD.Ctm = T.Ctm
    ),CTE3 AS (
    	SELECT * FROM CTE2 DD LEFT OUTER JOIN (
    	SELECT     Ctm, - SUM(Aammt) AS Bbbl   FROM  DBname.Schmaname.DebLBbbl
             WHERE      (@yr <= @yr) AND Ctm = @S
             GROUP BY Ctm
    	) B ON B.Ctm=DD.Ctm
    )
    --- FINALLY
    SELECT * FROM CTE3


    Regards, RSingh


    Wednesday, July 10, 2013 6:48 AM
  • Please post DDL. You need to write something like this. (not tested)

    declare @A int = 12;
    declare @yr int = 13;
    declare @S int = 3;
    SELECT * FROM (
    	(SELECT     Ctm, - SUM(Aammt) AS D   FROM  DBname.Schmaname.Tab1
    		WHERE      (Ayr = @yr + 1) AND (Vtn = 51) AND Ctm = @A
    		GROUP BY Ctm
    	 ) T LEFT OUTER JOIN 
    	(SELECT Ctm, - SUM(Aammt) AS DDeduction  FROM DBname.Schmaname.Tab1 AS Tab1_1
            WHERE      (Ayr = @yr + 1) AND (Vtn = 45) AND Ctm = @S
    		GROUP BY Ctm) AS DD 
    	ON DD.Ctm = T.Ctm LEFT OUTER JOIN
        (SELECT     Ctm, - SUM(Aammt) AS Bbbl   FROM  DBname.Schmaname.DebLBbbl
             WHERE      (@yr <= @yr) AND Ctm = @S
             GROUP BY Ctm) AS B ON DD.Ctm=B.Ctm --error
    ) X


    Regards, RSingh

    Thanks RSing...

    the above querry have an error on second last line saying "an expression of non-boolean type specified in a context where a condition is expected"

    Wednesday, July 10, 2013 7:36 AM
  • Also you can try the below code, (not tested)

    ;WITH CTE1 AS (
    	SELECT     Ctm, - SUM(Aammt) AS D   FROM  DBname.Schmaname.Tab1
    		WHERE      (Ayr = @yr + 1) AND (Vtn = 51) AND Ctm = @A
    		GROUP BY Ctm
    ),CTE2 AS (
    	SELECT * FROM CTE1 T LEFT OUTER JOIN (
    	SELECT Ctm, - SUM(Aammt) AS DDeduction  FROM DBname.Schmaname.Tab1 AS Tab1_1
            WHERE      (Ayr = @yr + 1) AND (Vtn = 45) AND Ctm = @S
    		GROUP BY Ctm
    	) DD ON DD.Ctm = T.Ctm
    ),CTE3 AS (
    	SELECT * FROM CTE2 DD LEFT OUTER JOIN (
    	SELECT     Ctm, - SUM(Aammt) AS Bbbl   FROM  DBname.Schmaname.DebLBbbl
             WHERE      (@yr <= @yr) AND Ctm = @S
             GROUP BY Ctm
    	) B ON B.Ctm=DD.Ctm
    )
    --- FINALLY
    SELECT * FROM CTE3


    Regards, RSingh


    this is retrieving  the wrong data :-(
    Wednesday, July 10, 2013 7:40 AM
  • We have no idea what you are trying to do - but your syntax is all messed up.  It appears you are trying to use derived tables - that is a just a guess.  The general form of such is:

    select T.col, ... from (select ... from ... where ... ) as T where T.col <> 5;

    You have an odd assortment of parentheses and even stranger attempts to join your derived tables. Lets look at the first select statement - the one you gave the alias "T".  Immediately after the alias name, you attempt to join it to a variable.  You can't do that.  You need to move the logical condition into the select statement by adding it to the where clause.  Example:

    select Ctm ... from .. where Ayr = @yr + 1 and Vtn = 51 and Ctm = @A ...

    Now, given that you are limiting the rows to a specific value from Ctm, you could simply replace the colum with the variable and remove the group by clause. Example:

    select @A as Ctm, - sum(... from .. where Ayr = @yr + 1 and Vtn = 51 and Ctm = @A )

    That doesn't completely solve your problem, since we have no idea what you are trying to accomplish with this aggregation of statements.  This is not valid tsql syntax, but the replacement is not obvious since that depends on the number and shape of the resultsets you desire - as well as the rather nebulous relationships between them.

    Wednesday, July 10, 2013 12:57 PM
  • I believe what you are trying to do is this:

    declare @A int = 12;
    declare @yr int = 13;
    declare @S int = 3;
                              
    (SELECT     Ctm, -1*SUM(Aammt) AS D
    FROM          DBname.Schmaname.Tab1
    WHERE      (Ayr = @yr + 1) AND (Vtn = 51)
    GROUP BY Ctm) AS T ON @A = T.Ctm LEFT OUTER JOIN
    (SELECT     Ctm, -1*SUM(Aammt) AS DDeduction
    FROM          DBname.Schmaname.Tab1 AS Tab1_1
    WHERE      (Ayr = @yr + 1) AND (Vtn = 45)
    GROUP BY Ctm) AS DD ON @S= DD.Ctm LEFT OUTER JOIN
    (SELECT     Ctm, -1*SUM(Aammt) AS Bbbl
    FROM          DBname.Schmaname.DebLBbbl
    WHERE      (@yr <= @yr)
    GROUP BY Ctm) AS B ON @S= B.Ctm

    Wednesday, July 10, 2013 1:31 PM