Converting MSAccess SQL to TSQL - newbie - Replace Access DMin function with TSQL Min

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » T-SQL (SS2K8) » Converting MSAccess SQL to TSQL - newbie -...

Converting MSAccess SQL to TSQL - newbie - Replace Access DMin function with TSQL Min

Rate Topic

Display Mode

Topic Options

Author

Message

Mile Higher Than Sea Level

Posted Wednesday, October 17, 2012 3:44 PM

Valued Member

Group: General Forum Members
Last Login: Wednesday, May 08, 2013 11:59 AM
Points: 72, Visits: 223

Can someone give me some support in migrating this to SQL Server? The Req_Fin_Priorities are numeric (1, 2, or 3 no nulls).

In Access, the DMin function returns the minimum value in a specified set of records (or domain). The syntax for the DMin function is: DMin ( expression, domain, [criteria] )
In TSQL - it appears to need: SELECT Min(column) FROM table WHERE condition

Access SQL Sub_2 (works)
SELECT sub_1.ID_Wells, sub_1.Req_Fin_Priority AS MinPriority, sub_1.ID_SHLBHL
FROM sub_1
WHERE (((sub_1.Req_Fin_Priority)=DMin("[Req_Fin_Priority]","sub_1","[ID_Wells]=" & [ID_Wells])));

Access SQL sub_1 (works)
SELECT Wells_SHLBHL.ID_Wells, Req_Fin_Priorities.Req_Fin_Priority, Wells_SHLBHL.Req_Fin, Wells_SHLBHL.ID_SHLBHL, Wells_SHLBHL.SHLBHL
FROM Wells_SHLBHL INNER JOIN Req_Fin_Priorities ON Wells_SHLBHL.Req_Fin = Req_Fin_Priorities.Req_Fin_Type
WHERE (((Wells_SHLBHL.SHLBHL)="SHL"));

Post #1374071

Mile Higher Than Sea Level

Posted Thursday, October 18, 2012 8:48 AM

Valued Member

Group: General Forum Members
Last Login: Wednesday, May 08, 2013 11:59 AM
Points: 72, Visits: 223

Slugged through it and came up with this.

SELECT ID_Wells, Req_Fin_Priority AS MinPriority, ID_SHLBHL, Req_Fin, SHLBHL
FROM dbo.Sub_1
WHERE (Req_Fin_Priority =
(SELECT MIN(Req_Fin_Priority) AS Expr1
FROM dbo.Sub_1 AS Sub_1_1
WHERE (dbo.Sub_1.ID_Wells = ID_Wells)))

Post #1374415

Mile Higher Than Sea Level

Posted Friday, October 19, 2012 8:51 AM

Valued Member

Group: General Forum Members
Last Login: Wednesday, May 08, 2013 11:59 AM
Points: 72, Visits: 223

On the MS Access UK site, another suggestion was this.
It provided the exact same results as my solution above.
Did not see any difference in the time for 100,000 records.
It uses the TOP and Order By
Thought it would be good to share this too.
MS Access developers could use this solution and be able to migrate to T-SQL easily.

SELECT ID_Wells, ID_SHLBHL, Req_Fin_Priority AS MinPriority, Req_Fin, SHLBHL
FROM dbo.Sub_1
WHERE (Req_Fin_Priority =
(SELECT TOP (1) Req_Fin_Priority AS Expr1
FROM dbo.Sub_1 AS Sub_1_1
WHERE (dbo.Sub_1.ID_Wells = ID_Wells)
ORDER BY Expr1))

Post #1374867

« Prev Topic | Next Topic »

Permissions