| Groups | Blog | Home |
sql server reporting services : TOP N Error in SQL
and the top n needs to be a parameter. I keep getting an ado error on my sql query. Can someone tell me what I am doing wrong? I would appreciate it. Thanks in advance. if @Topn = ' ' BEGIN SELECT Defects.Defect1, Units.CreateDate, COUNT(*) AS Expr1 FROM Defects INNER JOIN UnitDefects ON Defects.DefectID = UnitDefects.DefectID INNER JOIN Units ON UnitDefects.UnitID = Units.UnitID GROUP BY Units.CreateDate, Defects.Defect1 HAVING (Units.CreateDate BETWEEN @StartDate AND @EndDate) AND (NOT (Defects.Defect1 = 'freon charge')) AND (NOT (Defects.Defect1 = 'spc')) ORDER BY Expr1 DESC ELSE BEGIN SELECT TOP @Topn Defects.Defect1, Units.CreateDate, COUNT(*) AS Expr1 FROM Defects INNER JOIN UnitDefects ON Defects.DefectID = UnitDefects.DefectID INNER JOIN Units ON UnitDefects.UnitID = Units.UnitID GROUP BY Units.CreateDate, Defects.Defect1 HAVING (Units.CreateDate BETWEEN @StartDate AND @EndDate) AND (NOT (Defects.Defect1 = 'freon charge')) AND (NOT (Defects.Defect1 = 'spc')) ORDER BY Expr1 DESC END
Hi
TOP does not support variables. SELECT TOP 10 works, SELECT TOP @Topn does not. You need to use dynamic SQL if you want to do it in the SP. Regards -------------------------------- Mike Epprecht, Microsoft SQL Server MVP Zurich, Switzerland IM: mike@epprecht.net MVP Program: http://www.microsoft.com/mvp Blog: http://www.msmvps.com/epprecht/ [quoted text, click to view] "Andy Jones" <ajones@rheemac.com> wrote in message news:OgZFGlMMFHA.1476@TK2MSFTNGP09.phx.gbl... > I am trying to select the top n of a result where the date is a parameter > and the top n needs to be a parameter. I keep getting an ado error on my sql > query. Can someone tell me what I am doing wrong? I would appreciate it. > Thanks in advance. > > > if @Topn = ' ' > BEGIN > SELECT Defects.Defect1, Units.CreateDate, COUNT(*) AS Expr1 > FROM Defects INNER JOIN > UnitDefects ON Defects.DefectID = UnitDefects.DefectID > INNER JOIN > Units ON UnitDefects.UnitID = Units.UnitID > GROUP BY Units.CreateDate, Defects.Defect1 > HAVING (Units.CreateDate BETWEEN @StartDate AND @EndDate) AND (NOT > (Defects.Defect1 = 'freon charge')) AND (NOT (Defects.Defect1 = 'spc')) > ORDER BY Expr1 DESC > > ELSE > > BEGIN > > SELECT TOP @Topn Defects.Defect1, Units.CreateDate, COUNT(*) AS Expr1 > FROM Defects INNER JOIN > UnitDefects ON Defects.DefectID = UnitDefects.DefectID > INNER JOIN > Units ON UnitDefects.UnitID = Units.UnitID > GROUP BY Units.CreateDate, Defects.Defect1 > HAVING (Units.CreateDate BETWEEN @StartDate AND @EndDate) AND (NOT > (Defects.Defect1 = 'freon charge')) AND (NOT (Defects.Defect1 = 'spc')) > ORDER BY Expr1 DESC > > END > >
Thanks for the tip. However, I am new to SQL and I do not know what you mean
by dynamic sql? [quoted text, click to view] "Mike Epprecht (SQL MVP)" <mike@epprecht.net> wrote in message news:%23QVCUsMMFHA.2940@TK2MSFTNGP15.phx.gbl... > Hi > > TOP does not support variables. > > SELECT TOP 10 works, SELECT TOP @Topn does not. > > You need to use dynamic SQL if you want to do it in the SP. > > Regards > -------------------------------- > Mike Epprecht, Microsoft SQL Server MVP > Zurich, Switzerland > > IM: mike@epprecht.net > > MVP Program: http://www.microsoft.com/mvp > > Blog: http://www.msmvps.com/epprecht/ > > "Andy Jones" <ajones@rheemac.com> wrote in message > news:OgZFGlMMFHA.1476@TK2MSFTNGP09.phx.gbl... >> I am trying to select the top n of a result where the date is a parameter >> and the top n needs to be a parameter. I keep getting an ado error on my > sql >> query. Can someone tell me what I am doing wrong? I would appreciate it. >> Thanks in advance. >> >> >> if @Topn = ' ' >> BEGIN >> SELECT Defects.Defect1, Units.CreateDate, COUNT(*) AS Expr1 >> FROM Defects INNER JOIN >> UnitDefects ON Defects.DefectID = > UnitDefects.DefectID >> INNER JOIN >> Units ON UnitDefects.UnitID = Units.UnitID >> GROUP BY Units.CreateDate, Defects.Defect1 >> HAVING (Units.CreateDate BETWEEN @StartDate AND @EndDate) AND (NOT >> (Defects.Defect1 = 'freon charge')) AND (NOT (Defects.Defect1 = 'spc')) >> ORDER BY Expr1 DESC >> >> ELSE >> >> BEGIN >> >> SELECT TOP @Topn Defects.Defect1, Units.CreateDate, COUNT(*) AS >> Expr1 >> FROM Defects INNER JOIN >> UnitDefects ON Defects.DefectID = > UnitDefects.DefectID >> INNER JOIN >> Units ON UnitDefects.UnitID = Units.UnitID >> GROUP BY Units.CreateDate, Defects.Defect1 >> HAVING (Units.CreateDate BETWEEN @StartDate AND @EndDate) AND (NOT >> (Defects.Defect1 = 'freon charge')) AND (NOT (Defects.Defect1 = 'spc')) >> ORDER BY Expr1 DESC >> >> END >> >> > >
thanks for the help. I guess this will not work for. I haven't been able to
get past the query analyzer it keeps giving me errors. Thanks again [quoted text, click to view] "Mary Bray [MVP]" <reply@tonewsgroup.com.NOSPAMPLEASE> wrote in message news:e4QU02PMFHA.3492@TK2MSFTNGP10.phx.gbl... > You can however set the rowcount using a variable ie: > if @Topn <> ' ' > set @@rowcount=@Topn > > etc > > -- > > Mary Bray [SQL Server MVP] > Please reply only to newsgroups > > "Mike Epprecht (SQL MVP)" <mike@epprecht.net> wrote in message > news:%23QVCUsMMFHA.2940@TK2MSFTNGP15.phx.gbl... >> Hi >> >> TOP does not support variables. >> >> SELECT TOP 10 works, SELECT TOP @Topn does not. >> >> You need to use dynamic SQL if you want to do it in the SP. >> >> Regards >> -------------------------------- >> Mike Epprecht, Microsoft SQL Server MVP >> Zurich, Switzerland >> >> IM: mike@epprecht.net >> >> MVP Program: http://www.microsoft.com/mvp >> >> Blog: http://www.msmvps.com/epprecht/ >> >> "Andy Jones" <ajones@rheemac.com> wrote in message >> news:OgZFGlMMFHA.1476@TK2MSFTNGP09.phx.gbl... >>> I am trying to select the top n of a result where the date is a >>> parameter >>> and the top n needs to be a parameter. I keep getting an ado error on my >> sql >>> query. Can someone tell me what I am doing wrong? I would appreciate it. >>> Thanks in advance. >>> >>> >>> if @Topn = ' ' >>> BEGIN >>> SELECT Defects.Defect1, Units.CreateDate, COUNT(*) AS Expr1 >>> FROM Defects INNER JOIN >>> UnitDefects ON Defects.DefectID = >> UnitDefects.DefectID >>> INNER JOIN >>> Units ON UnitDefects.UnitID = Units.UnitID >>> GROUP BY Units.CreateDate, Defects.Defect1 >>> HAVING (Units.CreateDate BETWEEN @StartDate AND @EndDate) AND (NOT >>> (Defects.Defect1 = 'freon charge')) AND (NOT (Defects.Defect1 = 'spc')) >>> ORDER BY Expr1 DESC >>> >>> ELSE >>> >>> BEGIN >>> >>> SELECT TOP @Topn Defects.Defect1, Units.CreateDate, COUNT(*) AS >>> Expr1 >>> FROM Defects INNER JOIN >>> UnitDefects ON Defects.DefectID = >> UnitDefects.DefectID >>> INNER JOIN >>> Units ON UnitDefects.UnitID = Units.UnitID >>> GROUP BY Units.CreateDate, Defects.Defect1 >>> HAVING (Units.CreateDate BETWEEN @StartDate AND @EndDate) AND (NOT >>> (Defects.Defect1 = 'freon charge')) AND (NOT (Defects.Defect1 = 'spc')) >>> ORDER BY Expr1 DESC >>> >>> END >>> >>> >> >> > >
Makes sense now. I guess I jsut need to figure out how I am coding it wrong
since the query keeps giving me errors. Thanks. [quoted text, click to view] "Mike Epprecht (SQL MVP)" <mike@epprecht.net> wrote in message news:uFH9nPWMFHA.2580@TK2MSFTNGP09.phx.gbl... > Hi > > http://www.sommarskog.se/dynamic_sql.html > > Regards > -------------------------------- > Mike Epprecht, Microsoft SQL Server MVP > Zurich, Switzerland > > IM: mike@epprecht.net > > MVP Program: http://www.microsoft.com/mvp > > Blog: http://www.msmvps.com/epprecht/ > > "Andy Jones" <ajones@rheemac.com> wrote in message > news:u0QNxKWMFHA.3540@tk2msftngp13.phx.gbl... >> Thanks for the tip. However, I am new to SQL and I do not know what you > mean >> by dynamic sql? >> "Mike Epprecht (SQL MVP)" <mike@epprecht.net> wrote in message >> news:%23QVCUsMMFHA.2940@TK2MSFTNGP15.phx.gbl... >> > Hi >> > >> > TOP does not support variables. >> > >> > SELECT TOP 10 works, SELECT TOP @Topn does not. >> > >> > You need to use dynamic SQL if you want to do it in the SP. >> > >> > Regards >> > -------------------------------- >> > Mike Epprecht, Microsoft SQL Server MVP >> > Zurich, Switzerland >> > >> > IM: mike@epprecht.net >> > >> > MVP Program: http://www.microsoft.com/mvp >> > >> > Blog: http://www.msmvps.com/epprecht/ >> > >> > "Andy Jones" <ajones@rheemac.com> wrote in message >> > news:OgZFGlMMFHA.1476@TK2MSFTNGP09.phx.gbl... >> >> I am trying to select the top n of a result where the date is a > parameter >> >> and the top n needs to be a parameter. I keep getting an ado error on > my >> > sql >> >> query. Can someone tell me what I am doing wrong? I would appreciate > it. >> >> Thanks in advance. >> >> >> >> >> >> if @Topn = ' ' >> >> BEGIN >> >> SELECT Defects.Defect1, Units.CreateDate, COUNT(*) AS Expr1 >> >> FROM Defects INNER JOIN >> >> UnitDefects ON Defects.DefectID = >> > UnitDefects.DefectID >> >> INNER JOIN >> >> Units ON UnitDefects.UnitID = Units.UnitID >> >> GROUP BY Units.CreateDate, Defects.Defect1 >> >> HAVING (Units.CreateDate BETWEEN @StartDate AND @EndDate) AND >> >> (NOT >> >> (Defects.Defect1 = 'freon charge')) AND (NOT (Defects.Defect1 = >> >> 'spc')) >> >> ORDER BY Expr1 DESC >> >> >> >> ELSE >> >> >> >> BEGIN >> >> >> >> SELECT TOP @Topn Defects.Defect1, Units.CreateDate, COUNT(*) AS >> >> Expr1 >> >> FROM Defects INNER JOIN >> >> UnitDefects ON Defects.DefectID = >> > UnitDefects.DefectID >> >> INNER JOIN >> >> Units ON UnitDefects.UnitID = Units.UnitID >> >> GROUP BY Units.CreateDate, Defects.Defect1 >> >> HAVING (Units.CreateDate BETWEEN @StartDate AND @EndDate) AND >> >> (NOT >> >> (Defects.Defect1 = 'freon charge')) AND (NOT (Defects.Defect1 = >> >> 'spc')) >> >> ORDER BY Expr1 DESC >> >> >> >> END >> >> >> >> >> > >> > >> >> > >
You can however set the rowcount using a variable ie:
if @Topn <> ' ' set @@rowcount=@Topn etc -- Mary Bray [SQL Server MVP] Please reply only to newsgroups [quoted text, click to view] "Mike Epprecht (SQL MVP)" <mike@epprecht.net> wrote in message news:%23QVCUsMMFHA.2940@TK2MSFTNGP15.phx.gbl... > Hi > > TOP does not support variables. > > SELECT TOP 10 works, SELECT TOP @Topn does not. > > You need to use dynamic SQL if you want to do it in the SP. > > Regards > -------------------------------- > Mike Epprecht, Microsoft SQL Server MVP > Zurich, Switzerland > > IM: mike@epprecht.net > > MVP Program: http://www.microsoft.com/mvp > > Blog: http://www.msmvps.com/epprecht/ > > "Andy Jones" <ajones@rheemac.com> wrote in message > news:OgZFGlMMFHA.1476@TK2MSFTNGP09.phx.gbl... >> I am trying to select the top n of a result where the date is a parameter >> and the top n needs to be a parameter. I keep getting an ado error on my > sql >> query. Can someone tell me what I am doing wrong? I would appreciate it. >> Thanks in advance. >> >> >> if @Topn = ' ' >> BEGIN >> SELECT Defects.Defect1, Units.CreateDate, COUNT(*) AS Expr1 >> FROM Defects INNER JOIN >> UnitDefects ON Defects.DefectID = > UnitDefects.DefectID >> INNER JOIN >> Units ON UnitDefects.UnitID = Units.UnitID >> GROUP BY Units.CreateDate, Defects.Defect1 >> HAVING (Units.CreateDate BETWEEN @StartDate AND @EndDate) AND (NOT >> (Defects.Defect1 = 'freon charge')) AND (NOT (Defects.Defect1 = 'spc')) >> ORDER BY Expr1 DESC >> >> ELSE >> >> BEGIN >> >> SELECT TOP @Topn Defects.Defect1, Units.CreateDate, COUNT(*) AS >> Expr1 >> FROM Defects INNER JOIN >> UnitDefects ON Defects.DefectID = > UnitDefects.DefectID >> INNER JOIN >> Units ON UnitDefects.UnitID = Units.UnitID >> GROUP BY Units.CreateDate, Defects.Defect1 >> HAVING (Units.CreateDate BETWEEN @StartDate AND @EndDate) AND (NOT >> (Defects.Defect1 = 'freon charge')) AND (NOT (Defects.Defect1 = 'spc')) >> ORDER BY Expr1 DESC >> >> END >> >> > >
Hi
http://www.sommarskog.se/dynamic_sql.html Regards -------------------------------- Mike Epprecht, Microsoft SQL Server MVP Zurich, Switzerland IM: mike@epprecht.net MVP Program: http://www.microsoft.com/mvp Blog: http://www.msmvps.com/epprecht/ [quoted text, click to view] "Andy Jones" <ajones@rheemac.com> wrote in message news:u0QNxKWMFHA.3540@tk2msftngp13.phx.gbl... > Thanks for the tip. However, I am new to SQL and I do not know what you mean > by dynamic sql? > "Mike Epprecht (SQL MVP)" <mike@epprecht.net> wrote in message > news:%23QVCUsMMFHA.2940@TK2MSFTNGP15.phx.gbl... > > Hi > > > > TOP does not support variables. > > > > SELECT TOP 10 works, SELECT TOP @Topn does not. > > > > You need to use dynamic SQL if you want to do it in the SP. > > > > Regards > > -------------------------------- > > Mike Epprecht, Microsoft SQL Server MVP > > Zurich, Switzerland > > > > IM: mike@epprecht.net > > > > MVP Program: http://www.microsoft.com/mvp > > > > Blog: http://www.msmvps.com/epprecht/ > > > > "Andy Jones" <ajones@rheemac.com> wrote in message > > news:OgZFGlMMFHA.1476@TK2MSFTNGP09.phx.gbl... > >> I am trying to select the top n of a result where the date is a parameter > >> and the top n needs to be a parameter. I keep getting an ado error on my > > sql > >> query. Can someone tell me what I am doing wrong? I would appreciate it. > >> Thanks in advance. > >> > >> > >> if @Topn = ' ' > >> BEGIN > >> SELECT Defects.Defect1, Units.CreateDate, COUNT(*) AS Expr1 > >> FROM Defects INNER JOIN > >> UnitDefects ON Defects.DefectID = > > UnitDefects.DefectID > >> INNER JOIN > >> Units ON UnitDefects.UnitID = Units.UnitID > >> GROUP BY Units.CreateDate, Defects.Defect1 > >> HAVING (Units.CreateDate BETWEEN @StartDate AND @EndDate) AND (NOT > >> (Defects.Defect1 = 'freon charge')) AND (NOT (Defects.Defect1 = 'spc')) > >> ORDER BY Expr1 DESC > >> > >> ELSE > >> > >> BEGIN > >> > >> SELECT TOP @Topn Defects.Defect1, Units.CreateDate, COUNT(*) AS > >> Expr1 > >> FROM Defects INNER JOIN > >> UnitDefects ON Defects.DefectID = > > UnitDefects.DefectID > >> INNER JOIN > >> Units ON UnitDefects.UnitID = Units.UnitID > >> GROUP BY Units.CreateDate, Defects.Defect1 > >> HAVING (Units.CreateDate BETWEEN @StartDate AND @EndDate) AND (NOT > >> (Defects.Defect1 = 'freon charge')) AND (NOT (Defects.Defect1 = 'spc')) > >> ORDER BY Expr1 DESC > >> > >> END > >> > >> > > > > > >
You need to make the @Topn parameter an integer and allow null values, then:
if @Topn is not null set @@rowcount=@Topn SELECT Defects.Defect1, Units.CreateDate, COUNT(*) AS Expr1 FROM Defects INNER JOIN UnitDefects ON Defects.DefectID = UnitDefects.DefectID INNER JOIN Units ON UnitDefects.UnitID = Units.UnitID GROUP BY Units.CreateDate, Defects.Defect1 HAVING (Units.CreateDate BETWEEN @StartDate AND @EndDate) AND (NOT (Defects.Defect1 = 'freon charge')) AND (NOT (Defects.Defect1 = 'spc')) ORDER BY Expr1 DESC -- Mary Bray [SQL Server MVP] Please reply only to newsgroups [quoted text, click to view] "Andy Jones" <ajones@rheemac.com> wrote in message news:eHYJKgWMFHA.1528@TK2MSFTNGP09.phx.gbl... > thanks for the help. I guess this will not work for. I haven't been able > to get past the query analyzer it keeps giving me errors. Thanks again > "Mary Bray [MVP]" <reply@tonewsgroup.com.NOSPAMPLEASE> wrote in message > news:e4QU02PMFHA.3492@TK2MSFTNGP10.phx.gbl... >> You can however set the rowcount using a variable ie: >> if @Topn <> ' ' >> set @@rowcount=@Topn >> >> etc >> >> -- >> >> Mary Bray [SQL Server MVP] >> Please reply only to newsgroups >> >> "Mike Epprecht (SQL MVP)" <mike@epprecht.net> wrote in message >> news:%23QVCUsMMFHA.2940@TK2MSFTNGP15.phx.gbl... >>> Hi >>> >>> TOP does not support variables. >>> >>> SELECT TOP 10 works, SELECT TOP @Topn does not. >>> >>> You need to use dynamic SQL if you want to do it in the SP. >>> >>> Regards >>> -------------------------------- >>> Mike Epprecht, Microsoft SQL Server MVP >>> Zurich, Switzerland >>> >>> IM: mike@epprecht.net >>> >>> MVP Program: http://www.microsoft.com/mvp >>> >>> Blog: http://www.msmvps.com/epprecht/ >>> >>> "Andy Jones" <ajones@rheemac.com> wrote in message >>> news:OgZFGlMMFHA.1476@TK2MSFTNGP09.phx.gbl... >>>> I am trying to select the top n of a result where the date is a >>>> parameter >>>> and the top n needs to be a parameter. I keep getting an ado error on >>>> my >>> sql >>>> query. Can someone tell me what I am doing wrong? I would appreciate >>>> it. >>>> Thanks in advance. >>>> >>>> >>>> if @Topn = ' ' >>>> BEGIN >>>> SELECT Defects.Defect1, Units.CreateDate, COUNT(*) AS Expr1 >>>> FROM Defects INNER JOIN >>>> UnitDefects ON Defects.DefectID = >>> UnitDefects.DefectID >>>> INNER JOIN >>>> Units ON UnitDefects.UnitID = Units.UnitID >>>> GROUP BY Units.CreateDate, Defects.Defect1 >>>> HAVING (Units.CreateDate BETWEEN @StartDate AND @EndDate) AND (NOT >>>> (Defects.Defect1 = 'freon charge')) AND (NOT (Defects.Defect1 = 'spc')) >>>> ORDER BY Expr1 DESC >>>> >>>> ELSE >>>> >>>> BEGIN >>>> >>>> SELECT TOP @Topn Defects.Defect1, Units.CreateDate, COUNT(*) AS >>>> Expr1 >>>> FROM Defects INNER JOIN >>>> UnitDefects ON Defects.DefectID = >>> UnitDefects.DefectID >>>> INNER JOIN >>>> Units ON UnitDefects.UnitID = Units.UnitID >>>> GROUP BY Units.CreateDate, Defects.Defect1 >>>> HAVING (Units.CreateDate BETWEEN @StartDate AND @EndDate) AND (NOT >>>> (Defects.Defect1 = 'freon charge')) AND (NOT (Defects.Defect1 = 'spc')) >>>> ORDER BY Expr1 DESC >>>> >>>> END >>>> >>>> >>> >>> >> >> > >
Mary,
When using the query builder in reporting services I get an ado error when I write the query like below. Do you have any other suggestions for me? I think I am getting desperate. Thanks for all your help though. [quoted text, click to view] "Mary Bray [MVP]" <reply@tonewsgroup.com.NOSPAMPLEASE> wrote in message news:%23v4dLnYMFHA.1176@TK2MSFTNGP12.phx.gbl... > You need to make the @Topn parameter an integer and allow null values, > then: > > if @Topn is not null > set @@rowcount=@Topn > > SELECT Defects.Defect1, Units.CreateDate, COUNT(*) AS Expr1 > FROM Defects INNER JOIN > UnitDefects ON Defects.DefectID = > UnitDefects.DefectID > INNER JOIN > Units ON UnitDefects.UnitID = Units.UnitID > GROUP BY Units.CreateDate, Defects.Defect1 > HAVING (Units.CreateDate BETWEEN @StartDate AND @EndDate) AND (NOT > (Defects.Defect1 = 'freon charge')) AND (NOT (Defects.Defect1 = 'spc')) > ORDER BY Expr1 DESC > > > -- > > Mary Bray [SQL Server MVP] > Please reply only to newsgroups > > "Andy Jones" <ajones@rheemac.com> wrote in message > news:eHYJKgWMFHA.1528@TK2MSFTNGP09.phx.gbl... >> thanks for the help. I guess this will not work for. I haven't been able >> to get past the query analyzer it keeps giving me errors. Thanks again >> "Mary Bray [MVP]" <reply@tonewsgroup.com.NOSPAMPLEASE> wrote in message >> news:e4QU02PMFHA.3492@TK2MSFTNGP10.phx.gbl... >>> You can however set the rowcount using a variable ie: >>> if @Topn <> ' ' >>> set @@rowcount=@Topn >>> >>> etc >>> >>> -- >>> >>> Mary Bray [SQL Server MVP] >>> Please reply only to newsgroups >>> >>> "Mike Epprecht (SQL MVP)" <mike@epprecht.net> wrote in message >>> news:%23QVCUsMMFHA.2940@TK2MSFTNGP15.phx.gbl... >>>> Hi >>>> >>>> TOP does not support variables. >>>> >>>> SELECT TOP 10 works, SELECT TOP @Topn does not. >>>> >>>> You need to use dynamic SQL if you want to do it in the SP. >>>> >>>> Regards >>>> -------------------------------- >>>> Mike Epprecht, Microsoft SQL Server MVP >>>> Zurich, Switzerland >>>> >>>> IM: mike@epprecht.net >>>> >>>> MVP Program: http://www.microsoft.com/mvp >>>> >>>> Blog: http://www.msmvps.com/epprecht/ >>>> >>>> "Andy Jones" <ajones@rheemac.com> wrote in message >>>> news:OgZFGlMMFHA.1476@TK2MSFTNGP09.phx.gbl... >>>>> I am trying to select the top n of a result where the date is a >>>>> parameter >>>>> and the top n needs to be a parameter. I keep getting an ado error on >>>>> my >>>> sql >>>>> query. Can someone tell me what I am doing wrong? I would appreciate >>>>> it. >>>>> Thanks in advance. >>>>> >>>>> >>>>> if @Topn = ' ' >>>>> BEGIN >>>>> SELECT Defects.Defect1, Units.CreateDate, COUNT(*) AS Expr1 >>>>> FROM Defects INNER JOIN >>>>> UnitDefects ON Defects.DefectID = >>>> UnitDefects.DefectID >>>>> INNER JOIN >>>>> Units ON UnitDefects.UnitID = Units.UnitID >>>>> GROUP BY Units.CreateDate, Defects.Defect1 >>>>> HAVING (Units.CreateDate BETWEEN @StartDate AND @EndDate) AND >>>>> (NOT >>>>> (Defects.Defect1 = 'freon charge')) AND (NOT (Defects.Defect1 = >>>>> 'spc')) >>>>> ORDER BY Expr1 DESC >>>>> >>>>> ELSE >>>>> >>>>> BEGIN >>>>> >>>>> SELECT TOP @Topn Defects.Defect1, Units.CreateDate, COUNT(*) AS >>>>> Expr1 >>>>> FROM Defects INNER JOIN >>>>> UnitDefects ON Defects.DefectID = >>>> UnitDefects.DefectID >>>>> INNER JOIN >>>>> Units ON UnitDefects.UnitID = Units.UnitID >>>>> GROUP BY Units.CreateDate, Defects.Defect1 >>>>> HAVING (Units.CreateDate BETWEEN @StartDate AND @EndDate) AND >>>>> (NOT >>>>> (Defects.Defect1 = 'freon charge')) AND (NOT (Defects.Defect1 = >>>>> 'spc')) >>>>> ORDER BY Expr1 DESC >>>>> >>>>> END >>>>> >>>>> >>>> >>>> >>> >>> >> >> > >
Don't see what you're looking for? Try a search.
|
June 2004
July 2004
August 2004
September 2004
October 2004
November 2004
December 2004
January 2005
February 2005
March 2005
April 2005
May 2005
June 2005
July 2005
August 2005
September 2005
October 2005
November 2005
December 2005
January 2006
February 2006
March 2006
April 2006
May 2006
June 2006
July 2006
August 2006
September 2006
October 2006
November 2006
December 2006
January 2007
February 2007
March 2007
April 2007
May 2007
June 2007
July 2007
August 2007
September 2007
October 2007
November 2007
| home · blog · groups | Questions? Comments? Contact the dn |