SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


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


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

Author
Message
Mile Higher Than Sea Level
Mile Higher Than Sea Level
Old Hand
Old Hand (356 reputation)Old Hand (356 reputation)Old Hand (356 reputation)Old Hand (356 reputation)Old Hand (356 reputation)Old Hand (356 reputation)Old Hand (356 reputation)Old Hand (356 reputation)

Group: General Forum Members
Points: 356 Visits: 465
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"));
Mile Higher Than Sea Level
Mile Higher Than Sea Level
Old Hand
Old Hand (356 reputation)Old Hand (356 reputation)Old Hand (356 reputation)Old Hand (356 reputation)Old Hand (356 reputation)Old Hand (356 reputation)Old Hand (356 reputation)Old Hand (356 reputation)

Group: General Forum Members
Points: 356 Visits: 465
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)))
Mile Higher Than Sea Level
Mile Higher Than Sea Level
Old Hand
Old Hand (356 reputation)Old Hand (356 reputation)Old Hand (356 reputation)Old Hand (356 reputation)Old Hand (356 reputation)Old Hand (356 reputation)Old Hand (356 reputation)Old Hand (356 reputation)

Group: General Forum Members
Points: 356 Visits: 465
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))
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search