Parameter Search

[ekta22]

I have a stored procedure that has 5 parameters entered by the users. I am using this stored procedure to run my crystal report. One parameter called 'pCODE' is based on 4 fields in the database. The 4 fields together are 5 characters in length(field1 is 2 characters, and rest 3 are 1 character). I have concatenated the 4 fields so that my selection looks like something like this

select * from table
where
field1||field2||field3||field4 = pCODE||'%' OR pCODE IS NULL
AND AREA = pAREA OR pAREA IS NULL
and so on

The issuse I am having is that if a user enters pAREA parameter or any other parameter and leaves pCODE blank I get data but if they enter a valid pCODE parameter I get no data. A user can enter partial data in pCODE paramter to search for data. Not sure why this is not working. Is this the right way to do it?

Appreciate any help!

Thanks,
-E

 

[dbtoo2001]

Assuming the percent sign to be a wildcard:

select * from table
where
field1||field2||field3||field4 LIKE pCODE||'%' OR pCODE IS NULL
AND AREA = pAREA OR pAREA IS NULL

 

[dbtoo2001]

you may need some single quotes around that pcode and percent sign.

 

[ekta22]

Thanks for your reply. I am still not getting any data when I enter pCODE parameter. Can't figure out why

 

[carp]

Try putting parentheses around your pcode condition:

select * from table
where
(field1||field2||field3||field4 = pCODE||'%' OR pCODE IS NULL)
AND (AREA = pAREA OR pAREA IS NULL)
and so on

I find that, as a general rule, when you are mixing ORs and ANDs, you are well-advised to use parentheses to make sure your clause precedences are exactly the way you want them to be!

 

[cmmrfrds]

Does field1 thru field4 alway contain data. If any one can be NULL, what do you want to do? You can use DCODE or a CASE Statement to handle the nulls. Even with dcode if field1 can be null the % on the end probably won't give you what is wanted.

Please explain what can be the content of the various fields in order to get a good solution.