1

I have two tables. I need to form a ratio based on the config table columns. Since 100 has only PROFIT and LOSS columns ratio would be PROFIT:LOSS. Is this require Dynamic SQL.

I tried to write but I am stuck not sure how to proceed. can you please suggest.

Config Table:

enter image description here

Table - A100

enter image description here

Output: I neeed to form a ratio based on the table 1 and table 2

enter image description here

Query I tried:

    WITH col as (SELECT  COLUMN_NAME
             FROM   config
             WHERE TABLE_NAME='A100' 
            ) ,
            
 fetch_col as (    SELECT DISTINCT CUSTOMER,COLUMN_NAME,SALES,PROFIT,LOSS,DIVIDEND
                   FROM A100 CROSS JOIN dba_tab_columns 
                   WHERE COLUM_NAME IN (SELECT COLUMN_NAME FROM col)    
            )
   SELECT  CUSTOMER,PROFI||':'||LOSS
            CASE WHEN a.COLUMN_NAME=b.COLUMN_NAME THEN PROFIT||':'||LOSS ELSE '' END         
   FROM fetch_col a INNER JOIN col b
   ON a.COLUMN_NAME=b.COLUMN_NAME
5
  • I don't see what table 1 has to do with this. Commented Jan 28, 2021 at 17:36
  • I need to read table 1 to check which columns to use to form a ratio and then read table 2 to form a ratio based on it Commented Jan 28, 2021 at 17:41
  • So your config table will always have 2 values for table - A100? As ration includes only 2 values. Why you have used distinct? Commented Jan 28, 2021 at 17:52
  • yes DISTINCT not needed ,updated now. It has only two values Commented Jan 28, 2021 at 17:52
  • How do you know it should be 'PROFIT:LOSS' and not 'LOSS:PROFIT' - does the config table have another column that indicates the order? And does "Since 100 has only..." imply some tables will have more that two columns in the config table; and if so what are you expecting to happen? Or... are you specifically looking for PROFIT and LOSS in the config table, and ignoring everything else? It's unclear if you're looking in config to get the column names; or only looking for fixed names. Commented Jan 28, 2021 at 18:18

2 Answers 2

2

I have tried to solve your problem. Maybe it does not fit yet.

I have extended the table "config" by a column "column_type" to identify which entry represents numerator and which denominator.

CREATE OR REPLACE PACKAGE pkg_demo
AS
   TYPE r_out IS RECORD
      ( table_name   VARCHAR2(30),
        customer     VARCHAR2(30),
        ratio        VARCHAR2(30)
      );
   TYPE t_out IS TABLE OF r_out;

   FUNCTION get_ratio ( i_table  IN VARCHAR2 )
      RETURN t_out PIPELINED;
   
END pkg_demo;
/

CREATE OR REPLACE PACKAGE BODY pkg_demo
AS

   FUNCTION get_ratio ( i_table  IN VARCHAR2 )
      RETURN t_out PIPELINED
   AS
      l_num config.column_name%TYPE;
      l_den config.column_name%TYPE;
      l_sql VARCHAR2(32767);
      TYPE cur_type IS REF CURSOR;
      l_cur cur_type;
      l_rec r_out;
   BEGIN
      SELECT config.column_name
        INTO l_num
        FROM config
       WHERE table_name = i_table
         AND column_type = 'N';
      
      SELECT config.column_name
        INTO l_den
        FROM config
       WHERE table_name = i_table
         AND column_type = 'D';

      l_sql := 'SELECT ''' || TRIM(LEADING 'A' FROM i_table) || ''', customer, TO_CHAR(' || l_num || ') ||'':''|| TO_CHAR('|| l_den || ', ''FM00'') FROM ' || i_table;
      
      OPEN l_cur FOR l_sql;
      LOOP
         FETCH l_cur INTO l_rec;
         EXIT WHEN l_cur%NOTFOUND;
         
         PIPE ROW(l_rec);
      END LOOP;
      
      CLOSE l_cur;
         
   END get_ratio;
   
END pkg_demo;
/

SELECT * FROM TABLE(pkg_demo.get_ratio('A100'));

Result:

TABLE_NAME  CUSTOMER  RATIO                         
----------- --------- ---------
100         Microsoft 10:01                         
100         Tesla     15:02     
Sign up to request clarification or add additional context in comments.

Comments

2

You can use DBMS_XMLGEN.GETXML to generate dynamic columns in a single SQL statement:

--Convert the XML to relational data.
select table_name, customer, ratio
from
(
    --Convert the SQL statement into XML results.
    select XMLType(dbms_xmlgen.getXML(sql)) results
    from
    (
        --Generate a SQL statement with column names based on configuration.
        select table_name,
            replace(replace(replace(
                q'[select '#TABLE_NAME#' table_name, customer, #COLUMN_1# || ':' || #COLUMN_2# ratio from #TABLE_NAME#]'
            , '#TABLE_NAME#', table_name)
            , '#COLUMN_1#', max(column_name) keep(dense_rank first order by column_order))
            , '#COLUMN_2#', max(column_name) keep(dense_rank first order by column_order desc)) sql
        from config
        group by table_name
    )
)
cross join
xmltable
(
    '/ROWSET/ROW'
    passing results
    columns
        table_name varchar2(128) path 'TABLE_NAME',
        customer   varchar2(128) path 'CUSTOMER',
        ratio      varchar2(128) path 'RATIO'
);

See this db<>fiddle for a runnable example.

A few notes about this solution:

  1. This XML trick only works when you have a set number of dynamic columns.
  2. You want to avoid this kind of dynamic solution when possible. Dynamic SQL in SQL is sometimes unavoidable, but usually there is a way to improve your data model to make the SQL simpler. Or perhaps this type of dynamic code is best handled by an application.
  3. As Thomas Kirchhoff pointed out, the solution requires some way of knowing which column comes first so I added a COLUMN_ORDER value.
  4. Compared to Thomas' solution, my solution is a bit smaller and does not require creating additional objects. On the other hand, my solution is more cryptic will run slower. Which answer is best probably depends on how often you will use these queries. If this is a rare query, it's simpler to just use DBMS_XMLGEN and get it done. If this is a common query, it's worth creating and maintaining a package to simplify the query.

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.