| Author |
Topic |
|
TPie9
Yak Posting Veteran
67 Posts |
 Posted - 2010-11-24 : 12:05:48
|
| I'm trying to run the code below as part of a pivot table query. Unfortunately it doesn't work with the where clause in the 'data' section. If I take the where clause out it works, but I need the where clause in to narrow down my results because otherwise I get millions of records I don't need. I'm wondering if that's because there's quotes ('') being used for the dateshipped and invoice numbers? This is the error I receive (Msg 102, Level 15, State 1, Line 43 Incorrect syntax near '10'.) and that's it. Any advice/help would be appreciated.ThanksSET @query = 'SELECT *FROM (select h.Invoice, cd.ChargeTypeCode, cd.Amountfrom InvoiceDetail h left join ServiceLevel sl on sl.levelofservicetype = h.levelofservicetype left join ChargeDistribution cd on cd.Invoice = h.Invoicewhere h.dateshipped between '10/01/2010' and '10/01/2010' and h.invoice in ('1231231231', '9879879879')) DataPIVOT(MAX(Amount)FOR [ChargeTypeCode]IN (' + @columns + '))AS p'EXECUTE(@query) |
|
|
X002548
Not Just a Number
15586 Posts |
|
|
TPie9
Yak Posting Veteran
67 Posts |
Posted - 2010-11-24 : 12:20:29
|
| I changed the 'SET @query' to 'Print @query' and received the following error message:Msg 102, Level 15, State 1, Line 28Incorrect syntax near '='. |
 |
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2010-11-24 : 12:27:25
|
Why are you doing this?DECLARE @query varchar(max), @columns varchar(max)SET @columns = 'xxx'SET @query = 'SELECT * FROM (select h.Invoice,cd.ChargeTypeCode,cd.Amount ' + 'from InvoiceDetail h ' + 'left join ServiceLevel sl on sl.levelofservicetype = h.levelofservicetype ' + 'left join ChargeDistribution cd on cd.Invoice = h.Invoice ' + 'where h.dateshipped between ''10/01/2010'' and ''10/01/2010''' + 'and h.invoice in (''1231231231'', ''9879879879'')' + ') Data ' + 'PIVOT (MAX(Amount) FOR [ChargeTypeCode] IN (' + @columns + ') )AS p'PRINT @queryBrett8-)Hint: Want your questions answered fast? Follow the direction in this linkhttp://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspxWant to help yourself?http://msdn.microsoft.com/en-us/library/ms130214.aspxhttp://weblogs.sqlteam.com/brettk/http://brettkaiser.blogspot.com/ |
|
|
|
TPie9
Yak Posting Veteran
67 Posts |
Posted - 2010-11-24 : 12:31:19
|
| Why am I doing what? I actually copied the code and adjusted it with my data and it worked. So not sure what else you're asking? |
|
|
|
TPie9
Yak Posting Veteran
67 Posts |
Posted - 2010-11-24 : 16:06:50
|
| I got my original query to work by putting an additional single quote by the existing single quotes in the where clause. Example below:Originalh.dateshipped between '10/01/2010' and '10/01/2010'and h.invoice in ('1231231231', '9879879879')Adjusted and it workedh.dateshipped between ''10/01/2010'' and ''10/01/2010''and h.invoice in (''1231231231'', ''9879879879'') |
|
|
|
X002548
Not Just a Number
15586 Posts |
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
|
|
|
Pivot Table - Set @query problem