Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
Discussion GroupsFormsForms ProgrammingQueriesModules / DAO / VBAReports / PrintingMacrosDatabase DesignSecurityConversionImporting / LinkingSQL Server / ADPMultiuser / NetworkingReplicationSetup / ConfigurationDeveloper ToolkitsActiveX ControlsNew UsersGeneral 1General 2
Access DirectoryToolsTutorialsUser Groups
Related Topics
SQL ServerOther DB ProductsMS OfficeMore Topics ...

MS Access Forum / General 2 / June 2007

Tip: Looking for answers? Try searching our database.

Convert OR to UNION

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
RB Smissaert - 14 Jun 2007 20:12 GMT
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
KARL DEWEY - 14 Jun 2007 21:27 GMT
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.

Signature

KARL DEWEY
Build a little - Test a little

> I am trying to make a function to convert a query with one or multiple OR
> conditions to a query with one or multiple
[quoted text clipped - 38 lines]
>
> RBS
RB Smissaert - 14 Jun 2007 21:33 GMT
> 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

> You are talking apples and cocunuts or I am bananas.
> Or is used in a query as part of the WHERE condition.
[quoted text clipped - 46 lines]
>>
>> RBS
RB Smissaert - 14 Jun 2007 23:57 GMT
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

>I am trying to make a function to convert a query with one or multiple OR
>conditions to a query with one or multiple
[quoted text clipped - 38 lines]
>
> RBS
 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2008 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.