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

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?

Answer

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?