Help converting Access Param Query to SQL Server

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Help converting Access Param Query to SQL Server

Author

Topic

BulletSponge
Starting Member

2 Posts

Posted - 2007-05-01 : 12:49:44

I've got a Test Access database that has some parameter queries
first one is simple;

***** QRY-1
SELECT x, y, Min(z) AS NextDate
FROM A
WHERE z >=[@BaseDate]
GROUP BY x, y

***** QRY-2
SELECT A.x, A.y, B.m B.n
FROM
(QRY-1 INNER JOIN B ON QRY-1.x= B.x)
INNER JOIN A ON (QRY-1.x = A.x)
AND (QRY-1.y = A.y)
AND (QRY-1.NextDate = A.z)
ORDER BY A.x, A.y

---- OK, now in Access, when I open QRY-2, it asks for a @BaseDate, which is set, and it returns what I want...
In SQL-Server, it doesn't work. You have any ideas why?
Here's the SQL-Server crap

CREATE PROCEDURE QRY-2(@BaseDate as smalldatetime)
AS

SELECT
A.x, A.y, B.m, B.n
FROM
[QRY-1] INNER JOIN [B] ON [QRY-1].[x] = [B].[x])
INNER JOIN [A] ON ([QRY-1].[x] = [A].[x])
AND ([QRY-1].[y] = [A].[y])
AND ([QRY-1].[NextDate] = [A].[z])
ORDER BY
A.x, A.y
GO

Looks the same right? So it should work right?
It gives me "Invalid object name 'QRY-1'." instead

::EDIT::
I found this which also describes my problem, but I'm trying to avoid a temporary table;
http://www.thescripts.com/forum/thread494349.html

mattyblah
Starting Member

49 Posts

Posted - 2007-05-01 : 18:13:17

try this:

SELECT A.x, A.y, B.m B.n
FROM
(SELECT x, y, Min(z) AS NextDate
FROM A
WHERE z >=[@BaseDate]
GROUP BY x, y) qry_1
INNER JOIN B ON QRY_1.x= B.x
INNER JOIN A ON (QRY_1.x = A.x)
AND (QRY_1.y = A.y)
AND (QRY_1.NextDate = A.z)
ORDER BY A.x, A.y

let me know how that works out.

BulletSponge
Starting Member

2 Posts

Posted - 2007-05-01 : 21:17:17

That got it.

I guess sometimes it doesn't pay to keep things clean

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources