none
How to write function in SQL - source in VBA

    Question

  • 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.

    Saturday, November 13, 2010 6:50 PM

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/en-us/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/en-us/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/en-us/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/en-us/library/ms181984.aspx

    5. For loop control in SQL, you can refer to http://msdn.microsoft.com/en-us/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.

    Thursday, November 18, 2010 4:10 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! * (n-k)!)


    In permutations, the order matters: 


    P(n,k) = n! /((n-k)!)


    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 N-elements

    (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 N-elements AS N1, 

           N-elements AS N2, 

           N-elements 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 N-elements AS N1, 

           N-elements AS N2, 

           N-elements AS N3

     WHERE N1.n < N2.n

       AND N2.n < N3.n;

     


    --CELKO-- Please post DDL, use ISO-11179 naming rules and format code so we can read it
    Friday, November 19, 2010 9:03 PM

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/en-us/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/en-us/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/en-us/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/en-us/library/ms181984.aspx

    5. For loop control in SQL, you can refer to http://msdn.microsoft.com/en-us/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.

    Thursday, November 18, 2010 4:10 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.

    Friday, November 19, 2010 5:49 PM
  • 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! * (n-k)!)


    In permutations, the order matters: 


    P(n,k) = n! /((n-k)!)


    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 N-elements

    (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 N-elements AS N1, 

           N-elements AS N2, 

           N-elements 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 N-elements AS N1, 

           N-elements AS N2, 

           N-elements AS N3

     WHERE N1.n < N2.n

       AND N2.n < N3.n;

     


    --CELKO-- Please post DDL, use ISO-11179 naming rules and format code so we can read it
    Friday, November 19, 2010 9:03 PM