0

My goal is to make a function to select a data in Tableau with vertical and horizontal coordinates. How to make my function body? The function should select column dynamically according to function parameter I_HORIZENT, which I have no idea how to handle with.

For example, if vertical coordinate is 'E1' and horizontal coordinate is 'RP' then the function should return '12'. Vertical coordinate is the one column K1. Horizontal coordinate is the one row with column NUM = 0.

  NUM  K1    K2  K3  K4 
  --- ---  ---- ---- ----
  0          RK  RP   RN
  1   E1     10  12   11 
  2   E2     21  23   19

Table and My Function are as below

CREATE TABLE DATAA ( 
NUM VARCHAR2(15),
K1 VARCHAR2(15),
K2 VARCHAR2(15),
K3 VARCHAR2(15),
K4 VARCHAR2(15)
);

INSERT INTO DATAA VALUES('0',NULL,'RK','RP','RN');
INSERT INTO DATAA VALUES('1','E1','10','12','11');
INSERT INTO DATAA VALUES('2','E2','21','23','19');

SELECT * FROM DATAA;



CREATE OR REPLACE FUNCTION MYFUN
(
I_VERTICAL IN VARCHAR2.
I_HORIZENT IN VARCHAR2
)RETURN VARCHAR2 AS V_VALUE VARCHAR2;
BEGIN
---



?  --How to make my function body?



---
RETURN V_VALUE;
END;
1

You don't need dynamic SQL for this; you can use UNPIVOT:

WITH unpivoted ( num, col_name, col_index, value ) AS (
  SELECT *
  FROM   DATAA
  UNPIVOT ( value FOR col_index IN ( K2 AS 2, K3 AS 3, K4 AS 4 ) )
)
SELECT r.value
FROM   ( SELECT * FROM unpivoted WHERE value = 'RP' AND num = '0' ) c
       INNER JOIN
       ( SELECT * FROM unpivoted WHERE col_name = 'E1' ) r
       ON ( r.col_index = c.col_index )

Outputs:

| VALUE |
| :---- |
| 12    |

db<>fiddle here

If you want to wrap it into a function then:

CREATE OR REPLACE FUNCTION MYFUN
(
  I_VERTICAL   IN VARCHAR2,
  I_HORIZONTAL IN VARCHAR2
) RETURN VARCHAR2
AS
  V_VALUE VARCHAR2(15);
BEGIN
  WITH unpivoted ( num, col_name, col_index, value ) AS (
    SELECT *
    FROM   DATAA
    UNPIVOT ( value FOR col_index IN ( K2 AS 2, K3 AS 3, K4 AS 4 ) )
  )
  SELECT r.value
  INTO V_VALUE
  FROM   ( SELECT * FROM unpivoted WHERE value = I_HORIZONTAL AND num = '0' ) c
         INNER JOIN
         ( SELECT * FROM unpivoted WHERE col_name = I_VERTICAL ) r
         ON ( r.col_index = c.col_index );
  RETURN V_VALUE;
EXCEPTION
  WHEN NO_DATA_FOUND THEN
    RETURN NULL;
END;
/

and

SELECT MYFUN( 'E1', 'RP' ) FROM DUAL;

outputs:

| MYFUN('E1','RP') |
| :--------------- |
| 12               |

db<>fiddle here

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.