drop type US_NotenObjTyp;
drop type D_NotenObjTyp;
drop type NotenObjTyp;
drop function USnachD_SQL;
drop type D_NotenTyp;
drop type US_NotenTyp;


create distinct type D_NotenTyp
  as decimal(3,2)
  with comparisons;
  
create distinct type US_NotenTyp
  as decimal(3,2)
  with comparisons;
  
create function USnachD_SQL(us US_NotenTyp) returns D_NotenTyp
  return( case when decimal(us) < 1.0 then D_NotenTyp(5.0)
               when decimal(us) < 1.5 then D_NotenTyp(4.0)
               when decimal(us) < 2.5 then D_NotenTyp(3.0)
               when decimal(us) < 3.5 then D_NotenTyp(2.0)
               else D_NotenTyp(1.0) end);
               
  
  
create type NotenObjTyp as (
  Land varchar(20),
  StringWert varchar(20))  
  not final
  not instantiable
  mode DB2SQL
    method getnote ()
    returns float
    language SQL
    deterministic
    reads sql data
    no external action;

  
  
create type US_NotenObjTyp under NotenObjTyp as(
  NumWert US_NotenTyp,
  WithHonors char(1) )
  instantiable
  mode DB2SQL
    overriding method getnote() returns float;    
    
    
create type D_NotenObjTyp under NotenObjTyp as(
  NumWert D_NotenTyp,
  Lateinisch varchar(15) )
  instantiable
  mode DB2SQL
    overriding method getnote() returns float;   
    
    
create method getnote() for US_NotenObjTyp
  RETURN  float(decimal(USnachD_SQL(self..NumWert)));
  
  
create method getnote() for D_NotenObjTyp
  RETURN  float(decimal(self..NumWert));