alscincy
asked on
Complex Oracle Query
Hello,
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?
SELECT emp.employee_id,
mbr.member_id,
CASE WHEN emp.freight_indicator = 'N' THEN mav1.text_value
ELSE mav2.text_value
END primary_acct_num,
emp.freight_indicator,
mbr.company_name,
mbr.name_first,
mbr.name_last,
CASE WHEN emp.freight_indicator = 'N' THEN emp.territory
WHEN emp.freight_indicator = 'Y' THEN mav2.text_value
ELSE NULL
END territory,
mav3.text_value facility_id,
CASE WHEN mbr.mbr_status = 'P' THEN NULL
ELSE mbr.enrollment_date
END registration_date,
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'
ELSE NULL
END campaign,
ern.earning_rate base_earning_rate,
mbr.current_balance
FROM mbr_fed_employee emp,
mbr_member_all mbr,
mbr_attr_value mav1,
mbr_attr_value mav2,
mbr_attr_value mav3,
mbr_level_earning_rate ern,
mbr_level lvl,
mbr_lookup mlk
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
FROM mbr_member_all
WHERE company_name LIKE '%in_company_name%'
AND in_wild_card_search_ind = 'Y')
AND mbr.name_last = (SELECT name_last
FROM mbr_member_all
WHERE name_last LIKE '%in_last_name%');
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?
SELECT emp.employee_id,
mbr.member_id,
CASE WHEN emp.freight_indicator = 'N' THEN mav1.text_value
ELSE mav2.text_value
END primary_acct_num,
emp.freight_indicator,
mbr.company_name,
mbr.name_first,
mbr.name_last,
CASE WHEN emp.freight_indicator = 'N' THEN emp.territory
WHEN emp.freight_indicator = 'Y' THEN mav2.text_value
ELSE NULL
END territory,
mav3.text_value facility_id,
CASE WHEN mbr.mbr_status = 'P' THEN NULL
ELSE mbr.enrollment_date
END registration_date,
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'
ELSE NULL
END campaign,
ern.earning_rate base_earning_rate,
mbr.current_balance
FROM mbr_fed_employee emp,
mbr_member_all mbr,
mbr_attr_value mav1,
mbr_attr_value mav2,
mbr_attr_value mav3,
mbr_level_earning_rate ern,
mbr_level lvl,
mbr_lookup mlk
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
FROM mbr_member_all
WHERE company_name LIKE '%in_company_name%'
AND in_wild_card_search_ind = 'Y')
AND mbr.name_last = (SELECT name_last
FROM mbr_member_all
WHERE name_last LIKE '%in_last_name%');
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER