CODEWITHSUNDEEP
oracle-databaseoracle11goracle-sqldeveloperoracle-apex
Roy
Roy

Reputation: 3

Data manipulation in APEX 20 oracle

Currently I am performing dynamic action (executing server side code) where I am selecting the values from two different tables (XYZ & ABC) performing calculation and inserting into another table (ABC_TEMP) and creating a report view out of that in apex(v20) Below is what I am performing.

BEGIN 
        INSERT INTO ABC_TEMP (
            A1,           --> VARCHAR2(4000)
            B1,           --> VARCHAR2(4000)
            C1,           --> NUMBER
            D1,           --> NUMBER
            E1,           --> NUMBER
            F,            --> NUMBER
            G1,           --> NUMBER
            H3,           --> NUMBER
            I3,           --> NUMBER
            J,            --> NUMBER
            K,            --> NUMBER
            L,            --> NUMBER
            timestamp     -->timestamp(6)
            )
        VALUES (
            :A_SELECT, 
            :B_SELECT, 
            :C_SELECT,
            (SELECT D2 FROM XYZ WHERE B2 = :B_SELECT AND C2 = :C_SELECT),
            (SELECT E2 FROM XYZ WHERE B2 = :B_SELECT AND C2 = :C_SELECT),
            (SELECT SUM(D2 + E2) FROM XYZ WHERE B2 = :B_SELECT AND C2 = :C_SELECT),
            (SELECT G2 FROM XYZ WHERE B2 = :B_SELECT AND C2 = :B_SELECT),
            (SELECT H2 FROM ABC WHERE A2 = :A_SELECT AND P2 = 'mock1' AND SE = 'mock2' AND Q2 = 'val1'),
            (SELECT H2 FROM ABC WHERE A2 = :A_SELECT AND P2 = 'mock1' AND SE = 'mock2' AND Q2 = 'val2'),
            (:J),  --> This value is derived from `ABC_TEMP` table only by dividing I3 BY F 
            (:K),  --> This value is derived from `ABC_TEMP` table only by dividing H3 BY G1 
            (:L),  --> this value is derived from  low of J & K column 
            (CURRENT_TIMESTAMP)
            );
END;

My question is how do I set the values of column J,K,L in the same query as it involves selecting from the same table and performing calculation on top of it where I am inserting data. if this is not possible what can be other approach out here.

Upvotes: 0

Views: 46

Answers (1)

Littlefoot
Littlefoot

Reputation: 143053

Literally copy/paste those columns' source (select statements) and divide them:

values
  (:A_SELECT, 
   ...
   -- for J, literally copy/paste I3 / F
   (SELECT H2 FROM ABC WHERE A2 = :A_SELECT AND P2 = 'mock1' AND SE = 'mock2' AND Q2 = 'val2') /
   (SELECT SUM(D2 + E2) FROM XYZ WHERE B2 = :B_SELECT AND C2 = :C_SELECT)
   -- the same goes for other columns
  );

Does it work? Sure:

SQL> select (select sal from emp where rownum = 1) /
  2         (select empno from emp where rownum = 1) as j
  3  from dual;

         J
----------
,108562899

SQL>

Is it optimized? Of course not, you'll be using the same query twice (once for the "original" column and once for "derived" one).

Can you optimize it? Maybe. Try to create bunch of CTEs (one for each subquery you used) and then re-use it for derived columns.

On the other hand, why would you store such a value into the table? That's redundant. Omit (drop) columns J, K and L from the table and compute their value whenever needed, e.g.

select a1, 
       c1,
       i3 / f as J      --> this
from abc_temp
where ...

Or, you could even create a view using the same select (I posted above) and select values from a view.

Upvotes: 0

Related Questions