Q:

ORA-00907: missing right parenthesis tips in Order by

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

A:

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

