We help IT Professionals succeed at work.

Complex Oracle Query

alscincy asked
Last Modified: 2013-12-18

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,
            CASE WHEN emp.freight_indicator = 'N' THEN mav1.text_value
               ELSE mav2.text_value
            END primary_acct_num,
           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,
      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%');
Watch Question

Top Expert 2005
This one is on us!
(Get your first solution completely free - no credit card required)


Thank you so much.  For some reason, I was thinking that the nvl's would not work in this situation.  I spent 3 hours trying to figure this out so I really appreciate your help.
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.


Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.