Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Converting MSAccess SQL to TSQL - newbie - Replace Access DMin function with TSQL Min Expand / Collapse
Author
Message
Posted Wednesday, October 17, 2012 3:44 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Saturday, August 16, 2014 11:16 AM
Points: 123, Visits: 345
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
Posted Thursday, October 18, 2012 8:48 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Saturday, August 16, 2014 11:16 AM
Points: 123, Visits: 345
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
Posted Friday, October 19, 2012 8:51 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Saturday, August 16, 2014 11:16 AM
Points: 123, Visits: 345
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 »

Add to briefcase

Permissions Expand / Collapse