Convert OR to UNION

[RB Smissaert]

I am trying to make a function to convert a query with one or multiple OR
conditions to a query with one or multiple
UNION's. The reason is that in my particular database (Interbase 5.6) the
UNION construction is a lot faster.

So, for example:

select
field
from
table
where
field like 'a%' or field like 'b%'

to

select
field
from
table
where
field like 'a%'
union
select
field
from
table
where
field like 'b%'

This is a simple example, but it can get more complex when there are NOT
conditions or IN constructions.

I was thinking maybe somebody of this NG has done this function already,
saving me reinventing the wheel.
It has to be coded in VB/VBA. I would like it to be a function that has the
orginal SQL as a string argument and the
new UNION SQL as the string result. I had a good search for this on the net,
but nil found.
Thanks.

RBS

 

[Guest]

You are talking apples and cocunuts or I am bananas.
Or is used in a query as part of the WHERE condition.
Union is used to append queries together.

Give an example of your expected input data and then expected output.

 

[RB Smissaert]

Give an example of your expected input data and then expected output

Not saying you are bananas, but that is exactly what I did.

RBS

 

[RB Smissaert]

OK, if anybody is interested in this I have put together a function that
works
for my particular situation where the OR applies to a particular field.
Not sure if Access is clever enough to automatically convert to the faster
method.


Function ConvertOr2Union(strSQL As String, _
Optional bUnionAll As Boolean = True) As String

Dim i As Long
Dim c As Long
Dim x As Long
Dim arrORS
Dim arrORSWithRead
Dim arrSQL
Dim strUnion As String
Dim strSQLUnion As String
Dim lWHEREPos As Long
Dim strAfterWHERE As String
Dim lStart As Long
Dim lEnd As Long
Dim strReadPart As String
Dim lUBReadBits As Long

If bUnionAll Then
strUnion = " UNION ALL "
Else
'this will remove duplicate rows and will be slower
'--------------------------------------------------
strUnion = " UNION "
End If

'get the OR parts after the WHERE
'--------------------------------
lWHEREPos = InStr(1, strSQL, "WHERE", vbTextCompare)
strAfterWHERE = Mid$(strSQL, lWHEREPos + 5)
arrORS = Split(strAfterWHERE, "OR", , vbTextCompare)

'count the READ_CODE OR parts
'----------------------------
For i = 0 To UBound(arrORS)
If InStr(1, UCase(arrORS(i)), "READ_CODE", vbBinaryCompare) > 0 Then
c = c + 1
End If
Next i

lUBReadBits = c - 1

If lUBReadBits = 0 Then
'no Read OR's so give original SQL back and get out
'--------------------------------------------------
ConvertOr2Union = strSQL
Exit Function
End If

'set up an array with the READ_CODE OR parts
'-------------------------------------------
ReDim arrORSWithRead(0 To lUBReadBits) As String

For i = 0 To lUBReadBits
If InStr(1, UCase(arrORS(i)), "READ_CODE", vbBinaryCompare) > 0 Then
arrORSWithRead(x) = arrORS(i)
x = x + 1
End If
Next i

'get the whole READ_CODE conditions string to be replaced
'--------------------------------------------------------
lStart = InStr(1, strSQL, arrORSWithRead(0), vbBinaryCompare)

lEnd = InStr(InStr(1, strSQL, arrORSWithRead(lUBReadBits),
vbBinaryCompare), _
strSQL, "AND", vbTextCompare)

If lEnd = 0 Then
lEnd = InStr(InStr(1, strSQL, arrORSWithRead(lUBReadBits),
vbBinaryCompare), _
strSQL, "ORDER", vbTextCompare)
End If

If lEnd = 0 Then
lEnd = Len(strSQL)
End If

strReadPart = Mid$(strSQL, lStart, lEnd - lStart)

'now get the clean READ_CODE OR bits
'-----------------------------------
arrORSWithRead = Split(strReadPart, "OR", -1, vbTextCompare)

'setup the new UNION SQL
'-----------------------
ReDim arrSQL(0 To lUBReadBits) As String

For i = 0 To lUBReadBits

If i = 0 Then
'clear opening bracket
'---------------------
If InStr(1, arrORSWithRead(0), "(", vbBinaryCompare) > 0 Then
arrORSWithRead(0) = Replace(arrORSWithRead(0), "(", "", 1, 1,
vbBinaryCompare)
End If
End If

If i = lUBReadBits Then
'clear closing bracket
'---------------------
If InStr(1, arrORSWithRead(lUBReadBits), ")", vbBinaryCompare) > 0
Then
arrORSWithRead(lUBReadBits) = _
Replace(arrORSWithRead(lUBReadBits), ")", "", 1, 1, vbBinaryCompare)
End If
End If

arrSQL(i) = Replace(strSQL, strReadPart, arrORSWithRead(i), 1, 1,
vbBinaryCompare)

Next i

For i = 0 To lUBReadBits
If i = 0 Then
strSQLUnion = arrSQL(i)
Else
strSQLUnion = strSQLUnion & strUnion & arrSQL(i)
End If
Next i

ConvertOr2Union = strSQLUnion

End Function


RBS