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 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.
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.
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
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.
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 It is a valid operator and displays in my list of operators. I appreciate the help…any other ideas? I am stuck.
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
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)
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.
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!!!
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)
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!
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 ). The “match patern %” is a good solution but how manage multiple values ?