Convert OR to UNION

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 

0
RB
6/14/2007 7:12:56 PM
access 16762 articles. 3 followers. Follow

3 Replies
735 Views

Similar Articles

[PageSpeed] 48

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.

-- 
KARL DEWEY
Build a little - Test a little


"RB Smissaert" wrote:

> 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 
> 
> 
0
Utf
6/14/2007 8:27:02 PM
> 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


"KARL DEWEY" <KARLDEWEY@discussions.microsoft.com> wrote in message 
news:7B560E9C-ADBE-427A-81DB-2CC741C192B6@microsoft.com...
> 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.
>
> -- 
> KARL DEWEY
> Build a little - Test a little
>
>
> "RB Smissaert" wrote:
>
>> 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
>>
>> 

0
RB
6/14/2007 8:33:33 PM
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


"RB Smissaert" <bartsmissaert@blueyonder.co.uk> wrote in message 
news:eQDhvgrrHHA.4740@TK2MSFTNGP02.phx.gbl...
>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 

0
RB
6/14/2007 10:57:41 PM
Reply:

Similar Artilces:

how do I convert Mozilla thunderbird data to outlook 2010?
Converted from an old Outlook to Mozilla Thunderbird years ago. Like new Outlook and would like to switch back. Can't seem to see a conversion utility -- Mishka I suspect you export from thunderbird to a format Outlook can import, eg csv (assuming pop mail) "Mishka" <Mishka@discussions.microsoft.com> wrote in message news:509C3250-167B-4FFF-9E07-5E5A8C625AA8@microsoft.com... > Converted from an old Outlook to Mozilla Thunderbird years ago. Like new > Outlook and would like to switch back. Can't seem to see a conversion > utility > -- >...

Convert Print Artist to Publisher
I need to convert documents in Sierra Print Artist Gold (2000) to Publisher. I would also be very happy to locate any program that could read and properly display my documents from this discontinued program. The Sierra company (now Vivendi Games) could not help me. TIA Judy You can try to do a Select All, Copy and then Paste it into a blank Publisher document. If that doesn't work (and don't expect perfection if it does), then you're going to have to start from scratch. -- JoAnn Paules MVP Microsoft [Publisher] "Judy" <Judy@discussions.microsoft.com>...

Converting from Money 5.0
Believe it or not, I'm still using Microsoft Money 5.0. It looks like I'm being "forced" to upgrade because my bank is no longer support the "OFC" file format and it doesn't look like I can use "OFX" in Money 5.0. Anyone know of any problems with upgrading to Money 2004 right from 5.0? Thanks, Stuart Providing you are staying in the same region for the upgrade (US > US), then Money reads all previous version's data files. -- Regards Bob Peel, Microsoft MVP - Money Hints/Tips http://support.microsoft.com/default.aspx?scid=fh;E...

Convert row to column?
How do I do the following: I have a an email group - long list. When expanded, it is --last name, first name (email); It looks like: Adams, Sam (adams @ sam.com); Baker, George (baker @ george.com); etc. I want to create a COLUMN in excel consisting of each name and email address (they don't need to be separated - just one name and address per cell going down the column in order). The unique separator would be the ; . How do I do this? I've tried importing in various ways but they always end up in one ROW which is not what I need. Thank you. Not clear where this long...

convert all tables to local tables
Is there a fast way to convert all tables to local tables without doing each one individually. You mean convert linked tables to local tables? Just import everything into the backend and you will have all local tables.... -- Maurice Ausum "Luther" wrote: > Is there a fast way to convert all tables to local tables without doing each > one individually. No. You have to import each table individually. -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no e-mails, please!) "Luther" <Luther@discussions.microsoft.com> wrote in message news:C4C3...

MAPILab Toolbox for Outlook: 14 tools, including new vCard Converter
Hello! At October 3, we have released MAPILab Toolbox v2.3 - not it contains 14 tools including vCard Converter. If you need to save your data in the vCard format or to update your contacts using the data in the vCard format, vCard Converter is created to help you. It will allow you to import any files in the vCard format (.vcf files) as well as to save vCard data from the contacts, from messages into the contacts or files in the vCard format. Product homepage: http://www.mapilab.com/outlook/toolbox/ Direct download for 30-days trial version: http://www.mapilab.com/files/toolbox.zip S...

Converting individual mailbox to a file using MAPI
Hi All, I am looking for a way to backup individual mail box of our Exchange Server 2003.I want to convert my individual mail box to a file and backup it. Can any help me with what MAPI methods i can achive this(though MAPI is not designed for backup). Should I read each mail of mail box and write it to a file ?. Any help on this will be greatly apppreciated. Regards, Sridhar D Hi All, After googling, I understand that exporting mailbox to .pst file can be achieved through NameSpace functions and CopyTo method Is there any methods equivalent to NameSpace AddStore?. I doesn't wa...

Converting Opportunities to Quotes in CRM 4.0
Is it possible to convert an Opportunity to a Quote. Have not discovered this functionality. Many thanks in advance. You can achieve this through custom development, write assembly to do this. -- Regards, MS CRM Certified Professional http://microsoftcrm3.blogspot.com Chat with me on MSN / Gmail / Skype : ID Is :.. mscrmexpert@gmail.com "dem@cfo" wrote: > Is it possible to convert an Opportunity to a Quote. Have not discovered this > functionality. Many thanks in advance. On May 27, 9:12=A0pm, dem@cfo <dem@c...@discussions.microsoft.com> wrote: > Is it p...

Are you able to convert a MS Publisher prepared doc into MS Word?
I need to create a 'flyer' and attach this document to one of our internal databases at work. However, if I was to create this flyer/document in MS Publisher 2003 - is it possible to then go and convert this document into a Word document? As the internal database that we use can only accept Word docs as attachments. ShuGill wrote: > I need to create a 'flyer' and attach this document to > one of our internal databases at work. However, if I was > to create this flyer/document in MS Publisher 2003 - is > it possible to then go and convert this document into...

Converting from Money to Quicken
I guess I don't really have any choice bt to convert to Quicken. Please tell me the best way to convert my data for use in quicken. I have about 15 years of data in my file and really would like to have all the data in Quicken. I use Money Plus H&B and have investment data, banking data, mortgage data and rental property data all in the file. "neil154" wrote: > I guess I don't really have any choice bt to convert to Quicken. > > Please tell me the best way to convert my data for use in quicken. I have > about 15 years of data in my file and really...

Convert numbers into text (one thousand two hundred)
I would appreciate any assistance in this regard, I would like to word numbers inside my excel sheet Hi Motasim! If you want numbers to be translated in Thai then there�s an Excel function BAHTTEXT that will do it. For some reason Microsoft didn�t think non-Thai speakers needed an equivalent. So: See: XL2000: How to Convert a Numeric Value into English Words http://support.microsoft.com/default.aspx?scid=KB;EN-US;213360 and: XL: How to Convert a Numeric Value into English Words http://support.microsoft.com/default.aspx?scid=KB;EN-US;140704& and: (courtesy of a cut and paste from...

convert quicken V8
I have just purchsed Money 2004 and want to convert from quicken personal V8 but get the message - could not convert - with no explanation, any ideas?? steve >-----Original Message----- >I have just purchsed Money 2004 and want to convert from >quicken personal V8 but get the message - could not >convert - with no explanation, any ideas?? steve >. > I ran into a similar situation when trying to convert a Quicken Deluxe 99 file into Money 2004 (Demo Version). My attempt also had no explanation. Incidentally, my Quicken file size was 185 MB - data since 1994. I ende...

convert notepad to access
can i convert note pad to access 2000 ...

How do I convert csv to columns?
Can someone tell me (using Excel 2330) how I make a csv file which has semi colon separation points into columns? thanks After you open this file all of your data should be in column A, select this column and Data, Text to Columns, Delimited, by semicolon should give you the result you seek. plato Wrote: > Can someone tell me (using Excel 2330) how I make a csv file which has > semi > colon separation points into columns? > > thanks -- Bryan Hessey ------------------------------------------------------------------------ Bryan Hessey's Profile: http://www.excelfo...

XML File Converter for MS 2004
Version: 2004 Operating System: Mac OS X 10.4 (Tiger) Processor: intel I'm a high school teacher. I write power points on MS 2004 on my Mac and transfer them to my school's PC that has MS 2003. This worked great, with exception of minor formatting changes, until a month ago. Power points that I previously opened on that PC, along with new ones, would not open - this is disasterous for instruction. My tech says 1) the school's PC may have had updates that caused the new problem and 2) that I should install the Open XML File Converter for Mac's MS 2004. I'm concerned tha...

Can't Convert Quicken 2001 Deluxe Data to MS Money 2004
First I tried to convert the Quicken Data using the MS Money 2003 OEM Version that came with the new computer I bought last week. Then I purchased MS Money 2004 Standard Edition and still couldn't convert my Quicken data. On the box, Microsoft says they offer a 30 day money back guarantee with no details about how to get your money back. Does anyone know how I can get my money back? Regards, John E. Golden ...

How to convert PDF to Office
As we know, PDF is easy to generated but hard to eidt. Many PDF files are created from Office document, sometimes we need to edit some contents in PDF ,so we nend to convert PDF to Office, including Word, Excel and Powerpoint.And recommend a App named PDF Converter by which users can convert PDF to Word, Excel,Powerpoint, Text and HTML. The conversion quality is superb. Maybe you can give some good comments about this APP. http://www.anypdftools.com/pdf-converter.html#201 ...

Newbie question: how to convert one column to multiple columns?
Anyone, I have a spreadsheet of about 50 names/addresses in which all of the data is in a single column, i.e.: name1 address1 city1 state1 zip1 name2 address2 .... etc. I'd like to convert this spreadsheet so that the names are all in column A, the addresses in column B, etc. I'm certainly willing to select all five related cells for a given person (I would need to do that 50 times), but once those five cells are selected, is there a way that I can recast those five cells from being in the same column into multiple columns? Thanks so much! David You can easily do that with a fo...

Program Fail to Run After Convert from VC++ 6.0 to VC++ .NET 2003
I got a VC++ 6.0 program which is a windows service. After converting to VC++ ..NET 2003 project and fixing some coding error. It can compile and looks work fine. But when I run the service, it said program error and looks it cannot get a thread from AfxGetThread function. Below please find a code segment which I experience the error. Any can help on this? const CString NDSApp::SERV_NAME(_T("NDS Data Delivery")); NDSApp::NDSApp() :CNTService("NDS Data Delivery"){ m_pNDSSetting = new CRegKeyValuePair(APPNAME,SECTION); } NDSApp::~NDSApp() { if (m_pNDSSetting) de...

want to convert number to word (eg) 1 as one
I would like to convert automaticaly the numericals into word format fo example In excel If I type 100.00 ( it should type automatically (one hundre only). Is it available in excel. If anyone knows pls reply to my e-mail wintersc@vsnl.ne -- Message posted from http://www.ExcelForum.com It is not available by default, but this is an often-asked question in this forum. You may want to do a search and see if you find your answer. Otherwise, are you looking for a formula or a VBA macro? If you're looking to put the text in a different cell from the number, then a formula will work. If ...

Converting notes over when converting leads to contact/accounts/op
The notes do not carry over when a lead is converted. I think that this is highly unproductive. I think that the user should at least be given the option of bringing over thier notes when they convert a lead. Sales has to re-write everything or else drill back to the lead through the new form which is not a very good way to keep everything in order. ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, ...

CONVERT Function Disappered in Excel
Hi I have Office XP which I have updated to SP3. I ran Windows Update the other day and it offered to check other programs and it recommended some form of security update for Office which I opted to download. It might be coincidence but I have a worksheet that contains the CONVERT worksheet function which no longer works after this update. When I recalculate, I get #### in the boxes that contain the CONVERT function and when I move the mouse over the box I get #NAME?. If I try the 'Insert Function' option, CONVERT is not available from the list and if I copy & paste a CONVERT ...

converting open office documents to Word.
I have several ODT documents from Open Office and would like to convert them to word documents. How do I do this please? Are you using Word 2007? That version can open .*odt files, assuming that you have installed the latest updates. You could then convert the files, one by one, via the Save As dialog box. -- Stefan Blom Microsoft Word MVP "alggomas" <alggomas@discussions.microsoft.com> wrote in message news:267718A9-3DE2-450A-92FE-1FB81C29B640@microsoft.com... >I have several ODT documents from Open Office and would like to convert >them > ...

Converting Quatro Pro to Excel
How can this be done? "Wings" <Wings@discussions.microsoft.com> wrote in message news:895AEFD5-9DE8-480B-B046-D48C43BBA142@microsoft.com... > How can this be done? Excel 2000 has a converter available for that, but when installing Office, the default is to NOT install that converter. Check your Office install disk - you didn't mention what version you have. Later versions might not have that converter, probably because Quattro Pro hasn't been around in a zillion years. Then, you'll need to find someone who's got the converter (like me!). Just upda...

Convert Integer to Date in Access ADP Project
I am new to creating Access projects with SQL databases. I have created a View from two tables in my database. One of the tables contains a field that stores the date as an integer. I need to convert the field into a date. In Access MDB, I would create a formula using a DateValue function but in Access ADP I get a message the DATEVALUE function is not recognized. This question would be best asked in a newsgroup specific about the T-SQL language such as m.p.sqlserver.programming. Also, the official newsgroup for ADP is m.p.access.adp.sqlserver. As for your question, use the Conve...