Could somebody explain match() function and exists()?

[Anonymous]

Hello,

It would be very helpful if, somebody could attacch a sample application which uses match function in it.

Though i went through the referrencee manual, but unfortunately didn't clearly understand the explanation about Match () and exists()

~B

[pover · Accepted solution]

This is an example of exists that is a part of Rob Wunderlich's cookbook available at his website robwunderlich.com.

The match function is a way to avoid writing

Load ...

From ...

Where Customer = 'A' or Customer ='B' or Customer='C'

and write it as

Load ...

From ...

Where match(Customer, 'A','B','C')

and it comes in a variety of flavors.  WildMatch() is usually my favorite.

Regards, Karl

[Miguel_Angel_Baeyens]

Hi,

In addition to Karl's post, Exists() referes to a previously loaded field to which compares the value that is loading, as opposed to Match() that compares to any given string or expression:

Customers:
LOAD CustomerID,
     CustomerName
FROM Customers.qvd (qvd);
Orders:
LOAD OrderID,
     OrderDate,
     Qty,
     Amt,
     CustomerID
FROM Orders.qvd (qvd)
WHERE EXISTS(CustomerID);

The Orders table will only load from the file Orders.qvd those where the value in CustomerID already exists in the table Customers, so for example, if Customer C has two orders but it's not in the Customers table, these two orders are not loaded.

Hope that helps.

Miguel

[Anonymous]

Thank you Karl and Miguel....for being crystal clear...

~B

[natebrunner]

Quick question.

How about the efficiency of an EXISTS vs. a MATCH?  For example if you add some matching criteria to an Inline Table at the beginning of the script and then use EXISTS on a Load. Is that faster than simply doing a MATCH on the load with the same criteria?

[Miguel_Angel_Baeyens]

Hi Nate,

In my experience, I only use Match() when the number of possible values is very limited, say no more than 10. And besides that, if the value is already in a field that has been loaded previously, it takes more time to build the function starting from the values loaded than simply using the Exists().

Anyway, feel free to try and share you experiences with large data sets and performance.

Hope that makes sense.

Miguel

[#7]

Hi Miguel.

My problem at the moment with either "OR" or "Match" function is that the moment I specify more than one value to return data for, it seems to "ignore" my criteria and return all values regardless.

Code snippet:

where (budget_code = '2013' or budget_code = '2014');

In this case, it returns every budget_code in existence. If I say where budget_code = '2013' then I get 2013 values only.

The similar thing is happening with match:

where match(budget_code, '2013', '2014') ignores my condition except for when only one value is specified, i.e.

where match(budget_code, '2013').

Do you have any suggestions please?

Thank you.