ORACLE - How to make hierarchical query

In this example you can see hierarchical query with start with and connect by section.

• start with - is initial row (data) for question (..here key to top item).
• connect by - tells, how the new data are connected to previous row.

Here is example from BOM table:

select substr(lpad(' ', level) || level, 1, 10) as level_,
a.tl_nr, a.tl_unt_nr, a.tl_unt_fam, a.tl_unt_atn, 
a.mng_pos_re_mz as quantity, a.mng_pos_me as unit
from ertesr a
start with 
a.tl_nr = '442-97345-622-1' and
a.tl_fam = '0' and 
a.tl_atn = '02'
connect by prior a.tl_unt_nr = a.tl_nr
and prior a.tl_unt_fam = a.tl_fam
and prior a.tl_unt_atn = a.tl_atn
order by rownum

Output:

LEVEL_     TL_NR                TL_UNT_NR            TL_UNT TL_UNT   QUANTITY UN
---------- -------------------- -------------------- ------ ------ ---------- --
 1         442-97345-622-1      S-312-221-1001       0      0      ,025       KG
 1         442-97345-622-1      442-90780-566-1      0      0               1 KS
  2        442-90780-566-1      S-137-114-1060       0      0      ,6         KG
 1         442-97345-622-1      S-246-931-1005       0      0      ,019       KG
 1         442-97345-622-1      S-821-303-0144       0      0               1 KS
 1         442-97345-622-1      442-05011-145-1      0      0               2 KS
  2        442-05011-145-1      S-331-123-2058       0      0               1 KS

7 rows selected. 

ORACLE - How get last day in the month

select sysdate, last_day( sysdate ) from dual;

select last_day( to_date( '201801', 'yyyymm' ) ) from dual;

Output:

SYSDATE  LAST_DAY
-------- --------
22.01.18 31.01.18

LAST_DAY
--------
31.01.18

ORACLE - select with limiting rows count (..how to show first 5 rows)

select tl_nr, dat_anf from ertest
where
rownum <= 5

Output:

TL_NR                DAT_ANF 
-------------------- --------
S-319-421-1000       12.02.97
-94547-508           12.02.97
-90391-127           12.02.97
-94547-517           12.02.97
-94215-137           12.02.97

ORACLE, SQL SERVER - Typical subselect

Typical SQL subselect for finding value:

update factory.an_turnover a
set
a.c_last_date = ( select max( bew_dat ) from factory.himtbw
                  where
                  tl_nr = a.tl_nr and
                  tl_fam = a.tl_fam and
                  tl_atn = a.tl_atn and
                  bew_art in ( 'PP', 'PV', 'PN' ) and 
                  lag_ort not in ( '700', '701' )
                 )
;

commit;

ORACLE - SELECT which returns only 1 row

This example returns only one (or no) row:

declare
  iPrice number;
begin
  ..
  select kost_soll_voll_mz into iPrice
  from erhtks
  where 
  tl_nr = pTl_nr and 
  tl_fam = pTl_fam and 
  tl_atn = pTl_atn and 
  koa_nr = pKoa_nr and 
  kal_auf_nr_ursp = pKal_auf_nr and 
  rownum = 1;

ORACLE - How handle pl/sql exception

Typical example for handling pl/sql exception (when no row is found in select):

is
  iCount number;
  /* take type from table field */
  iKategp factory.an_hr_table.kategp%type;
begin
...
  begin
    select kategp into iKategp
    from factory.an_hr_table 
    where
    osc = pOsc;
  exception
    when NO_DATA_FOUND then
      pErrorMessage := 'No data found for worker: ' || pOsc;
      return( -1 );
  end;
...

ORACLE - example of pl/sql function (stored function)

Stored function takes two params and returns number value (0 when row is added):

function sql_addToFavorite( pUser in varchar2, pID in number ) return number
is
  iCount number;
begin
  /* exists ? */

  select count(*) into iCount
  from factory.cieb_an_sql_favorite
  where
  upper( c_user ) = upper( pUser ) and
  c_ID = pID;

  if iCount <> 0 then
    return( 1 );
  end if;

  /* add */

  insert into factory.cieb_an_sql_favorite
  ( c_user, c_id )
  values
  ( upper( pUser ), pID );

  commit;

  return( 0 );
end;

ORACLE - How use conditional logic in select, decode() statement

When is in field pos_dbf_ze value "HO" (=hour) is used pos_bdf_mz. In another case is pos_bdf_mz divided by value 60.

select sum( decode( pos_bdf_ze, 'HO', pos_bdf_mz, pos_bdf_mz/60 ) )
from aukabl
where 
arb_ord_nr like '16%'