<html>
<body>
I was using @qs to see what the statement would evaluate to. In the
end it was aliasing the from select statement that caused the
problem.<br><br>
Thanks for responding.<br><br>
David<br><br>
At 11/09/2003, you wrote:<br>
<blockquote type=cite class=cite cite><font face="arial" size=2 color="#0000FF">Maybe
it would help if you would explain more about why you are trying to do
this. How is "@qs" declared and what are you trying to do
with it?</font><br>
<br>
<font face="arial" size=2>Doris Manning</font><br>
<font face="arial" size=2>Database Administrator</font><br>
<font face="arial" size=2>Hargrove Inc.</font><br>
<font face="arial" size=2><a href="http://www.hargroveinc.com/">www.hargroveinc.com</a></font><br>
<dl>
<dd><font face="tahoma" size=2>-----Original Message-----<br>
<dd>From:</b> dba-sqlserver-bounces@databaseadvisors.com
[<a href="mailto:dba-sqlserver-bounces@databaseadvisors.com" eudora="autourl">mailto:dba-sqlserver-bounces@databaseadvisors.com</a>]
On Behalf Of </b>David Emerson<br>
<dd>Sent:</b> Wednesday, September 10, 2003 9:19 PM<br>
<dd>To:</b> dba-SQLServer@databaseadvisors.com<br>
<dd>Subject:</b> [dba-SQLServer]Sproc - Selecting from select
statement<br><br>
</font>
<dd>I am trying to use a select statement as the source of another select
statement in a Sproc.<br><br>
<dd>The following statement works -<br><br>
<dd><font face="tahoma">SELECT GateIDNo, MAX(MDQ) AS MaxMDQ, MAX(MDQDate)
AS OldMDQDate<br>
<dd><x-tab> </x-tab>FROM
dbo.tblGateStationMDQ<br>
<dd><x-tab> </x-tab>WHERE
MDQ <> 0 <br>
<dd><x-tab> </x-tab>GROUP
BY GateIDNo<br>
<dd><x-tab> </x-tab>HAVING
(MAX(MDQDate) < '20031001')<br><br>
<dd>But when I do the following -<br><br>
<dd>SELECT @qs =<x-tab> </x-tab>'SELECT GateIDNo,
MaxMDQ, OldMDQDate<br>
<dd><x-tab> </x-tab>FROM
(SELECT GateIDNo, MAX(MDQ) AS MaxMDQ, MAX(MDQDate) AS OldMDQDate<br>
<dd><x-tab> </x-tab><x-tab> </x-tab><x-tab> </x-tab>FROM
dbo.tblGateStationMDQ<br>
<dd><x-tab> </x-tab><x-tab> </x-tab><x-tab> </x-tab>WHERE
MDQ <> 0 <br>
<dd><x-tab> </x-tab><x-tab> </x-tab><x-tab> </x-tab>GROUP
BY GateIDNo<br>
<dd><x-tab> </x-tab><x-tab> </x-tab><x-tab> </x-tab>HAVING
(MAX(MDQDate) < ''20031001''))'<br><br>
</font>
<dd>I get the following error -<br><br>
<dd>Server: Msg 170, Level 15, State 1, Line 6<br>
<dd>Line 6: Incorrect syntax near ')'.<br><br>
<dd>Using Print (@qs) I get -<br><br>
<dd>SELECT GateIDNo, <font face="tahoma">Max</font>MDQ, OldMDQDate<br>
<dd><x-tab> </x-tab>FROM
(SELECT GateIDNo, MAX(MDQ) AS <font face="tahoma">Max</font>MDQ,
MAX(MDQDate) as OldMDQDate<br>
<dd><x-tab> </x-tab><x-tab> </x-tab><x-tab> </x-tab>FROM
dbo.tblGateStationMDQ<br>
<dd><x-tab> </x-tab><x-tab> </x-tab><x-tab> </x-tab>WHERE
MDQ <> 0 <br>
<dd><x-tab> </x-tab><x-tab> </x-tab><x-tab> </x-tab>GROUP
BY GateIDNo<br>
<dd><x-tab> </x-tab><x-tab> </x-tab><x-tab> </x-tab>HAVING
(MAX(MDQDate) < '20031001'))<br><br>
<dd>Any suggestions?<br><br>
<dd>Regards<br><br>
<dd>David Emerson<br>
<dd>DALYN Software Ltd<br>
<dd>25b Cunliffe St, Johnsonville<br>
<dd>Wellington, New Zealand<br>
<dd>Ph/Fax (877) 456-1205 <br><br>
</dl>_______________________________________________<br>
dba-SQLServer mailing list<br>
dba-SQLServer@databaseadvisors.com<br>
<a href="http://databaseadvisors.com/mailman/listinfo/dba-sqlserver" eudora="autourl">http://databaseadvisors.com/mailman/listinfo/dba-sqlserver</a><br>
<a href="http://www.databaseadvisors.com/" eudora="autourl">http://www.databaseadvisors.com</a></blockquote></body>
</html>