CREATE OR REPLACE function column_datatype (
	   	  		  		   con_datatype in number)
-- This function converts a concept-datatype
-- into a columnset-datatype.
--
-- Parameters:
-- con_datatype- datatype of concept
-- Return; column_datatype
--
-- Author: Regina Zücker, Swiss Life, Zürich.
-- Date: June 20th, 2001.
-- Probably depreciated !
return number
as
  rV number := 0;
begin
	 --nominal to string
	 if con_datatype = 1 then rV := 13;
	 --categorial to number
	 elsif con_datatype = 2 then rV := 12;
	 --keyattrib to key
     elsif con_datatype = 3 then rV := 15;
	 --constant to number
	 elsif con_datatype = 4 then rV := 12;
	 --timegroup to date
	 elsif con_datatype = 5 then rV := 14;
	 --spatial to number
	 elsif con_datatype = 6 then rV := 12;
	 --numeric to number
	 elsif con_datatype = 7 then rV := 12;
	 --ordinal to number
	 elsif con_datatype = 8 then rV := 12;
     --scalar to number
	 elsif con_datatype = 9 then rV := 12;
	 --time to date
	 elsif con_datatype = 10 then rV := 14;
     --binary to number
	 elsif con_datatype = 11 then rV := 12;
	 end if;

return rV;
end;
/

CREATE OR REPLACE FUNCTION m4_date_to_timestamp(d in DATE) return NUMBER is
begin
        return TO_CHAR(d,'J')*86400+TO_CHAR(d,'SSSSS');
end;
/

CREATE OR REPLACE FUNCTION m4_timestamp_to_date(d in NUMBER) return DATE is
begin
        return TO_DATE(TO_char(trunc(d/86400)) || '-' || TO_CHAR(mod(d,86400)) ,'J-SSSSS');
--        return TO_DATE(trunc(d/86400),'J')+TO_DATE(mod(d,86400),'SSSSS');
end;
/

CREATE OR REPLACE function mm_add (value in number, addValue in number)
return number
as
begin
return(value + addValue);
end;
/

CREATE OR REPLACE function mm_divide (value in number, divideValue in number)
return number
as
begin
return(value / divideValue);
end;
/

COMMIT;

-----------------------------------------------------------------
-- SQL functions for intervals mapping (discretization) by NIT --
-----------------------------------------------------------------

create or replace function time_interv_to_cat (
   time date, 
   time_format in varchar2,
   disc_table in varchar2,
   default_val in varchar2
)
return varchar2 is
  cur number;
  rows_processed number;
  ret_val varchar2(100); -- I think it's enough!
  the_query varchar2(500);
  time_string varchar2(200);
begin

  cur := dbms_sql.open_cursor;
  the_query := 'select value from ' || disc_table || ' where ' || 
     '(int_start < :time_v and int_end > :time_v and left_b = ''E'' and right_b = ''E'') or ' ||
     '(int_start <= :time_v and int_end > :time_v and left_b = ''I'' and right_b = ''E'') or ' ||
     '(int_start < :time_v and int_end >= :time_v and left_b = ''E'' and right_b = ''I'') or ' ||
     '(int_start <= :time_v and int_end >= :time_v and left_b = ''I'' and right_b = ''I'')';

  dbms_sql.parse(cur, the_query, dbms_sql.native);
  dbms_sql.bind_variable(cur, ':time_v', time);
  dbms_sql.define_column(cur,  1, ret_val, 30);
  rows_processed := dbms_sql.execute(cur);

  loop 
   rows_processed := dbms_sql.fetch_rows(cur);
   exit when rows_processed = 0;
   dbms_sql.column_value(cur, 1, ret_val);   -- last interval that time falls in is returned
  end loop;
  
  if ret_val is null then
     ret_val := default_val;
  end if;

  dbms_sql.close_cursor(cur);

  return ret_val;
 
end;
/


create or replace function num_interv_to_cat (
   num in number, 		-- numerical value
   disc_table in varchar2,	-- discretization table
   default_val in varchar2	-- default value
)
return varchar2 is
  cur number;
  rows_processed number;
  ret_val varchar2(100); -- I think it's enough!
begin

  cur := dbms_sql.open_cursor;
  dbms_sql.parse(cur, 
     'select value from ' || disc_table || ' where ' ||
     '(int_start < :num_v and int_end > :num_v and left_b = ''E'' and right_b = ''E'') or ' ||
      '(int_start <= :num_v and int_end > :num_v and left_b = ''I'' and right_b = ''E'') or ' ||
       '(int_start < :num_v and int_end >= :num_v and left_b = ''E'' and right_b = ''I'') or ' ||
       '(int_start <= :num_v and int_end >= :num_v and left_b = ''I'' and right_b = ''I'')',
     dbms_sql.native);
  dbms_sql.bind_variable(cur, ':num_v', num);
  dbms_sql.define_column(cur, 1, ret_val, 30);
  rows_processed := dbms_sql.execute(cur);

  loop 
   rows_processed := dbms_sql.fetch_rows(cur);
   exit when rows_processed = 0;
   dbms_sql.column_value(cur, 1, ret_val);   -- last interval that num falls in is returned
  end loop;
  
  if ret_val is null then
     ret_val := default_val;
  end if;

  dbms_sql.close_cursor(cur);
  return ret_val;
 
end;
/

----------------------------
COMMIT;

exit
