[dba-SQLServer]Sproc - Selecting from select statement

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.

Thanks for responding.

David

At 11/09/2003, you wrote:
>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?
>
>Doris Manning
>Database Administrator
>Hargrove Inc.
><http://www.hargroveinc.com/>www.hargroveinc.com
>-----Original Message-----
>From: dba-sqlserver-bounces at databaseadvisors.com 
>[mailto:dba-sqlserver-bounces at databaseadvisors.com] On Behalf Of David Emerson
>Sent: Wednesday, September 10, 2003 9:19 PM
>To: dba-SQLServer at databaseadvisors.com
>Subject: [dba-SQLServer]Sproc - Selecting from select statement
>
>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
>
>_______________________________________________
>dba-SQLServer mailing list
>dba-SQLServer at databaseadvisors.com
>http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
>http://www.databaseadvisors.com
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://databaseadvisors.com/pipermail/dba-sqlserver/attachments/20030912/c9e0c416/attachment.html>