SQL to convert array values into query output 1

[teach314]

hello

I'm getting bogged down trying to write some SQL.

I have an array of distinct integer values, Arr(row,col). Rows run from 0 to 4, columns run from 0 to 3. Here is the complete array:

18 42 85 95
71 65 91 87
37 61 17 10
84 98 16 70
19 23 24 52

I need to create a query that outputs 4^5 = 1024 rows like...

18 71 37 84 19
18 71 37 84 23
18 71 37 84 24
... ... ... ... ...
95 87 10 70 23
95 87 10 70 24
95 87 10 70 52

Each row of the query takes one value from each row of the array.

Can anyone offer some assistance?

Thank you

 

[MajP]

AFAIK jet sql has no ability to query arrays. I am no SQL expert, but my guess that is not possible in pure sql.

However, reading an array and writing the solution to a table would be pretty trivial. Does it have to be a query? If the number of rows is known the solution is much easier than if the number of rows can change.

 

[teach314]

hi MajP - Thanks for replying. No, it doesn't have to be a query - a table would be fine. I could still use a few pointers if that's OK. The solution has exactly 1024 rows.

 

[MajP]

Assuming the dimensions or your arrays are exactly like described. If the dimension of the source array are variable then this problem becomes pretty hard. I would have to think a lot harder.

Assume you already have a tblTemp with fields val1,... val5 for your columns. You could build the table in code and delete it after it is used. I did not bother with that because that is trivial and not the challenging part. The harder part is permuting every item in each row with the items in the other rows.

Public Function PermuteArray(arr() As Integer) As Variant
'pass in a 5 x 4 matrix and return a 1024 X 5
  Dim I As Integer
  Dim J As Integer
  Dim K As Integer
  Dim L As Integer
  Dim M As Integer
  Dim val0 As Integer
  Dim val1 As Integer
  Dim val2 As Integer
  Dim val3 As Integer
  Dim val4 As Integer
  Dim newArray(1023, 4) As Integer
  Dim row As Integer
  
  For I = 0 To 3
       val0 = arr(0, I)
     For J = 0 To 3
        val1 = arr(1, J)
        For K = 0 To 3
           val2 = arr(2, K)
           For L = 0 To 3
              val3 = arr(3, L)
              For M = 0 To 3
                val4 = arr(4, M)
                newArray(row, 0) = val0
                newArray(row, 1) = val1
                newArray(row, 2) = val2
                newArray(row, 3) = val3
                newArray(row, 4) = val4
                row = row + 1
              Next M
                M = 0
           Next L
             L = 0
        Next K
          K = 0
     Next J
       J = 0
  Next I
  PermuteArray = newArray()
End Function


Public Sub PermuteToTable()
  Dim arr() As Integer
  Dim pArr() As Integer
  Dim row As Integer
  Dim col As Integer
  Dim tempString As String
  Dim strSql As String
  
  arr = getTestArray
  pArr = PermuteArray(arr)
  For row = 0 To 1023
    For col = 0 To 4
      If col = 0 Then
        tempString = pArr(row, col)
      Else
        tempString = tempString & ", " & pArr(row, col)
      End If
      If col = 4 Then
        strSql = "INSERT INTO tblTemp (val1,val2,val3,val4,val5) values (" & tempString & ")"
        'Debug.Print strSql
        CurrentDb.Execute strSql
      End If
    Next col
    col = 0
    tempString = ""
    strSql = ""
   Next row
  
End Sub

Public Function getTestArray() As Integer()
  'used to generate the test array
  Dim arr(4, 3) As Integer
  arr(0, 0) = 18
  arr(0, 1) = 42
  arr(0, 2) = 85
  arr(0, 3) = 95
  arr(1, 0) = 71
  arr(1, 1) = 65
  arr(1, 2) = 91
  arr(1, 3) = 87
  arr(2, 0) = 37
  arr(2, 1) = 61
  arr(2, 2) = 17
  arr(2, 3) = 10
  arr(3, 0) = 84
  arr(3, 1) = 98
  arr(3, 2) = 16
  arr(3, 3) = 70
  arr(4, 0) = 19
  arr(4, 1) = 23
  arr(4, 2) = 24
  arr(4, 3) = 52
  getTestArray = arr()

End Function

What is the purpose of this and why even have arrays? If I am working in Access I can not think of any good reason to work in arrays. The source data should be in a table or recordset. A table pretty much looks like a rectangular array to me. Permuting a 5X4 matrix stored in a table would be a couple lines of code and a few supporting queries.

 

[MajP]

If you can get you data into a table transposed by row and column (this can be done by code) then you can run a query.
tblPermuteData

val1	val2	val3	val4	val5
18	71	37	84	19
42	65	61	98	23
85	91	17	16	24
95	87	10	17	52

SELECT 
 A.val1, 
 B.val2,
 C.val3,
 D.val4,
 E.val5
FROM 
 tblPermuteData AS A, 
 tblPermuteData AS B, 
 tblPermuteData AS C,
 tblPermuteData AS D,
 tblPermuteData AS E
ORDER BY
 1,
 2,
 3,
 4,
 5

 

[teach314]

MajP - that's really efficient. I'm going to adopt this technique. Thanks much!

 

[MajP]

How will you get your initial array into the transposed table? Or can you just start with the transposed table? You never said where the initial array comes from.

Note. The above solution uses the concept of Cartesian products. Whenever you add two or more tables to a query and do not specify a join, it forms a Cartesian product. For each record in A it returns every record in B.