Combining Multiple 'Matches Pattern'

Hello Listers,

In the Query Panel of BO, rather than giving : Billing ID Matches Pattern ‘RET%’
OR
Billing ID Matches Pattern ‘GED%’
OR
Billing ID Matches Pattern ‘UMB%’
OR

Is there a way to make the above something like, Billing ID Matches Pattern ‘RET%,GED%,UMB%’

Also,
IF I want to allow the users to specify multiple patterns, would it possible to achieve it using a single prompt ?

Thanks in advance.

Regards,


Unni


Listserv Archives (BOB member since 2002-06-25)

Can you use the IN LIST or IN function for your query? I think they would support wildcards…

BILLING ID IN (‘RET%’, ‘GED%’, UMB%’)

Good luck,
Brent

Hello Listers,

In the Query Panel of BO, rather than giving : Billing ID Matches Pattern ‘RET%’
OR
Billing ID Matches Pattern ‘GED%’
OR
Billing ID Matches Pattern ‘UMB%’
OR

Is there a way to make the above something like, Billing ID Matches Pattern ‘RET%,GED%,UMB%’

Also,
IF I want to allow the users to specify multiple patterns, would it possible to achieve it using a single prompt ?

Thanks in advance.

Regards,


Unni


Listserv Archives (BOB member since 2002-06-25)

Correct me if I am wrong, but if it is true to say that the wildcard will only apply to all characters appearing after the initial three characters of the chosen Billing ID, then I believe the following approach will serve you well:

WHERE SUBSTR(BILLING_ID,1,3) IN (‘GED’,‘RET’,‘UMB’)

You can use this mechanism for all the 3 char combinations that you need to search for.

PJ

From: K. Unnikrishnan Nair [SMTP:K.Unnikrishnan.Nair@ERCGROUP.COM]

In the Query Panel of BO, rather than giving : Billing ID Matches Pattern ‘RET%’
OR
Billing ID Matches Pattern ‘GED%’
OR
Billing ID Matches Pattern ‘UMB%’
OR

Is there a way to make the above something like, Billing ID Matches Pattern ‘RET%,GED%,UMB%’


Listserv Archives (BOB member since 2002-06-25)

Brent

The suggestion you made below will not bring any data back unless BILLING ID contains exactly the values in the IN statement

Unni

The simple answer to your question is no you can’t combine multiple matches pattern. The way you have specified it is fine.

Answer to your second question is no you cant combine the prompts either. You will have to include 3 matches pattern prompts with OR’s between them.

Regards

Minal :slight_smile:

Can you use the IN LIST or IN function for your query? I think they would
support wildcards…

BILLING ID IN (‘RET%’, ‘GED%’, UMB%’)

Good luck,
Brent

Hello Listers,

In the Query Panel of BO, rather than giving : Billing ID Matches Pattern ‘RET%’
OR
Billing ID Matches Pattern ‘GED%’
OR
Billing ID Matches Pattern ‘UMB%’
OR

Is there a way to make the above something like, Billing ID Matches Pattern ‘RET%,GED%,UMB%’

Also,
IF I want to allow the users to specify multiple patterns, would it possible to achieve it using a single prompt ?

Thanks in advance.

Regards,
Unni


Listserv Archives (BOB member since 2002-06-25)

Brent:
For an IN/IN LIST, there has to be an exact match. It does not work.

Paul:
What you have written is true but it will mean that I will have to modify the auto BO generated SQL which I do not want to.

Thanks & Regards,


Unni

Correct me if I am wrong, but if it is true to say that the wildcard will
only apply to all characters appearing after the initial three characters of
the chosen Billing ID, then I believe the following approach will serve you
well:

WHERE SUBSTR(BILLING_ID,1,3) IN (‘GED’,‘RET’,‘UMB’)

You can use this mechanism for all the 3 char combinations that you need to
search for.

PJ

From: K. Unnikrishnan Nair [SMTP:K.Unnikrishnan.Nair@ERCGROUP.COM]

In the Query Panel of BO, rather than giving : Billing ID Matches Pattern ‘RET%’
OR
Billing ID Matches Pattern ‘GED%’
OR
Billing ID Matches Pattern ‘UMB%’
OR

Is there a way to make the above something like, Billing ID Matches Pattern ‘RET%,GED%,UMB%’

Pls report bounces in response to postings to BUSOB-L-Request@listserv.aol.com
Web archives (24 hrs. a day now!):
listserv.aol.com/archives/busob-l.html
OR search: Mail to listserv@listserv.aol.com, ‘search a_phrase in BUSOB-L’
Unsubscribe: Mail to listserv@listserv.aol.com, ‘unsubscribe BUSOB-L’
====================================

Brent


Listserv Archives (BOB member since 2002-06-25)

Unni,

R u not able to create an object for SUBSTR(BILLING_ID,1,3)

If u can, then simply drop this new object into the conditions sub-panel of the Query Panel.

PJ

From: K. Unnikrishnan Nair [SMTP:K.Unnikrishnan.Nair@ERCGROUP.COM] Sent: Thursday, March 09, 2000 4:49 PM

Brent:
For an IN/IN LIST, there has to be an exact match. It does not work.

Paul:
What you have written is true but it will mean that I will have to modify the auto BO generated SQL which I do not want to.


Listserv Archives (BOB member since 2002-06-25)

In a message dated 00-03-09 11:20:21 EST, you write:

n the Query Panel of BO, rather than giving :
Billing ID Matches Pattern ‘RET%’
OR
Billing ID Matches Pattern ‘GED%’
OR
Billing ID Matches Pattern ‘UMB%’
OR

Is there a way to make the above something like, Billing ID Matches Pattern ‘RET%,GED%,UMB%’

You can only do this if your database supports it. BusObj writes SQL code that is passed to your database. If this query structure was supported by your database, then you could do it. Unfortunately, I am not aware of any database that would allow you to use this.

Also,
IF I want to allow the users to specify multiple patterns, would it possible to achieve it using a single prompt ?

Ummm, well, yes, potentially. You would have to code the prompt in Designer, rather than in the client. And you would probably have to code for a specific number of prompts, so the user would be required to enter exactly the same number of patterns each time. And you would have to parse the user input and find the delimiter, and would have to expect the user to use the correct delimiter…

Oh heck, just say you can’t do it. It’s easier.

You should, however, be able to do this with a script. Again, that is not something that a query user might be expected to write. The script could be contained in an object in the universe, and would have to do all of those things that I started writing in the previous paragraph. But since a script can be interactive (where SQL cannot), you have more options.

Regards,
Dave Rathbun
Integra Solutions
www.islink.com


Listserv Archives (BOB member since 2002-06-25)