Matches pattern/Different from pattern with underscores doesn't work

[Former Member]

We have one prompt, that prompt field having _ as a character, Use of u2018Matches patternu2019 not work properly in report queries if an underscore is part of the condition. For instance, Org Group Matches pattern u201C%EV_%u201D shows tickets that start with u201CEVu201D, not just ones starting with u201CEV_u201D. This is the requirements for us; I have given this query to the user

( ((( upper (TICKET.ORG_GROUP) ) LIKE upper(('%' || @Prompt('Enter Org Group in upper case - use % as a wildcard or ALL for any value:','C',,Mono,FREE) || '%')) OR ('ALL') IN @Prompt('Enter Org Group in upper case - use % as a wildcard or ALL for any value:','C',,mono,FREE))) )

AND

substr(TICKET.ORG_GROUP,3,1)='_'

But they told certain Org Groups can begin with u2018EV_u2019, u2018MR_u2019, u2018ML_u2019, etc. And most Org Groups donu2019t contain an underscore. So Iu2019m guessing that we need a CASE WHEN surrounding the Prompt filter or another Filter we can include in the Conditions that says:

We now have an unexpected result: If I search for Org group LIKE "EV_%", I get results with any value before the 'EV_'. For instance, I get tickets for Org Groups of LVEV_GA and SIEV_GA. I would expect those Org groups to be included if I searched for "%EV_%", but not with "EV_%".

Can anybody give me the query for that.

Thanks

SS

[Former Member · Accepted solution]

e have one prompt, that prompt field having _ as a character, Use of u2018Matches patternu2019 not work properly in report queries if an underscore is part of the condition

It can, and I already provided a suggestion on BOB here for this same question:

http://www.forumtopics.com/busobj/viewtopic.php?t=156520