BusinessObjects Board

Like Pattern, ALL, and List of Values prompt

Hello everyone…it has been forever since I have been out here but you have always worked wonders for me in the past and I am hoping you can do it again :slight_smile: I have searched the archives and didn’t find the solution but if you know of a place just send me there!.

I am using the following for a prompt that accepts an (*) for all values or finds an exact match for a value entered.

( (( V_ARCHIVE_INQ_LETTER.LAST_NAME )IN@Prompt('4. Select a last name or enter * for all names','a','V Archive Inq Letter\Last Name',multi,free) ) OR '*' IN @Prompt('4. Select a last name or enter * for all names','a','V Archive Inq Letter\Last Name',multi,free)  )

How can I tweak this to allow the user to enter a partial value but force the search to do a “Like” statement? For example, if I enter Mic, I want Michele, Michael, and Mickey to all be returned.
Any guidence is appreciated.


Michele Asay (BOB member since 2002-08-15)

Try something along the lines of (Oracle syntax):

ColumnName LIKE @Prompt ('Enter Value', 'A',,mono,free)
OR '*' = @Prompt ('Enter Value', 'A',,mono,free)

Andreas :de: (BOB member since 2002-06-20)

Thanks, I tried that and I get an error “Parse Failed: Invalid relational operator”. Am I doing something wrong in the syntax?


Michele Asay (BOB member since 2002-08-15)

Which DBMS are you using? What is the exact SQL code you are using?


Andreas :de: (BOB member since 2002-06-20)

I know this works for SQL Server, but you’d have to experiment with Oracle.

( (( V_ARCHIVE_INQ_LETTER.LAST_NAME ) LIKE @Prompt(‘4. Select a last name or enter * for all names’,‘a’,‘V Archive Inq Letter\Last Name’,mono,free)||’%’ ) OR ‘*’ = @Prompt(‘4. Select a last name or enter * for all names’,‘a’,‘V Archive Inq Letter\Last Name’,mono,free) )

This isn’t perfect but should give you a head start. Basically, you have to combine like and = instead of IN.


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

This is the exact where clause of the universe object that currently works as I described in my initial post:

(( V_ARCHIVE_INQ_LETTER.LAST_NAME )IN@Prompt('4. Select a last name or enter * for all names','a','V Archive Inq Letter\Last Name',multi,free) ) OR '*' IN @Prompt('4. Select a last name or enter * for all names','a','V Archive Inq Letter\Last Name',multi,free)

Based on your response, I changed the IN to LIKE and get the error I listed in my last post. Here is the exact SQL:

(( V_ARCHIVE_INQ_LETTER.LAST_NAME )LIKE@Prompt('4. Select a last name or enter * for all names','a','V Archive Inq Letter\Last Name',multi,free) ) OR '*' IN @Prompt('4. Select a last name or enter * for all names','a','V Archive Inq Letter\Last Name',multi,free)

I am using Oracle and the exact error message returned is

Any ideas :cry:


Michele Asay (BOB member since 2002-08-15)

You need to change your stuff to MONO. Multi only works with IN.
In general all your stuff should be MONO. You only need IN when you want to allow multiple choice.


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

Thanks…I tried changing it to ‘mono’ but it still gives me the invalid operator when I use ‘LIKE’. I just don’t understand why :hb: It is a valid operator and displays in my list of operators. I appreciate the help…any other ideas? I am stuck.


Michele Asay (BOB member since 2002-08-15)

You also are missing a space after the Operator and the @Prompt.


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

Sadly, space or no space, it still doesn’t parse. And I got very excited when you posted …thinking it was really a stupid little thing like that :wink:
Here is what I have exactly in case something else jumps out that I could be overlooking…it should work like this right?

(V_ARCHIVE_INQ_LETTER.LAST_NAME LIKE @Prompt('4. Select a last name or enter * for all names','a','V Archive Inq Letter\Last Name',mono,free) ) OR '*' LIKE @Prompt('4. Select a last name or enter * for all names','a','V Archive Inq Letter\Last Name',mono,free)

Michele Asay (BOB member since 2002-08-15)

Change the 2nd LIKE to = .

Also, try parsing the 2 parts separately to find out what’s wrong. I’ve also had problems in the past with the SYNTAX not being EXACTLY correct. Things like mono vs MONO can make it fail.


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

THANK YOU STEVE!!! The second “Like” to “=” worked. (I had it that way when you first suggested it but must not have saved it when I changed the multi to mono.)
Ugh, I can now have a happy easter and I hope you do the same. Thanks!!! :cheers:


Michele Asay (BOB member since 2002-08-15)

One last thing. Right now you are not getting a LIKE because you have no wildcard.

(V_ARCHIVE_INQ_LETTER.LAST_NAME LIKE @Prompt(‘4. Select a last name or enter * for all names’,‘a’,‘V Archive Inq Letter\Last Name’,mono,free)||’%’ ) OR ‘*’ = @Prompt(‘4. Select a last name or enter * for all names’,‘a’,‘V Archive Inq
Letter\Last Name’,mono,free)

Let me know…


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

Thanks Steve. I had added that piece back in and it parsed. I am not sure why it didn’t originally take it (I pasted your syntax in). Something was off but it works just the way I need it to . Thanks again for your help! :+1:


Michele Asay (BOB member since 2002-08-15)

Hi,

Is it possible to select multiple values in the same way ? (select one or multiple values or all)

How to manage your solution with “in” ?*

My LOV contains 4 000 values and I can’t make “all values” with a “in list” containing 4 000 items (so 4 000 table scans :roll_eyes: ). The “match patern %” is a good solution but how manage multiple values ?

Thanks for your help in advance… :crazy_face:

Sybase 12.5 / Webi 6.1b (Apache/Tomcat)


nemo (BOB member since 2004-08-05)

nemo – have you taken a look at this topic in our Designer FAQ questions?


Anita Craig :us: (BOB member since 2002-06-17)

:oops: Sorry…

Thank you for your help, it’s fine.

Google brings to me this site yesterday evening and I found lots and lots answers… It’s really great. Nice job !!! :smiley: :smiley:


nemo (BOB member since 2004-08-05)