ORA-00907: missing right parenthesis tips in Order by

Question

missing right parenthesis tips in Order by !!

i want to get last amn_unit_pric_rial from sal_sale_fct per shp.DAT_CALCULATE, shp.COD_PROD_SAPRB from shp_inventory_fct if row is not found in sal_sale_fct return a last price from last date in sal_sale_fct

select shp.DAT_CALCULATE,
      shp.COD_PROD_SAPRB,     
          (select sal.amn_unit_pric_rial
                  from sal_sale_fct sal 
                 where rownum = 1
                   and shp.cod_prod_saprb = sal.saprb_cod_prod_saprb
                   and shp.dat_calculate <= sal.dat_ord_ordhe
                   order by sal.dat_ord_ordhe
                   ) price
  From shp_inventory_fct shp

Answer 1

When you use rownum and order by together then the rownum is evaluated first .Hence the first row will not be in the order by of sal.dat_ord_ordhe. So you need to order by first and then select the first row using rownum

select shp.DAT_CALCULATE,
  shp.COD_PROD_SAPRB,     
      (select price 
           From  (
             select sal.amn_unit_pric_rial price
              from sal_sale_fct sal 
             where shp.cod_prod_saprb = sal.saprb_cod_prod_saprb
               and shp.dat_calculate <= sal.dat_ord_ordhe
               order by sal.dat_ord_ordhe
               ) where  rownum = 1) Price
               From shp_inventory_fct shp