use of inlist and matches pattern in same query

[system]

I have a request from a customer to have a report that they can either enter a matches pattern or in list as an answer to a prompt. The report would be broken down by 3 different selectable entities. So they could enter either for the prompts for association, group code, or department number. Initial thought was they could enter a % where they wanted all and the in list would handle a limited list.

Ideally I would want to set the variables up in an “Or” statement within the query. It will be a full client report but refreshable on WEBI. Issue I have been running into is that matches pattern is a mono verses the multi I need for the in list.

---

mmurray (BOB member since 2003-01-13)

[system]

Hi Mike,

Take a look at this post for starters

Using 'Matches Pattern' and 'Inlist' in one prompt.

I've created a report that contains many report prompts, one of which being the following: PLANT_MATERIALS.MRP_CONTROLLER IN @variable('2. Select ''MRP Controller/s'' for analysis') However rather than just being able to pick from the LOV or enter the values required, how can I modify the prompt so that the user can pick from the list of values or enter a wildcard; eg, % so that the report returns all values. Whilst I here I'll ask another question: What determines the maximum number of val…

---

irish_stan (BOB member since 2003-05-13)

[system]

The issue with using the LOV is there could be hundreds of them. An example of what they are trying to do is.

Enter Group number:

If the wanted all groups in Iowa and groups were all prefixed by the state abbreviation followed by a number they could enter IA as a pattern to match and would get all IAxxxx groups back.

If there was a group in every county that would return 99 rows. But if they only wanted two adjacent counties they would enter IA1234;IA1256. If they wanted all groups they could enter %.

Then this type of logic would continue with the other criteria

---

mmurray (BOB member since 2003-01-13)

[system]

You’re on the right path. The problem is that BO doesn’t do optional prompts.

The limit of inlist and wildcard is not a BO one, it’s a SQL one.

---

Steve Krandel (BOB member since 2002-06-25)

[system]

I also have a case where I want to allow the user to enter either
single value, multiple values, partial value (e.g, CH%) or % for all.

Steve, I am not sure I understand your post. Are you saying that this is not possible to do?

---

ciodice (BOB member since 2005-04-05)

[system]

What I’m saying is that you can’t have a sql statement that accepts a wilcard within with an IN operator.

You need to try to generate something like:

Where
table.column in @prompt(…,multi, free) OR
table.column like @prompt(…,mono, free) OR
‘ALL’ in @prompt(…,multi, free) OR

The question is this: can you use the same prompt with different parameters? I don’t have time to check this out right now. Try it and let us know.

---

Steve Krandel (BOB member since 2002-06-25)

[system]

I wasn’t able to do it.
I was able to get a single value, pattern matching and % for all. However, when I selected multiple values I received the following error:

Connection or SQL Sentence Error DA0005

any ideas anyone?

---

ciodice (BOB member since 2005-04-05)

[system]

What are the details of the error? When I see that error, I get a “details” button, and when I click on the Details, a more descriptive message appears.

---

KSG (BOB member since 2002-07-17)

[system]

Here are the details:

Exception: DBD, ORA-00907: missing right parenthesis
State: N/A

---

ciodice (BOB member since 2005-04-05)

[system]

You may want to post the SQL you used. This sounds like a syntax issue.

---

KSG (BOB member since 2002-07-17)