error:- Unhandled Exception: System.Data.SqlClient.SqlException: Incorrect syntax near t he keyword 'as'.‌

  • HI All,
    I'm trying to use this sql query in my asp.net application but i keep getting error:-

    Unhandled Exception: System.Data.SqlClient.SqlException: Incorrect syntax near t
    he keyword 'as'.

       
    SqlCommand cmd = new SqlCommand("select DATENAME(month, GETDATE()) AS [month],month(getdate()) as [monthnumber],t1.Actual,t2.Target from (cast(round(sum(amountmst)/1000000,2) as decimal(10,2)) as [Actual] from trans where month(transdate) =month(getdate()) and year(transdate) = year(getdate()) and transtype=2 group by month(transdate)) as t1,
    (Select cast(round(sum(monthlybudget)/1000000,2) as decimal(10,2)) as [Target] from customer where month(date) = month(getdate()) and year(date)= year(getdate()) group by month(date)) as t2", con);

    I know this should be a stor proc but don't have access.
    Any ideas would be appreciated

  • Patrick.I - Saturday, February 3, 2018 6:07 AM

    HI All,
    I'm trying to use this sql query in my asp.net application but i keep getting error:-

    Unhandled Exception: System.Data.SqlClient.SqlException: Incorrect syntax near t
    he keyword 'as'.

       
    SqlCommand cmd = new SqlCommand("select DATENAME(month, GETDATE()) AS [month],month(getdate()) as [monthnumber],t1.Actual,t2.Target from (cast(round(sum(amountmst)/1000000,2) as decimal(10,2)) as [Actual] from trans where month(transdate) =month(getdate()) and year(transdate) = year(getdate()) and transtype=2 group by month(transdate)) as t1,
    (Select cast(round(sum(monthlybudget)/1000000,2) as decimal(10,2)) as [Target] from customer where month(date) = month(getdate()) and year(date)= year(getdate()) group by month(date)) as t2", con);

    I know this should be a stor proc but don't have access.
    Any ideas would be appreciated

    Could be that your SQL isn't even syntactically correct.

  • There is a missing select, and no join condition for t1 and t2.
    SqlCommand cmd = new SqlCommand("
        select
            DATENAME(month, GETDATE()) AS [month],
            month(getdate()) as [monthnumber],
            t1.Actual,
            t2.Target
        from
            ( [NO SELECT HERE]
                cast(round(sum(amountmst)/1000000,2) as decimal(10,2)) as [Actual]
            from
                trans
            where
                month(transdate) = month(getdate())
                and year(transdate) = year(getdate())
                and transtype=2
            group by
                month(transdate)) as t1,
            (Select
                cast(round(sum(monthlybudget)/1000000,2) as decimal(10,2)) as [Target]
            from
                customer
            where
                month(date) = month(getdate())
                and year(date)= year(getdate())
            group by
                month(date)) as t2
    [NO JOIN CONDITION HERE]
    ", con);

    The SQL Guy @ blogspot[/url]

    @SeanPearceSQL

    About Me[/url]

  • Sean Pearce - Sunday, February 4, 2018 5:00 AM

    There is a missing select, and no join condition for t1 and t2.
    SqlCommand cmd = new SqlCommand("
        select
            DATENAME(month, GETDATE()) AS [month],
            month(getdate()) as [monthnumber],
            t1.Actual,
            t2.Target
        from
            ( [NO SELECT HERE]
                cast(round(sum(amountmst)/1000000,2) as decimal(10,2)) as [Actual]
            from
                trans
            where
                month(transdate) = month(getdate())
                and year(transdate) = year(getdate())
                and transtype=2
            group by
                month(transdate)) as t1,
            (Select
                cast(round(sum(monthlybudget)/1000000,2) as decimal(10,2)) as [Target]
            from
                customer
            where
                month(date) = month(getdate())
                and year(date)= year(getdate())
            group by
                month(date)) as t2
    [NO JOIN CONDITION HERE]
    ", con);

    Actually, it is a Cartesian product (or cross join).

  • I didn't say there must be a join condition, just pointing out that there is no condition, which the OP may or may not be aware of 😎

    The SQL Guy @ blogspot[/url]

    @SeanPearceSQL

    About Me[/url]

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply