Answered by:
How to write function in SQL  source in VBA

I have a function written in VBA. This function works fine.
Public Function InsertPairs(ByVal n As Integer, ByVal k As Integer, ByVal TableName As String) As Integer Dim max As Double Dim idx() As Double Dim i, j, m, kt, t As Integer Dim strLine As String Dim strCmd As String Dim asNumbers() As String max = Ckn(n, k) If max < 1 Then InsertPairs = 1 Exit Function End If ReDim idx(k  1) For i = 1 To k idx(i  1) = i Next i = 1 m = UBound(idx) Do strLine = "" For j = LBound(idx) To UBound(idx) strLine = strLine & CStr(idx(j)) & "," Next strLine = VBA.Left(strLine, Len(strLine)  1) strCmd = "INSERT INTO " & TableName & " (" For t = 1 To k strCmd = strCmd & "L" & VBA.Trim$(VBA.Str$(t)) & "," Next t strCmd = VBA.Left(strCmd, Len(strCmd)  1) strCmd = strCmd & ") VALUES (" & strLine & ")" DoCmd.RunSQL strCmd m = UBound(idx) kt = 1 If max = 1 Then Exit Do If idx(UBound(idx)) = n Then test: idx(m  kt) = idx(m  kt) + 1 For j = kt  1 To 0 Step 1 idx(m  j) = idx(m  j  1) + 1 Next If idx(m) > n Then kt = kt + 1 GoTo test End If Else idx(UBound(idx)) = idx(UBound(idx)) + 1 End If max = max  1 i = i + 1 Loop While max > 0 End Function
How to convert this function to SQL language
Ckn function calculate combination k elements from n and I already have this function written in SQL. The function written in VBA writes all combination in to table.
 Moved by Mangal PardeshiModerator Tuesday, November 16, 2010 1:21 PM (From:Data Mining)
Question
Answers

Hi TheTrace,
Most people in this forum are not VBA experts. Could you please let us know what the difficulties you were facing when convert this VBA function to SQL?
Currently I would like to give you some hints on the conversion:
1. Implement a SQL stored procedure to replace this function, http://msdn.microsoft.com/enus/library/ms187926.aspx.
2. For array in your function, declare a table variable with one colume. You can refer to this article for a table variable declaration http://msdn.microsoft.com/enus/library/ms188927.aspx.
3. You can declare a varchar(max) typed variable in SQL for your "strCmd" variable, and then use "exec sp_executeSQL @strSQL" to execute the dynamic SQL, http://msdn.microsoft.com/enus/library/ms188001.aspx.
4. For string functions regarding trim, left, and right etc in SQL, you can refer to this article, http://msdn.microsoft.com/enus/library/ms181984.aspx
5. For loop control in SQL, you can refer to http://msdn.microsoft.com/enus/library/ms178642.aspx
Please try to convert your function to SQL based on the above hints. If you encounter any difficulty, please post it out so that we can give you further assistance.
 Proposed as answer by Naomi NModerator Thursday, November 18, 2010 4:22 AM
 Marked as answer by Kalman TothModerator Thursday, November 25, 2010 5:50 AM

Choose function calculates the combinations k elements from n .. writes all combination into a table.
For other readers, here are the Combination and Permutation formulas:
In combinations, the order does not matters:
C(n,k) = n! /(k! * (nk)!)
In permutations, the order matters:
P(n,k) = n! /((nk)!)
SQL is a declarative language, so we do not want loops or procedural code. Firsts we need to create the big set of the (n) elements:
CREATE TABLE Nelements
(n CHAR(1) NOT NULL PRIMARY KEY);
If we display the combinations or permutations in a row, need to know (k). Let's go with (k=3) and start with Permutations. While there are more of them, they are easy to do
CREATE TABLE Perms_3
(k1 CHAR(1) NOT NULL,
k2 CHAR(1) NOT NULL,
k3 CHAR(1) NOT NULL,
PRIMARY KEY (k1, k2, k3));
INSERT INTO Perms_3 (k1, k2, k3)
SELECT DISTINCT N1.n, N2.n, N3.n
FROM Nelements AS N1,
Nelements AS N2,
Nelements AS N3;
We do the same CROSS JOIN for combinations, but we sort the data :
CREATE TABLE Comb_3
(k1 CHAR(1) NOT NULL,
k2 CHAR(1) NOT NULL,
k3 CHAR(1) NOT NULL,
PRIMARY KEY (k1, k2, k3));
INSERT INTO Comb_3 (k1, k2, k3)
SELECT DISTINCT N1.n, N2.n, N3.n
FROM Nelements AS N1,
Nelements AS N2,
Nelements AS N3
WHERE N1.n < N2.n
AND N2.n < N3.n;
CELKO Please post DDL, use ISO11179 naming rules and format code so we can read it Proposed as answer by Naomi NModerator Friday, November 19, 2010 9:10 PM
 Marked as answer by Wojciech Wojtulewski Saturday, November 20, 2010 9:40 AM
All replies

Hi TheTrace,
Most people in this forum are not VBA experts. Could you please let us know what the difficulties you were facing when convert this VBA function to SQL?
Currently I would like to give you some hints on the conversion:
1. Implement a SQL stored procedure to replace this function, http://msdn.microsoft.com/enus/library/ms187926.aspx.
2. For array in your function, declare a table variable with one colume. You can refer to this article for a table variable declaration http://msdn.microsoft.com/enus/library/ms188927.aspx.
3. You can declare a varchar(max) typed variable in SQL for your "strCmd" variable, and then use "exec sp_executeSQL @strSQL" to execute the dynamic SQL, http://msdn.microsoft.com/enus/library/ms188001.aspx.
4. For string functions regarding trim, left, and right etc in SQL, you can refer to this article, http://msdn.microsoft.com/enus/library/ms181984.aspx
5. For loop control in SQL, you can refer to http://msdn.microsoft.com/enus/library/ms178642.aspx
Please try to convert your function to SQL based on the above hints. If you encounter any difficulty, please post it out so that we can give you further assistance.
 Proposed as answer by Naomi NModerator Thursday, November 18, 2010 4:22 AM
 Marked as answer by Kalman TothModerator Thursday, November 25, 2010 5:50 AM

the procedure generate combination all k elements from n, so it's typical statistic problem and I'd like some procedure that can do this in sql. So if I pass values k=2 n=6 I'd like to have result like this
1,2
1,3
1,4
1,5
1,6
2,3
2,4
...
5,6
So I need some help how to write a function in SQL.

Choose function calculates the combinations k elements from n .. writes all combination into a table.
For other readers, here are the Combination and Permutation formulas:
In combinations, the order does not matters:
C(n,k) = n! /(k! * (nk)!)
In permutations, the order matters:
P(n,k) = n! /((nk)!)
SQL is a declarative language, so we do not want loops or procedural code. Firsts we need to create the big set of the (n) elements:
CREATE TABLE Nelements
(n CHAR(1) NOT NULL PRIMARY KEY);
If we display the combinations or permutations in a row, need to know (k). Let's go with (k=3) and start with Permutations. While there are more of them, they are easy to do
CREATE TABLE Perms_3
(k1 CHAR(1) NOT NULL,
k2 CHAR(1) NOT NULL,
k3 CHAR(1) NOT NULL,
PRIMARY KEY (k1, k2, k3));
INSERT INTO Perms_3 (k1, k2, k3)
SELECT DISTINCT N1.n, N2.n, N3.n
FROM Nelements AS N1,
Nelements AS N2,
Nelements AS N3;
We do the same CROSS JOIN for combinations, but we sort the data :
CREATE TABLE Comb_3
(k1 CHAR(1) NOT NULL,
k2 CHAR(1) NOT NULL,
k3 CHAR(1) NOT NULL,
PRIMARY KEY (k1, k2, k3));
INSERT INTO Comb_3 (k1, k2, k3)
SELECT DISTINCT N1.n, N2.n, N3.n
FROM Nelements AS N1,
Nelements AS N2,
Nelements AS N3
WHERE N1.n < N2.n
AND N2.n < N3.n;
CELKO Please post DDL, use ISO11179 naming rules and format code so we can read it Proposed as answer by Naomi NModerator Friday, November 19, 2010 9:10 PM
 Marked as answer by Wojciech Wojtulewski Saturday, November 20, 2010 9:40 AM