0

How to replace a concrete value with a variable in Oracle SQL? I have

 select 5, min(id) from my_table where id > 5 --AND ... complex condition
 UNION ALL     
 select 6, min(id) from my_table where id > 6  --AND ... complex condition
  UNION ALL
  ....  

 | 5 | 6 |
 | 6 | 8 |
 ...

How to wrap it in a function executing pseudocode below?

for ( $i in ( select id from my_table)){
    UNION ALL
    select $i, min(id) from my_table where id > $i  --AND ...
 }

To make it clear, I am looking for a general method to turn a select with hard wired values into a function that accepts variables. Note the --AND part. This is NOT a question about using lead and over but about replacing values with variables.


I am trying to replace START WITH ID=10 with a variable in this query.

WITH t AS (SELECT 1 ID, 'A' in_node, 'B' out_node, 17 weight FROM dual UNION ALL
           SELECT 2 ID, 'B' in_node, 'A' out_node, 4 weight FROM dual UNION ALL
           SELECT 3 ID, 'C' in_node, 'A' out_node, 5 weight FROM dual UNION ALL
           SELECT 4 ID, 'A' in_node, 'D' out_node, 6 weight FROM dual UNION ALL
           SELECT 5 ID, 'C' in_node, 'G' out_node, 33 weight FROM dual UNION ALL
           SELECT 6 ID, 'X' in_node, 'Z' out_node, 12 weight FROM dual UNION ALL
           SELECT 7 ID, 'Z' in_node, 'Y' out_node, 15 weight FROM dual UNION ALL
           SELECT 8 ID, 'X' in_node, 'Y' out_node, 42 weight FROM dual UNION ALL
           SELECT 9 ID, 'K' in_node, 'M' out_node, 66 weight FROM dual UNION ALL
           SELECT 10 ID, 'A' in_node, 'Z' out_node, 20 weight FROM dual),
   res AS (SELECT ID,
                  in_node,
                  out_node,
                  weight,
                  MAX(CASE WHEN ID = connect_by_root(ID) THEN in_node END) OVER () orig_in_node,
                  MAX(CASE WHEN ID = connect_by_root(ID) THEN out_node END) OVER () orig_out_node,
                  MAX(CASE WHEN ID = connect_by_root(ID) THEN ID END) OVER () orig_id,
                  CASE WHEN MAX(CASE WHEN ID = connect_by_root(ID) THEN in_node END) OVER () IN (in_node, out_node) THEN 'in'
                       ELSE 'out'
                  END direction_from_orig_node,
                  LEAST(in_node, out_node) node1,
                  GREATEST(in_node, out_node) node2,
                  row_number() OVER (PARTITION BY LEAST(in_node, out_node), GREATEST(in_node, out_node) ORDER BY weight) rn
           FROM   t
           START WITH ID = 10
           CONNECT BY NOCYCLE (PRIOR out_node IN (in_node, out_node)
                              OR PRIOR in_node IN (in_node, out_node))
                              AND LEVEL <= 2)
SELECT orig_id,
       COUNT(DISTINCT CASE WHEN direction_from_orig_node = 'in' THEN node1||'~'||node2 END) in_count,
       nvl(SUM(CASE WHEN direction_from_orig_node = 'in' THEN weight END), 0) in_sum,
       COUNT(DISTINCT CASE WHEN direction_from_orig_node = 'out' THEN node1||'~'||node2 END) out_count,
       nvl(SUM(CASE WHEN direction_from_orig_node = 'out' THEN weight END), 0) out_sum
FROM   res
WHERE  rn = 1
AND    ID != orig_id
GROUP BY orig_id;

Apparently I can use this template, but do i have to use a pointer for this?

--creates a function
CREATE OR REPLACE Function dummy_function   ( name_in IN number )
   RETURN number
IS
   cnumber number;
   cursor c1 is
   SELECT weight FROM dummy_links WHERE ID = name_in;
BEGIN
   open c1;
   fetch c1 into cnumber;

   if c1%notfound then
      cnumber := 9999;
   end if;
   close c1;
RETURN cnumber+1; --to have some diversity we add 1

EXCEPTION
WHEN OTHERS THEN
   raise_application_error(-20001,'An error was encountered - '||SQLCODE||' -ERROR- '||SQLERRM);
END;

--calling a function
SELECT id, weight, dummy_function(id) AS weight_plus_one FROM dummy_links;
1

If I undferstand correctly, all you need is a bind variable.

SQL> variable sw number
SQL> exec :sw := 10;

PL/SQL procedure successfully completed.

WITH t AS (SELECT 1 ID, 'A' in_node, 'B' out_node, 17 weight FROM dual UNION ALL
           SELECT 2 ID, 'B' in_node, 'A' out_node, 4 weight FROM dual UNION ALL
           SELECT 3 ID, 'C' in_node, 'A' out_node, 5 weight FROM dual UNION ALL
           SELECT 4 ID, 'A' in_node, 'D' out_node, 6 weight FROM dual UNION ALL
           SELECT 5 ID, 'C' in_node, 'G' out_node, 33 weight FROM dual UNION ALL
           SELECT 6 ID, 'X' in_node, 'Z' out_node, 12 weight FROM dual UNION ALL
           SELECT 7 ID, 'Z' in_node, 'Y' out_node, 15 weight FROM dual UNION ALL
           SELECT 8 ID, 'X' in_node, 'Y' out_node, 42 weight FROM dual UNION ALL
           SELECT 9 ID, 'K' in_node, 'M' out_node, 66 weight FROM dual UNION ALL
           SELECT 10 ID, 'A' in_node, 'Z' out_node, 20 weight FROM dual),
   res AS (SELECT ID,
                  in_node,
                  out_node,
                  weight,
                  MAX(CASE WHEN ID = connect_by_root(ID) THEN in_node END) OVER () orig_in_node,
                  MAX(CASE WHEN ID = connect_by_root(ID) THEN out_node END) OVER () orig_out_node,
                  MAX(CASE WHEN ID = connect_by_root(ID) THEN ID END) OVER () orig_id,
                  CASE WHEN MAX(CASE WHEN ID = connect_by_root(ID) THEN in_node END) OVER () IN (in_node, out_node) THEN 'in'
                       ELSE 'out'
                  END direction_from_orig_node,
                  LEAST(in_node, out_node) node1,
                  GREATEST(in_node, out_node) node2,
                  row_number() OVER (PARTITION BY LEAST(in_node, out_node), GREATEST(in_node, out_node) ORDER BY weight) rn
           FROM   t
           START WITH ID = :sw
           CONNECT BY NOCYCLE (PRIOR out_node IN (in_node, out_node)
                              OR PRIOR in_node IN (in_node, out_node))
                              AND LEVEL <= 2)
SELECT orig_id,
       COUNT(DISTINCT CASE WHEN direction_from_orig_node = 'in' THEN node1||'~'||node2 END) in_count,
       nvl(SUM(CASE WHEN direction_from_orig_node = 'in' THEN weight END), 0) in_sum,
       COUNT(DISTINCT CASE WHEN direction_from_orig_node = 'out' THEN node1||'~'||node2 END) out_count,
       nvl(SUM(CASE WHEN direction_from_orig_node = 'out' THEN weight END), 0) out_sum
FROM   res
WHERE  rn = 1
AND    ID != orig_id
GROUP BY orig_id;

   ORIG_ID   IN_COUNT     IN_SUM  OUT_COUNT    OUT_SUM
---------- ---------- ---------- ---------- ----------
        10          3         15          2         27

Then change the value of the variable to whatever you need:

SQL> exec :sw := 9;

PL/SQL procedure successfully completed.

SQL> /

no rows selected

SQL> exec :sw := 8;

PL/SQL procedure successfully completed.

SQL> /

   ORIG_ID   IN_COUNT     IN_SUM  OUT_COUNT    OUT_SUM
---------- ---------- ---------- ---------- ----------
         8          1         12          1         15

SQL> exec :sw := 3;

PL/SQL procedure successfully completed.

SQL> /

   ORIG_ID   IN_COUNT     IN_SUM  OUT_COUNT    OUT_SUM
---------- ---------- ---------- ---------- ----------
         3          1         33          3         30
|improve this answer|||||
  • Almost. Now I need to glue exec :sw := 3; into select t.*, blob.in_count,blob.in_sum from t, blob where blob.id = t.id where 'blob' is the fing starting at WITH t AS and ending at GROUP BY orig_id; I need a table of rows for each id. Function might work better. – Stepan Nov 8 '18 at 18:22

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.