How do you search several fields

[bobde]

I’m new at this… I’m trying to search across three fields . The only way I’ve been able to do this is to input the value 3 times. I’d like to be able to search the three fields with one input… Is that possible
Here is what I’ve got…
SELECT “RosterInfo”.“Team”, “RosterInfo”.“Team2”, “RosterInfo”.“Team3”, “RosterInfo”.“RosterID”, “RosterInfo”.“Position”, “memberPersonalInfo”.“Phone_Number”, “memberPersonalInfo”.“First_Name”, “memberPersonalInfo”.“Last_Name”, “memberPersonalInfo”.“Notes”, “RosterInfo”.“Status” FROM “security”.“rosterinfo” AS “RosterInfo”, “security”.“memberpersonalinfo” AS “memberPersonalInfo” WHERE “RosterInfo”.“RosterID” = “memberPersonalInfo”.“RosterID” AND ( “RosterInfo”.“Team” = ? OR “RosterInfo”.“Team2” = ? OR “RosterInfo”.“Team3” = ? )

Thanks
Bob

[Ratslinger]

Hello,

You can do this with a parameter as input. :SOMETHING (starts with colon then recognizable name for you) will prompt for the value to be used in it’s place. Using the same parameter name in all three places will place the same value in each place. For example in the last portion of your SQL:

AND ( "RosterInfo"."Team" = ? OR "RosterInfo"."Team2" = ? OR "RosterInfo"."Team3" = ? )

is replaced by

AND ( "RosterInfo"."Team" = :Team OR "RosterInfo"."Team2" = :Team OR "RosterInfo"."Team3" = :Team )

When executed, you will be prompted to enter Team. Remember this is case sensitive.

If this is not acceptable, further explanation is necessary on what you are attempting to accomplish.

Edit:

In case you are interested, this ignores case:

AND ( UPPER("RosterInfo"."Team") = UPPER(:Team) OR UPPER("RosterInfo"."Team2") = UPPER(:Team) OR UPPER("RosterInfo"."Team3") = UPPER(:Team) )

and using:

LIKE UPPER ( CONCAT( '%', :Team, '%' )

will search for entry within the entry field such as ‘Team A’ in field containing ‘None other than Team A is here’.

If this answers your question please tick the (upper left area of answer). It helps others to know there was an accepted answer.

[bobde]

Thank You !! That’s perfect …
Again Thanks
Bob

[bbater]

Helped me too!