Архив

Архив раздела ‘SQL’

LAG IGNORE NULLS

Для Oracle Database 10g

with t as
  (
  select 1 id,   1 val from dual
  union all
  select 3 id,   45 val from dual
  union all
  select 4 id,   null val from dual
  union all
  select 5 id,   null val from dual
  union all
  select 12 id,   48 val from dual
  union all
  select 10 id,   5 val from dual
  union all
  select 15 id,   10 val from dual)
--
select id, val,
       last_value(val IGNORE NULLS) over (order by ID ROWS BETWEEN
                  UNBOUNDED PRECEDING AND 1 PRECEDING ) prev from t
order by id

Пример вывода данных:

        ID        VAL       PREV

---------- ---------- ----------

         1          1

         3         45          1

         4                    45

         5                    45

        10          5         45

        12         48          5

        15         10         48
Categories: SQL Tags: , , , ,

Конвертирование единиц измерения

Пакет inv_convert

FUNCTION inv_um_convert(p_item_id       IN NUMBER,
                        p_from_uom_code IN VARCHAR2,
                        p_to_uom_code   IN VARCHAR2) RETURN NUMBER;

Пример:

select msi.segment1,
       msi.primary_uom_code,
       'кг' as second_uom,
       inv_convert.inv_um_convert(msi.inventory_item_id,'кг',
                                  msi.primary_uom_code) as coefficient
from mtl_system_items_b msi
where msi.primary_uom_code = 'т'
and rownum <= 10