optional multi-select prompt macro using LIKE

[brymack]

A user can select 0, 1, or many values from an optional prompt. My syntax (in a report using SQL rather than a package) works if they select 0 or 1 record, but not if they select multiple.  The data is stored in a denormalized form such that there are fields such as:
field1: may or may not have value
field2: may or may not have value
...
fieldN: may or may not have value

Here is my syntax:

      where (
                    m.dedn1 like ('%'||#promptmany('parm_carrier','string','bd.carrier')#||'%')
               or m.dedn2 like ('%'||#promptmany('parm_carrier','string','bd.carrier')#||'%')
                    or m.dedn3 like ('%'||#promptmany('parm_carrier','string','bd.carrier')#||'%')
               or m.dedn4 like ('%'||#promptmany('parm_carrier','string','bd.carrier')#||'%')
               or m.dedn5 like ('%'||#promptmany('parm_carrier','string','bd.carrier')#||'%')
               or m.dedn6 like ('%'||#promptmany('parm_carrier','string','bd.carrier')#||'%')
               or m.dedn7 like ('%'||#promptmany('parm_carrier','string','bd.carrier')#||'%')
               or m.dedn8 like ('%'||#promptmany('parm_carrier','string','bd.carrier')#||'%')
                    )

As I said, it works when 1 or 0 are selected in the prompt, but I get this error when I select multiple in the prompt:
UDA-SQL-0107 A general exception has occurred during the operation "prepare".ORA-00907: missing right parenthesis
RQP-DEF-0149 The query specification is incorrect.RQP-DEF-0457 Referenced Query 'Report Query_tab0' is not defined
or its query items contain unresolved references.RSV-SRV-0042

For the record, if I take this out and plug it into SQL Developer and substitute a string of many for the macro, it works just fine.

Thanks in advance!

[bdbits]

The #prompt from a multiselect will resolve to something like:
   First Selection, Second Selection, Third Selection

If you substitute that into everything between the # symbols, you can see why the like is probably not going to work.

Unfortunately it appears you are using custom written SQL, instead of a package. So most Cognos functions - which could fix your problem - are going to be useless for you. This is yet another case of why it is a Bad Idea to use custom SQL instead of getting the data properly modeled in a package. Sorry, but that is the way Cognos is designed to work.

[brymack]

I realized I never replied to this, the fix was quite simple, can't believe I couldn't figure this out right away. Anyways, just posting this in case someone else runs into the same problem, love the flexibility this gives me without having to revamp the entire model for something I'll only use once (especially since I'm a programmer and not a Cognos person)

AND  (m.dedn1 in  (#promptmany('parm_carrier','string','bd.carrier')#)
                           or m.dedn2 in (#promptmany('parm_carrier','string','bd.carrier')#)
                              or m.dedn3 in  (#promptmany('parm_carrier','string','bd.carrier')#)
                           or m.dedn4 in  (#promptmany('parm_carrier','string','bd.carrier')#)
                           or m.dedn5 in (#promptmany('parm_carrier','string','bd.carrier')#)
                           or m.dedn6 in (#promptmany('parm_carrier','string','bd.carrier')#)
                           or m.dedn7 in (#promptmany('parm_carrier','string','bd.carrier')#)
                           or m.dedn8 in (#promptmany('parm_carrier','string','bd.carrier')#)
                              )
       )