If you are unable to create a new account, please email support@bspsoftware.com

 

News:

MetaManager - Administrative Tools for IBM Cognos
Pricing starting at $2,100
Download Now    Learn More

Main Menu

Wild Card for Nine Digit Zip Codes in Prompt

Started by wwconslt, 04 Mar 2020 10:33:33 AM

Previous topic - Next topic

wwconslt

Hello Everyone,

Relational Data Model.

I'm trying to create an optional prompt where the user can enter zip codes they would like to EXCLUDE from the results. Since they will be entering multiple five digit zips, I would like to use a wild card that says to exclude that five digit zip or any nine digit zip beginning with that five digit zip the user provides.

For example if the user enters 90210, I would like it to exclude the results 90210, 90210-0222, etc.

I've tried different versions of these but none seem to work:
[ZIP_CODE] not in '?Excluded_Zip?+%'
[ZIP_CODE] not in Starts With ?Excluded_Zip?

Thank you!

dougp

Try these:
[ZIP_CODE] not in ?Excluded_Zip?+'%'
[ZIP_CODE] not Starts With ?Excluded_Zip?

wwconslt

Thanks Doug.  Unfortunately, the first example throws a parsing error.  The second example works but only with one zip entered at a time.  The users need the ability to paste in up to 1000 zip codes in an optional, multi-line, multi-select text box prompt.  Usually requires the word "in"  [ZIP_CODE] in ?Excluded_Zip?

ashley

#3
I think dougp's first example should be [ZIP_CODE] not like ?Excluded_Zip?+'%'. Like allows for wildcard operators, but these are single select only afaik.

dougp

Ashley:  Correct.

wwconslt:  left([ZIP_CODE], 5) in ?Excluded_Zip?
This limits the user to entering 5-digit zip codes.
If LEFT doesn't work (you didn't say which RDBMS you are using), you'll need to use SUBSTR.

wwconslt

Thank you everyone.  Doug your solution works perfectly--thank you!