February 12, 2018 at 4:25 am
Hi All,
I'm trying to run this code in my asp.net app below
but getting error
SqlException :- incorrect syntax near the keyword as,incorrect syntax near t2SqlCommand cmd = new SqlCommand("select DATENAME(month, GETDATE()) AS [month],month(getdate()) as [monthnumber],t1.Actual,t2.Target
from (cast(round(sum(amount)/1000000,2) as decimal(10,2)) as [Actual] from Database1.dbo.trans where month(transdate) =month(getdate()) and year(transdate) = year(getdate()) and transtype=2 group by month(transdate)) as t1,
(Select cast(round(sum(monthly)/1000000,2) as decimal(10,2)) as [Target] from Sandbox.dbo.budget where month(date) = month(getdate()) and year(date)= year(getdate()) group by month(date)) as t2", con);
Not sure what i'm missing ..don't have enough access to create a view or stored proc..
Any help would be appreciated
February 12, 2018 at 4:35 am
There's a lot of things wrong with that, but the most obvious is that the FROM is not followed by a table or subquery. Looks like there's a SELECT missing.
Formatted to be readable:
select
DATENAME(month, GETDATE()) AS [month],
MONTH(getdate()) as [monthnumber],
t1.Actual,
t2.Target
from
(cast(round(sum(amount)/1000000,2) as decimal(10,2)) as [Actual] from Database1.dbo.trans where month(transdate) =month(getdate()) and year(transdate) = year(getdate()) and transtype=2 group by month(transdate)) as t1,
(Select cast(round(sum(monthly)/1000000,2) as decimal(10,2)) as [Target] from Sandbox.dbo.budget where month(date) = month(getdate()) and year(date)= year(getdate()) group by month(date)) as t2
You also have a cross join there. Two subqueries without any join criteria.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 12, 2018 at 4:50 am
GilaMonster - Monday, February 12, 2018 4:35 AMThere's a lot of things wrong with that, but the most obvious is that the FROM is not followed by a table or subquery. Looks like there's a SELECT missing.
Formatted to be readable:
select
DATENAME(month, GETDATE()) AS [month],
MONTH(getdate()) as [monthnumber],
t1.Actual,
t2.Target
from
(cast(round(sum(amount)/1000000,2) as decimal(10,2)) as [Actual] from Database1.dbo.trans where month(transdate) =month(getdate()) and year(transdate) = year(getdate()) and transtype=2 group by month(transdate)) as t1,
(Select cast(round(sum(monthly)/1000000,2) as decimal(10,2)) as [Target] from Sandbox.dbo.budget where month(date) = month(getdate()) and year(date)= year(getdate()) group by month(date)) as t2You also have a cross join there. Two subqueries without any join criteria.
Thanks for that but the main issue i have here is how to use this in my application(using sql command).Can i turn this into a dynamic sql?
February 12, 2018 at 5:28 am
Patrick.I - Monday, February 12, 2018 4:50 AMGilaMonster - Monday, February 12, 2018 4:35 AMThere's a lot of things wrong with that, but the most obvious is that the FROM is not followed by a table or subquery. Looks like there's a SELECT missing.
Formatted to be readable:
select
DATENAME(month, GETDATE()) AS [month],
MONTH(getdate()) as [monthnumber],
t1.Actual,
t2.Target
from
(cast(round(sum(amount)/1000000,2) as decimal(10,2)) as [Actual] from Database1.dbo.trans where month(transdate) =month(getdate()) and year(transdate) = year(getdate()) and transtype=2 group by month(transdate)) as t1,
(Select cast(round(sum(monthly)/1000000,2) as decimal(10,2)) as [Target] from Sandbox.dbo.budget where month(date) = month(getdate()) and year(date)= year(getdate()) group by month(date)) as t2You also have a cross join there. Two subqueries without any join criteria.
Thanks for that but the main issue i have here is how to use this in my application(using sql command).Can i turn this into a dynamic sql?
As Gail already mentioned: you're missing a SELECT in the first subquery. The formatted code from Gail is to make it readable for other posters.
When you have added the SELECT you can put the whole working T-SQL code within the quotation marks of the SQLCommand. Your code would look like this:
SqlCommand cmd = new SqlCommand("
SELECT DATENAME(month, GETDATE()) AS [month]
, month(getdate()) AS [monthnumber]
, t1.Actual
, t2.Target
FROM (
SELECT -- !!! you are missing this SELECT in your query !!!
cast(round(sum(amount) / 1000000, 2) AS DECIMAL(10, 2)) AS [Actual]
FROM Database1.dbo.trans
WHERE month(transdate) = month(getdate())
AND year(transdate) = year(getdate())
AND transtype = 2
GROUP BY month(transdate)
) AS t1
, (
SELECT cast(round(sum(monthly) / 1000000, 2) AS DECIMAL(10, 2)) AS [Target]
FROM Sandbox.dbo.budget
WHERE month(DATE) = month(getdate())
AND year(DATE) = year(getdate())
GROUP BY month(DATE)
) AS t2
", con);
And also take note of the remark of Gail about the CROSS JOIN!!. You have joined two subquery's but you have nowhere stated which criteria they must use to join. This will result in a cartesian product (resulting in the number of rows of the first SELECT multiplied by the number of rows in the second SELECT) .
February 12, 2018 at 5:31 am
Patrick.I - Monday, February 12, 2018 4:50 AMThanks for that but the main issue i have here is how to use this in my application(using sql command).Can i turn this into a dynamic sql?
???
Dynamic SQL is something you use in stored procedures. You have an ad-hoc SQL query here, so no, you would run if from you application as you have in the first post, with a SQLCommand object.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 13, 2018 at 5:09 am
Thanks guys.Works like a charm now 🙂
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply