I have a procedure that accepts 5 different inputs that are being passed in from a website. Only 1 input can be sent at a time and I have to write a query to output values based on what is being passed in. I had originally started writing this with multiple selects and IF statements but my boss told me to do it in one query. I am stuck on how to return the information based on whether or not certain values are passed in.
The person on the website could use a wildcard to search for a name so the website is passing a wild card indicator to my procedure. If a wildcard is used then I have to use a LIKE to search for the names otherwise use the entire name.
I have pasted my query below and put a note as to where the inputs come in. I tried using an OR but was told that it would work with outer joins.
Can anyone point me in the right direction on how to select data using one query where multiple inputs may or may not be passed in?
CASE WHEN emp.freight_indicator = 'N' THEN mav1.text_value
CASE WHEN emp.freight_indicator = 'N' THEN emp.territory
WHEN emp.freight_indicator = 'Y' THEN mav2.text_value
CASE WHEN mbr.mbr_status = 'P' THEN NULL
CASE WHEN lvl.name = 'Re:turn' THEN 'Re:turn'
WHEN lvl.name IN ('Shipping', 'Shipping HSOW') THEN 'My FedEx Rewards'
WHEN lvl.name = 'Game Time' THEN 'Game Time'
FROM mbr_fed_employee emp,
WHERE mav1.attr_id(+) = 5008
AND mav2.attr_id(+) = 5019
AND mav3.attr_id = 10121
AND mbr.member_id = mav1.member_id(+)
AND mbr.member_id = mav2.member_id(+)
AND mbr.member_id = mav3.member_id
AND mbr.mbr_level_id = lvl.mbr_level_id
AND ern.mbr_level_id = mbr.mbr_level_id
AND mbr.member_id = mlk.member_id
--these are the values that could or could not be passed in. Query works okay up to this point
AND mlk.member_key = in_express_acct
AND mav2.text_value = in_freight_acct
AND mbr.company_name = (SELECT company_name
WHERE company_name LIKE '%in_company_name%'
AND in_wild_card_search_ind = 'Y')
AND mbr.name_last = (SELECT name_last
WHERE name_last LIKE '%in_last_name%');