<html>
<body>
I am trying to use a select statement as the source of another select
statement in a Sproc.<br><br>
The following statement works -<br><br>
<font face="Tahoma">SELECT GateIDNo, MAX(MDQ) AS MaxMDQ, MAX(MDQDate) AS
OldMDQDate<br>
<x-tab> </x-tab>FROM
dbo.tblGateStationMDQ<br>
<x-tab> </x-tab>WHERE MDQ
<> 0 <br>
<x-tab> </x-tab>GROUP BY
GateIDNo<br>
<x-tab> </x-tab>HAVING
(MAX(MDQDate) < '20031001')<br><br>
But when I do the following -<br><br>
SELECT @qs =<x-tab> </x-tab>'SELECT GateIDNo,
MaxMDQ, OldMDQDate<br>
<x-tab> </x-tab>FROM
(SELECT GateIDNo, MAX(MDQ) AS MaxMDQ, MAX(MDQDate) AS OldMDQDate<br>
<x-tab> </x-tab><x-tab> </x-tab><x-tab> </x-tab>FROM
dbo.tblGateStationMDQ<br>
<x-tab> </x-tab><x-tab> </x-tab><x-tab> </x-tab>WHERE
MDQ <> 0 <br>
<x-tab> </x-tab><x-tab> </x-tab><x-tab> </x-tab>GROUP
BY GateIDNo<br>
<x-tab> </x-tab><x-tab> </x-tab><x-tab> </x-tab>HAVING
(MAX(MDQDate) < ''20031001''))'<br><br>
</font>I get the following error -<br><br>
Server: Msg 170, Level 15, State 1, Line 6<br>
Line 6: Incorrect syntax near ')'.<br><br>
Using Print (@qs) I get -<br><br>
SELECT GateIDNo, <font face="Tahoma">Max</font>MDQ, OldMDQDate<br>
<x-tab> </x-tab>FROM
(SELECT GateIDNo, MAX(MDQ) AS <font face="Tahoma">Max</font>MDQ,
MAX(MDQDate) as OldMDQDate<br>
<x-tab> </x-tab><x-tab> </x-tab><x-tab> </x-tab>FROM
dbo.tblGateStationMDQ<br>
<x-tab> </x-tab><x-tab> </x-tab><x-tab> </x-tab>WHERE
MDQ <> 0 <br>
<x-tab> </x-tab><x-tab> </x-tab><x-tab> </x-tab>GROUP
BY GateIDNo<br>
<x-tab> </x-tab><x-tab> </x-tab><x-tab> </x-tab>HAVING
(MAX(MDQDate) < '20031001'))<br><br>
Any suggestions?<br>
<x-sigsep><p></x-sigsep>
Regards<br><br>
David Emerson<br>
DALYN Software Ltd<br>
25b Cunliffe St, Johnsonville<br>
Wellington, New Zealand<br>
Ph/Fax (877) 456-1205</body>
</html>