filter a column based on another column in oracle query-CodePudding

I have table like this :

ID         | key | value
1          | A1  |o1
1          | A2  |o2
1          | A3  |o3
2          | A1  |o4
2          | A2  |o5
3          | A1  |o6
3          | A3  |o7
4          | A3  |o8

I want to write a oracle query that can filter value column based on key column . some thing like this

 ID=1
    and 
    if key = A1 then value ='o1' 
    and key = A3 then value ='o4'

please help me to write this query.

thanks

CodePudding user response：

IF means PL/SQL. In SQL, we use CASE expression instead (or DECODE, if you want). Doing so, you'd move value out of the expression and use something like this:

where id = 1
  and value = case when key = 'A1' then 'o1'
                   when key = 'A3' then 'o4'
              end

CodePudding user response：

You are mixing filtering and selection. List the columns that you want to display in the SELECT list and the columns used to filter in the WHERE clause

SELECT "key", "value"
FROM my_table
WHERE ID = 1 AND "key" IN ('A1', 'A2')

If there is no value column in your table, you can use the DECODE function

SELECT "key", DECODE("key", 'A1', 'o1', 'A2', 'o4', "key") AS "value"
FROM my_table
WHERE ID = 1

After the key, you must specify pairs of search and result values. The pairs can be followed by a default value. In this example, since we did not specify a result for 'A3', the result will be the key itself. If no default value was specified, NULL would be returned for missing search values.

Note that key and value are reserved words in Oracle, so I escaped them with "reserved_word".

CodePudding user response：

I asked for clarification under your question.

For this answer, I will assume that you meant the following: key must be 'A1' or 'A3'; and value must be 'o1' when key = 'A1', resp. 'o4' when key = 'A3'.

If so, then the simplest way to write the filter is like this:

...
where id = 1 and (key, value) in (('A1', 'o1'), ('A3', 'o4'))
...