[dba-SQLServer]Sproc - Selecting from select statement

Thanks for your response.  The problem WAS aliasing.

David

At 10/09/2003, you wrote:
>Also, you arent aliasing the inner Select statement:
>
>SELECT @qs =    'SELECT A.GateIDNo, A.MaxMDQ, A.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'')) AS A'
>
>although I don't think that its really the problem.

>David McAfee
>-----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 6: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