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

• Patrick.I

  Hall of Fame

  Points: 3105

  More actions

  February 3, 2018 at 6:08 am

  #403082

  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

• Lynn Pettis

  SSC Guru

  Points: 442467

  More actions

  February 3, 2018 at 11:30 pm

  #1978457

  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.

• Sean Pearce

  SSCoach

  Points: 15999

  More actions

  February 4, 2018 at 5:00 am

  #1978461

  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]

• Lynn Pettis

  SSC Guru

  Points: 442467

  More actions

  February 4, 2018 at 12:59 pm

  #1978473

  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).

• Sean Pearce

  SSCoach

  Points: 15999

  More actions

  February 5, 2018 at 12:40 am

  #1978505

  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]