[SOLVED] ASP.NET SQL Select Statement, Conditional Where by Parameter

Issue

I am working on an ASP.NET page and have gotten the search box to query a gridview of data from my Oracle database. As of right now, I have a parameter text box ("InventorySearchBox"). This enables me to search against one column. I’ve added another parameter, a drop-down list ("InventoryDropDownList"), but I can’t figure out how to work this into my select statement. Basically, ("InventoryDropDownList") has two list items right now, but could grow, based upon the list selection, I’d like the search-box to related to a different column.

So: If parameter ("InventoryDropDownList") = ‘searchbyproductcode’ then parameter ("InventorySearchBox") = tp.product_code else If parameter ("InventoryDropDownList") = ‘searchbydescription’ then parameter ("InventorySearchBox") like tic.product_description

TL;DR: The drop-down list defines what column the search box equals. Here is my code, that works without the drop-down list worked in. I’ve tried IF statements, BEGIN, END, and CASE statements, but haven’t figured it out.

SELECT tp.product_code,
    tic.product_description,
    til.location,
    SUM(trt.inventory_balance) QTY,
    trt.uom
FROM treceipt_transaction trt, tinventory_location til, tinventory_control tic, tproduct tp
WHERE trt.receipt_location = til.location AND
    trt.category_no = tic.category_no AND
    trt.product_description = tic.product_description AND
    trt.uom_schedule_id = tic.uom_schedule_id AND
    trt.uom = tic.uom AND
    tic.category_no = tp.category_no AND
    tic.product_description = tp.product_description AND
    tic.uom_schedule_id = tp.uom_schedule_id AND
    tp.product_code = :product_code AND
    trt.inventory_balance <> 0
GROUP BY tp.product_code, tic.product_description, til.location, trt.uom
ORDER BY til.location

I had tried doing something like:

 WHERE trt.receipt_location = til.location AND
        trt.category_no = tic.category_no AND
        trt.product_description = tic.product_description AND
        trt.uom_schedule_id = tic.uom_schedule_id AND
        trt.uom = tic.uom AND
        tic.category_no = tp.category_no AND
        tic.product_description = tp.product_description AND
        tic.uom_schedule_id = tp.uom_schedule_id AND
        IF(:InventoryDropDownList = 'searchbyproductcode' THEN
        tp.product_code = :InventorySearchBox ELSE IF
        :InventoryDropDownList = 'searchbydescription' THEN
        tic.product_description = :InventorySearchBox) AND
        trt.inventory_balance <> 0

Solution

You can use case to do this:

WHERE
  trt.receipt_location = til.location AND
  trt.category_no = tic.category_no AND
  trt.product_description = tic.product_description AND
  trt.uom_schedule_id = tic.uom_schedule_id AND
  trt.uom = tic.uom AND
  tic.category_no = tp.category_no AND
  tic.product_description = tp.product_description AND
  tic.uom_schedule_id = tp.uom_schedule_id AND
  CASE WHEN :InventoryDropDownList = 'searchbyproductcode' THEN tp.product_code
       WHEN :InventoryDropDownList = 'searchbydescription' THEN tic.product_description
       ELSE :InventorySearchBox
  END = :InventorySearchBox AND
  trt.inventory_balance <> 0

note by using the ELSE and having :InventorySearchBox = :InventorySearchBox allows this check to “default” to no effect if there is no selection made on the drop down or if it is invalid.

Answered By – Hogan

Answer Checked By – David Marino (BugsFixing Volunteer)