[dba-SQLServer]Sproc - Selecting from select statement

David:

You need an alias for your subquery.

Try 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'')) yo_table_name'

HTH
Billy


>From: "Mike and Doris Manning" <mikedorism at ntelos.net>
>Reply-To: dba-sqlserver at databaseadvisors.com
>To: <dba-sqlserver at databaseadvisors.com>
>Subject: RE: [dba-SQLServer]Sproc - Selecting from select statement
>Date: Thu, 11 Sep 2003 06:25:18 -0400
>
>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.
>www.hargroveinc.com <http://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
>

_________________________________________________________________
The new MSN 8: smart spam protection and 2 months FREE*  
http://join.msn.com/?page=features/junkmail