[dba-SQLServer]Sproc - Selecting from select statement

David Emerson davide at dalyn.co.nz
Wed Sep 10 20:19:25 CDT 2003


I am trying to use a select statement as the source of another select 
statement in a Sproc.

The following statement works -

SELECT GateIDNo, MAX(MDQ) AS MaxMDQ, MAX(MDQDate) AS OldMDQDate
         FROM dbo.tblGateStationMDQ
         WHERE MDQ <> 0
         GROUP BY GateIDNo
         HAVING (MAX(MDQDate) < '20031001')

But when I do the following -

SELECT @qs =    'SELECT GateIDNo, MaxMDQ, OldMDQDate
         FROM (SELECT GateIDNo, MAX(MDQ) AS MaxMDQ, MAX(MDQDate) AS OldMDQDate
                         FROM dbo.tblGateStationMDQ
                         WHERE MDQ <> 0
                         GROUP BY GateIDNo
                         HAVING (MAX(MDQDate) < ''20031001''))'

I get the following error -

Server: Msg 170, Level 15, State 1, Line 6
Line 6: Incorrect syntax near ')'.

Using Print (@qs) I get -

SELECT GateIDNo, MaxMDQ, OldMDQDate
         FROM (SELECT GateIDNo, MAX(MDQ) AS MaxMDQ, MAX(MDQDate) as OldMDQDate
                         FROM dbo.tblGateStationMDQ
                         WHERE MDQ <> 0
                         GROUP BY GateIDNo
                         HAVING (MAX(MDQDate) < '20031001'))

Any suggestions?

Regards

David Emerson
DALYN Software Ltd
25b Cunliffe St, Johnsonville
Wellington, New Zealand
Ph/Fax (877) 456-1205 
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://databaseadvisors.com/pipermail/dba-sqlserver/attachments/20030911/7e61b7c3/attachment.html>


More information about the dba-SQLServer mailing list