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;
Best Answer
If I undferstand correctly, all you need is a bind variable.
Then change the value of the variable to whatever you need: