Oracle SQL select greatest value of the result of two different select statements-CodePudding

I'm running select statements in a loop with a cursor to collect data from different tables; Quick (NOT WORKING) example;

select DISTINCT(ordernr) from orders 

INSERT INTO newtable (select Crs.ordernr as ordernr, value1 as value from table2 where table2.ordernr = Crs.ordernr );
INSERT INTO newtable (select Crs.ordernr as ordernr, value2 as value from table3 where tabel3.ordernr=Crs.ordernr ;

)
LOOP
END LOOP;
END;

What I could like is just one insert statement which insert just the biggest value of boths select statements. I've tried to work with greatest function in my loop but i'm running stuck. Both datatypes are the same for value1 and value2

insert into newtable(select crs.ordernr as ordernr, greatest
(
select value1 as value from table1 where condition=1, select value2 as value from table2 where condition=1)
)
);

Is it possible with a select case or other way to return only one value based on conditions? For example the biggest value of value1 or value2?

CodePudding user response：

You can use the greatest function and provide subqueries to it, but - follow the syntax, i.e. enclose each of them (select statements) into its own parenthesis.

Something like this:

SQL> select greatest (  (select max(sal) from emp where job = 'CLERK'),
  2                     (select max(sal) from emp where job = 'ANALYST')
  3                  ) greatest_salary
  4  from dual;

GREATEST_SALARY
---------------
           3450

SQL>

I have no idea what is this:

insert into newtable(select crs.ordernr as ordernr
                     -----------------------------

supposed to do; what is crs? Where's the from clause?

CodePudding user response：

From Oracle 12, you can use:

INSERT INTO newtable (ordernr, value)
SELECT *
FROM   (
  SELECT Crs.ordernr as ordernr,
         value1      as value
  FROM   table2
  WHERE  table2.ordernr = Crs.ordernr
UNION ALL
  SELECT Crs.ordernr,
         value2
  FROM   table3
  WHERE  table3.ordernr=Crs.ordernr
)
ORDER BY value DESC
FETCH FIRST ROW ONLY;

This will mean that if you extend the query to extract more columns and you want the highest of one column and the corresponding values of the other columns then the values will all come from that one row with the highest value.

db<>fiddle here