March 16, 2009 at 8:52 am
I have a query that looks like this:
Declare @SQL nvarchar(MAX)
set @SQL = ' SELECT x.SaleYear, x.TotalSales
FROM (SELECT SaleYear,
TotalSales= ISNULL((SELECT SUM(TotalSales) FROM InsSales WHERE region= ''NE'' AND SaleYear = y.SaleYear and salesPerson in (''' + @SalesPerson + ''') and SalesMonth <= (' + @Salesmonth + ') ,0)
FROM InsSales y where SaleYear in (' + @CYear + ', ' + @PYear + ')
GROUP BY SaleYear) As x '
its giving me and error on the last { ' is not a valid identifier}.
what's missing or wrong within this query?
What I'm trying to do is pass in the current year, prior year, and show the sales numbers like this
SaleYear TotalSales
2009 45,000
2008 32,000
and so on, What I really like to do is this if possible
I would like to pass my data like this
region = 'NE,'SE','SW','SE'
SalesPerson = 'Smith','Barney','Jones' if All Sales People selected or
SalesPerson = 'Smith' if only smith is selected.
What is the best way to do either one my lovely SQL issues?
March 16, 2009 at 8:57 am
I see two "from"s for one select statement...It looks like the "from" in the subselect is misplaced
March 16, 2009 at 8:59 am
could you do a PRINT Of @SQL for us so we can see what the code is that is trying to be run.
you might find that delimiting your strings to tables and joining on them could provide a better solution.
----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley
:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]
March 16, 2009 at 8:59 am
I declared the other variables, and then ran your code and it worked fine.
I suspect you're getting this error on an exec(@sql), correct? Please include the code that's the problem.
Instead of exec(), use print and see what the actual SQL is being run. Likely you have a { in one of your dates.
March 16, 2009 at 9:04 am
here is the query and the print statement when I run it:
query:
Declare @SQL nvarchar(MAX)
set @SQL = ' SELECT x.SaleYear, x.TotalSales
FROM (SELECT SaleYear,
TotalSales= ISNULL((SELECT SUM(TotalSales) FROM InsSales WHERE region= ''NE'' AND SaleYear = y.SaleYear and salesPerson in (''' + @SalesPerson + ''') and SalesMonth <= (' + @Salesmonth + ') ,0)
FROM InsSales y where SaleYear in (' + @CYear + ', ' + @PYear + ')
GROUP BY SaleYear) As x '
print statement
SELECT x.SaleYear, x.TotalSales
FROM (SELECT SaleYear,
TotalSales = ISNULL((SELECT SUM(TotalSales) FROM InsSales WHERE region = 'NE' AND SaleYear = y.SaleYear and salesPerson in ('Smith') and SalesMonth<= (2) ,0)
FROM InsSales y where SaleYear in (2009, 2008)
GROUP BY SaleYear) As x ' is not a valid identifier.
March 16, 2009 at 9:10 am
I think you missing a bracket in your nested select .
so try this:
Declare @SQL nvarchar(MAX)
set @SQL = ' SELECT x.SaleYear, x.TotalSales
FROM (SELECT SaleYear,
TotalSales= ISNULL((SELECT SUM(TotalSales) FROM InsSales WHERE region= ''NE''
AND SaleYear = y.SaleYear and salesPerson in (''' + @SalesPerson + ''')
and SalesMonth <= (' + @Salesmonth + ')) ,0) -- EXTRA BRACKET ADDED HERE
FROM InsSales y where SaleYear in (' + @CYear + ', ' + @PYear + ')
GROUP BY SaleYear) As x '
----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley
:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]
March 16, 2009 at 9:13 am
I tried that and still get the same error message.
March 16, 2009 at 9:16 am
ok could I get table definitions in the form a create statement so that I can run a complete script.
thanks
----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley
:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]
March 16, 2009 at 9:21 am
running this code gives no errors.
CREATE TABLE InsSales
(
region VARCHAR(100),
SaleYear INT,
salesPerson VARCHAR(100),
SalesMonth INT,
TotalSales INT )
SELECT x.SaleYear, x.TotalSales
FROM (SELECT SaleYear,
TotalSales = ISNULL((SELECT SUM(TotalSales)
FROM InsSales
WHERE region = 'NE'
AND SaleYear = y.SaleYear
and salesPerson in ('Smith')
and SalesMonth<= (2)) ,0)
FROM InsSales y where SaleYear in (2009, 2008)
GROUP BY SaleYear) As x
Can you confirm this is the same for you?
----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley
:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]
March 16, 2009 at 9:24 am
I can run it with the values hard coded just fine, so could it be the way the data is being passed to the proc, or could it be something else?
March 16, 2009 at 9:27 am
what about if you run this?
CREATE TABLE InsSales
(
region VARCHAR(100),
SaleYear INT,
salesPerson VARCHAR(100),
SalesMonth INT,
TotalSales INT )
GO
DECLARE @CYear VARCHAR(100)
DECLARE @PYear VARCHAR(100)
DECLARE @SalesPerson VARCHAR(100)
DECLARE @Salesmonth VARCHAR(100)
SELECT
@CYear = 2009,
@PYear = 2008,
@SalesPerson = 'Smith',
@Salesmonth = 2
Declare @SQL nvarchar(MAX)
set @SQL = ' SELECT x.SaleYear, x.TotalSales
FROM (SELECT SaleYear,
TotalSales= ISNULL((SELECT SUM(TotalSales)
FROM InsSales
WHERE region= ''NE'' AND SaleYear = y.SaleYear
and salesPerson in (''' + @SalesPerson + ''')
and SalesMonth <= (' + @Salesmonth + ')) ,0)
FROM InsSales y where SaleYear in (' + @CYear + ', ' + @PYear + ')
GROUP BY SaleYear) As x '
EXEC ( @SQL)
Oh and if you getting an error could you COPY AND PASTE exactly what it is saying to your next post
----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley
:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]
March 16, 2009 at 9:30 am
Run it as a print and please post that.
March 16, 2009 at 9:34 am
both of the posting that I have made run 100% and the only thing I have changed was adding the )
So I think the actually variable values that are being used are incorrect!!!
I don't think they are the same as the examples we are getting...
----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley
:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]
March 16, 2009 at 9:35 am
I would like to pass my data like this
region = 'NE,'SE','SW','SE'
SalesPerson = 'Smith','Barney','Jones' if All Sales People selected or
SalesPerson = 'Smith' if only smith is selected.
Were you trying to pass the variable @SalesPerson exactly like 'Smith','Barney','Jones'? If that's the case, you have to change SalesPerson = @SalesPerson to SalesPerson IN (@SalesPerson). Same for Region. Or am I completely missing the point?
Ronald HensbergenHelp us, help yourself... Post data so we can read and use it: http://www.sqlservercentral.com/articles/Best+Practices/61537/-------------------------------------------------------------------------2+2=5 for significant large values of 2
March 16, 2009 at 10:29 am
I'm gettiing the same error as I was when I posted.
this thing is driving me nuts, i've changed and tried everything you have recommended and I still get
{' is not a valid identifier.} as the error message.
Viewing 15 posts - 1 through 15 (of 32 total)
You must be logged in to reply to this topic. Login to reply