CODEWITHSUNDEEP
sqloracle-databaseplsql
Caleb
Caleb

Reputation: 359

A function that returns different column values based on input

Here is a table FRUITS -

FILTER  A   B   C
Apple   0   3   6
Orange  1   4   7
Apricot 2   5   8

Is it possible to create a function that returns column A for apple (0), B for Orange(4) and C for Apricot(8)?

SELECT Fruit_Function('Apple') FROM dual; --RESULT 0.
SELECT Fruit_Function('Orange') FROM dual; --RESULT 4.
SELECT Fruit_Function('Apricot') FROM dual; --RESULT 8.

Right now I have a function like this

CREATE OR REPLACE FUNCTION Fruit_Function (v_id integer, v_filter varchar2)
RETURN varchar2 as

    v_rp varchar2(500);

BEGIN
    SELECT ASSIGNEE INTO v_rp
    FROM FRUITS a
    WHERE a.id = v_id AND a.filter = v_filter;

    RETURN v_rp;
END Fruit_Function;

This function returns the column name, but not the actual value.

Upvotes: 1

Views: 268

Answers (4)

yukclam9
yukclam9

Reputation: 336

Create a function that takes 2 input parameters called filter_value, col_value

For the function definition,

  1. Define a variable called v_result
  2. Select
  3. Execute immediate ‘ select ‘ || col_value || ‘ from your_table where filter=‘’’ || filter_value || ‘’’’ into v_result;
  4. Return v result;

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1270873

Create a view!

create view v_t as
    select t.*,
           (case when filter = 'Apple' then a
                 when filter = 'Orange' then b
                 when filter = 'Apricot' then C 
           end) as result
    from t;

Then, use the view for the queries in your package.

You can also add the column as a computed column directly into the table:

alter table t add result number generated always as
           (case when filter = 'Apple' then a
                 when filter = 'Orange' then b
                 when filter = 'Apricot' then C 
            end);

With this approach, the column looks like it is part of the table.

Upvotes: 2

Olivier Jacot-Descombes
Olivier Jacot-Descombes

Reputation: 112712

You can use a case expression

SELECT
    CASE filter WHEN 'Apple' THEN A WHEN 'Orange' THEN B WHEN 'Apricot' THEN C END AS result
FROM
    myTable

If you want to reuse it 100+ times, create a view from it and output all the other columns as well

CREATE OR REPLACE VIEW myTableEx
   SELECT
      CASE filter WHEN 'Apple' THEN A WHEN 'Orange' THEN B WHEN 'Apricot' THEN C END AS result,
      filter, A, B, C, ...
   FROM
      myTable;

Upvotes: 0

Ismail
Ismail

Reputation: 1316

You can use below logic in function

Function(input)
Begin
If(input =='Apple')
Select A from table name
Else if(input =='something')
Select B from table name
....

Upvotes: 0

Related Questions