If Else changes where clause in Oracle

Question

I'm trying to add a line to my WHERE clause based on a value selected by the user on a Japser iReport web interface.

Select KeyId, Name, WorkOrder
FROM orderTbl oT
WHERE

------- ?????????????????? --------------

If userSelectedValue = 'Load'
        oT.workOrder = 'accepted'
End If

------- ?????????????????? --------------

I tried to follow a CASE example but that creates a new column with the results but don't know how to use the new value to only get the records I want.

Select KeyId, Name, WorkOrder
case 
  when userSelectedValue = 'Load' and workOrder  ='accepted' then 'useWorkOrder'
     else 'noWorkOrder'
end as "dropDown",

dropDown          KeyId  Name           WorkOrder
----------------  -----  -------------  --------------
useWorkOrder      45     John Doe       accepted
useWorkOrder      78     Bill Kearns    accepted
noWorkOrder       23     Sandy York     null

My Final Goal:
Retrieve 2 records if user selects "Load" from a drop-down (userSelectedValue="Load")
- 45     John Doe 
- 78     Bill Kearns
Retrieve 1 record if user selects "Retrieve" from a drop-down (userSelectedValue="Retrieve")
- 23     Sandy York

Are you saying that you only want those rows returned where dropDown = 'useWorkOrder'? — Mark J. Bobak, Apr 29 '14 at 15:13
Is this purely PL/SQL or is there a front-end passing parameters into a procedure? — Maurice Reeves, Apr 29 '14 at 15:32
Hi Mark + Maurice I updated the description to answers your questions. Our system is not PL/SQL — Shij, Apr 29 '14 at 16:35

Joseph B · Answer 1 · 2014-04-29T16:31:07.297

0

You do not have to use CASE. You can use compound conditions:

SELECT KeyId, Name 
FROM orderTbl
WHERE (userSelectedValue = 'Load' and workOrder = 'accepted' AND DropDown = 'useWorkOrder')
OR    (DropDown = 'noWorkOrder');

edited Apr 29 '14 at 16:31

answered Apr 29 '14 at 15:59

Joseph B

5,519
1
15
19

score 0 · Answer 2 · answered Apr 29 '14 at 16:26

0

The simplest modification you can do is:

Select KeyId, Name 
WHERE case 
  when userSelectedValue = 'Load' and workOrder  ='accepted' then 'useWorkOrder'
     else 'noWorkOrder'
end = 'useWorkOrder'

answered Apr 29 '14 at 16:26

Nikolay Dimitrov

1,766
1
14
23

score 0 · Accepted Answer · answered Apr 30 '14 at 17:38

Solution:

I used a combo of all your ideas to get this working. Special thanks to Joseph B + gubble.

Problem:

Select KeyId, Name, WorkOrder
FROM orderTbl oT
WHERE
If userSelectedValue = 'Load'
        oT.workOrder = 'accepted'
End If

Answer:

Select KeyId, Name, WorkOrder
FROM orderTbl oT
WHERE
 case 
    when (oT.KeyId = userSelectedValue AND oT.workOrder = 'accepted'
          OR de.KeyId = userSelectedValue)
       then 'useWorkOrder'
       else 'noWorkOrder'
 end = 'useWorkOrder'

If Else changes where clause in Oracle

3 Answers3