You are viewing this site as a guest. You can still ask questions and help others! Join our Dev2Dev IT Community to receive your own blog, share your knowledge, and much more.

Help with multiple Query Condition Prompts

3 replies [Last post]
Ed-D's picture
User offline. Last seen 9 years 38 weeks ago. Offline
Joined: 05/14/2008
Posts: 25

Hello,

It has been a long day, and I am stuck on one thing. I am using Query v9.x web based. I need to get a list of employees in a particular City, as well as also in a particular State. I want to offer the person running the query multiple Prompt options so they will be prompted to provide the State and/or the City.

Here are the conditions for the City prompt:

C.CITY BETWEEN Expr - Expr

Expr 1 = NVL(TRIM(:1),'A')
Expr 2 = NVL(TRIM(:1),'Z') || 'ZZZ'

The State Prompt and Conditions are similar. I have no problem with the State as the abbreviation is stored all in Upper case.

HOWEVER - The City can appear as mixed case, ALL UPPER CASE, or all lower case in the Table.

If I enter the City Name in all lower case at the Prompt, and it does not exist as all lower case in PSoft, I get zero rows returned.

I want to enter at the city name at the prompt without worrying about whether the case matches or not, get all of the employees' names returned in that city.

So... Galveston = galveston = GALVESTON
or... New York = new york = New york = NEW YORK
etc.

Any ideas?

Thanks!

Ed

Ed-D's picture
User offline. Last seen 9 years 38 weeks ago. Offline
Joined: 05/14/2008
Posts: 25
Re: Help with multiple Query Condition Prompts

Oops - sorry -

I am using version 8.8x of Psoft. It really was a bad day that I was having!!!!!

Ed

Lepa's picture
User offline. Last seen 2 years 20 weeks ago. Offline
Joined: 06/23/2008
Posts: 591
Re: Help with multiple Query Condition Prompts

Have you tried using the UPPER function? If you do an UPPER on the field and an UPPER on the expression you will end up comparing apples to apples.

try doing something like this UPPER(C.CITY) BETWEEN UPPER(Expr) - UPPER(Expr )

Give back to the community and help it grow!
* Help with unanswered forum questions and issues
* Register or login to share your knowledge at your own blog

Ed-D's picture
User offline. Last seen 9 years 38 weeks ago. Offline
Joined: 05/14/2008
Posts: 25
Re: Help with multiple Query Condition Prompts

Lepa -

Thank you! I must have been in a complete fog to have missed that one.

Ed