Ask Your Question
0

How do you search several fields

asked 2017-12-25 02:13:08 +0200

bobde gravatar image

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

edit retag flag offensive close merge delete

1 Answer

Sort by » oldest newest most voted
1

answered 2017-12-25 02:41:55 +0200

Ratslinger gravatar image

updated 2017-12-25 03:03:00 +0200

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.

edit flag offensive delete link more

Comments

Thank You !! That's perfect .. Again Thanks Bob

bobde gravatar imagebobde ( 2017-12-25 03:08:28 +0200 )edit
Login/Signup to Answer

Question Tools

1 follower

Stats

Asked: 2017-12-25 02:13:08 +0200

Seen: 32 times

Last updated: Dec 25 '17