--
-- ### Gate A
-- 
--

create table t_int ( a int );
create table t_string ( a string(30));
create table t_datetime ( a datetime);
create table t_long ( a long);
create table t_bool ( a bool);
create table t_bigint ( a bigint(10));
create table t_float ( a float);
create table t_double ( a double);
create table t_decimal ( a decimal(2));
create table t_fixed ( a fixed(2));
create table t_smallint ( a smallint);
create table t_tinyint ( a tinyint);
create table t_null ( a int);

create table t_fk1 ( primary a int not null, b int );
create table t_fk2 ( primary c int not null, a int );
alter table t_fk2 add foreign key fk1 ( a ) references t_fk1 ( a );

create table t_ta ( a int, b string(30));
create table t_sfu ( a int, b string(30));
create table t_upd ( a int, b string(30));
create table t_ins ( a int, b string(30));

create table t_ibs1 ( a int, b string(30));
create table t_ibs2 ( a int, b string(30));

create table t_strfunc ( a int, b string(30), c datetime, d float, e string(10));

create table t_nv1 ( primary a int not null, b string(30));
create table t_nv2 ( primary c int not null, d string(30));
create table t_nv3 ( primary e int not null, f string(30));
create table t_nv4 ( primary g int not null, h string(30));
create index t4_idx on t_nv4(h);

create view v_nv1 as select tx.a as a, ty.d as d from t_nv1 tx, t_nv2 ty where tx.a = ty.c;
create view v_nv2 as select tx.a as a, ty.e as e from v_nv1 tx, t_nv3 ty where tx.a = ty.e;

create table t_oj1 ( primary a int not null, b string(30));
create table t_oj2 ( primary c int not null, d string(30));

create view v_oj1 as select t_oj1.a as A, t_oj2.d as B from t_oj1 inner join t_oj2 on t_oj1.a = t_oj2.c;

create table t_cwc ( a int, b string(30));

create table t_supplier ( primary snr int not null, sname string(30), prio int);
create table t_material ( primary mnr int not null, mname string(30), snr int);
create table t_orders (primary onr int not null, mname string(30), orderdate datetime, cnr int);

@
create procedure checkType ( t in string(30) ) return string(10) 
begin 

   if  :t = 'int'
   then
	var i int;
	delete from t_int;
   	insert into t_int values ( 1 );
        
	cursor intCur as select a from t_int;
	if fetch intCur into ( :i  ) = true
   	then
		if :i = 1
		then
			return 'ok';
		else
			return 'error';
		end;
	else
		return 'error';
	end;
   elsif  :t = 'string'
   then
	var s string(30);
	delete from t_string;
   	insert into t_string values ( 'XXX' );
        
	cursor strCur as select a from t_string;
	if fetch strCur into ( :s  ) = true
   	then
		if :s = 'XXX'
		then
			return 'ok';
		else
			return 'error';
		end;
	else
		return 'error';
	end;
   elsif  :t = 'datetime'
   then
	var d datetime;
	var s string(30);

	delete from t_datetime;
	insert into t_datetime values ( date('%d.%m.%Y %H:%M:%S','01.01.2006 1:11:00'));

	cursor dtCur as select date2str(a, '%d.%m.%Y') from t_datetime;
	if fetch dtCur into ( :s  ) = true
   	then
		if :s = '01.01.2006'
		then
			return 'ok';
		else
			return 'error';
		end;
	else
		return 'error';
	end;

   elsif  :t = 'long'
   then
	var l long;

	delete from t_long;
	insert into t_long values ( (long)11111111111 );

	cursor dtCur as select a from t_long;
	if fetch dtCur into ( :l  ) = true
   	then
		if :l = (long)11111111111
		then  
		      	return 'ok';
		else
			return 'error';
		end;	       
	else
		return 'error';	
	end;	
   elsif  :t = 'bool'
   then
	var b bool;

	delete from t_bool;
	insert into t_bool values ( true );

	cursor boolCur as select a from t_bool;
	if fetch boolCur into ( :b  ) = true
   	then
		if :b = true
		then  
		      	return 'ok';
		else
			return 'error';
		end;	       
	else
		return 'error';	
	end;	
   elsif  :t = 'bigint'
   then
	var b bigint(10);

	delete from t_bigint;
	insert into t_bigint values ( (bigint)12345678 );

	cursor biCur as select a from t_bigint;
	if fetch biCur into ( :b  ) = true
   	then
		if :b = (bigint)12345678
		then  
		      	return 'ok';
		else
			return 'error';
		end;	       
	else
		return 'error';	
	end;
   elsif  :t = 'float'
   then
	var f float;

	delete from t_float;
	insert into t_float values ( 12.34 );

	cursor floatCur as select a from t_float;
	if fetch floatCur into ( :f  ) = true
   	then
		if :f = 12.34
		then  
		      	return 'ok';
		else
			return 'error';
		end;	       
	else
		return 'error';	
	end;
   elsif  :t = 'double'
   then
	var d double;

	delete from t_double;
	insert into t_double values ( (double)12.34 );

	cursor doubleCur as select a from t_double;
	if fetch doubleCur into ( :d  ) = true
   	then
		if :d = (double)12.34
		then  
		      	return 'ok';
		else
			return 'error';
		end;	       
	else
		return 'error';	
	end;
   elsif  :t = 'decimal'
   then
	var d decimal(2);

	delete from t_decimal;
	insert into t_decimal values ( (decimal)12.34 );

	cursor decimalCur as select a from t_decimal;
	if fetch decimalCur into ( :d  ) = true
   	then
		if :d = (decimal)12.34
		then  
		      	return 'ok';
		else
			return 'error';
		end;	       
	else
		return 'error';	
	end;
   elsif :t = 'fixed'
   then
	var f fixed(2);

	delete from t_fixed;
	insert into t_fixed values ( (fixed)12.34 );

	cursor fixedCur as select a from t_fixed;
	if fetch fixedCur into ( :f  ) = true
   	then
		if :f = (fixed)12.34
		then  
		      	return 'ok';
		else
			return 'error';
		end;	       
	else
		return 'error';	
	end;
   elsif :t = 'smallint'
   then
	var s smallint;

	delete from t_smallint;
	insert into t_smallint values ( (smallint)3 );

	cursor siCur as select a from t_smallint;
	if fetch siCur into ( :s  ) = true
   	then
		if :s = (smallint)3
		then  
		      	return 'ok';
		else
			return 'error';
		end;	       
	else
		return 'error';	
	end;

   elsif :t = 'tinyint'
   then
	var t tinyint;

	delete from t_tinyint;
	insert into t_tinyint values ( (tinyint)123 );

	cursor tiCur as select a from t_tinyint;
	if fetch tiCur into ( :t  ) = true
   	then
		if :t = (tinyint)123
		then  
		      	return 'ok';
		else
			return 'error';
		end;	       
	else
		return 'error';	
	end;
   elsif :t = 'null'
   then
	var n int;

	insert into t_null values ( null );

	cursor nullCur as select a from t_null;
	if fetch nullCur into ( :n  ) = true
   	then
		if :n = null
		then  
		      	return 'ok';
		else
			return 'error';
		end;	       
	else
		return 'error';	
	end;

   else
	return 'error';	
   end;
   
end;
@

@
create procedure checkNull () return string(10) 
begin 

     var i int;
     delete from t_int;
     insert into t_int values ( null );
     insert into t_int values ( 1 );

     cursor nullCur as select a from t_int where a is null;
     if fetch nullCur into ( :i  ) = true
     then

	if :i != null
	then
		return 'error';
	end;
	if :i is not null
	then
		return 'error';
	end;
     else
	return 'error';
     end;

     cursor intCur as select a from t_int where a is not null;
     if fetch intCur into ( :i  ) = true
     then
	if :i != 1
	then
		return 'error';
	end;
     else
	return 'error';
     end;
     return 'ok';	
end;
@

@
create procedure checkForeignKey() return string(20) 
begin 
      
     insert into t_fk1 values ( 1, 2 );
     insert into t_fk1 values ( 2, 1 );

     begin
	insert into t_fk2 values ( 2, 1 );	
     exception when any
     then
	return 'error';
     end;

     begin
	insert into t_fk2 values ( 1, 111 );
	return 'error';
     exception when any
     then
	noop;
     end;

     begin
	delete from t_fk1 where a = 1;
	return 'error';
     exception when any
     then
	noop;
     end;

     begin
	delete from t_fk1 where a = 2;
     exception when any
     then
	return 'error';
     end;

     return 'ok';
end;
@

@
create procedure checkTransaction() return string(20) 
begin 
      delete from t_ta;

      start transaction;
      insert into t_ta values ( 1, 'alpha' );
      commit;

      start transaction;
      insert into t_ta values ( 2, 'beta' );
      rollback;

      var i int;
      cursor taCur as select a from t_ta where b = 'alpha';
      if fetch taCur into ( :i  ) = true
      then
	if :i != 1
	then
		return 'error';
	end;
      else
	return 'error';
      end;

      cursor rbCur as select a from t_ta where b = 'beta';
      if fetch taCur into ( :i  ) = true
      then
	return 'error';
      end;

      return 'ok';
      
end;
@

@
create procedure checkSelectForUpdate () return string(20)
begin 

      delete from t_sfu;
      insert into t_sfu values ( 1,  'X' );

      begin
        var val int;
        update t_sfu set a = a + 1 where b = 'X' return :val = a;
	if :val = 2
	then
		return 'ok';
	else
		return 'error';
	end;
     exception when any
     then
	return 'error';
     end;
end;
@

@
create procedure checkDistinct() return string(20)
begin 

      delete from t_int;
      insert into t_int values ( 1 );
      insert into t_int values ( 1 );
      insert into t_int values ( 1 );
      insert into t_int values ( 2 );
      insert into t_int values ( 2 );
      insert into t_int values ( 3 );

      begin
	var i int;
	var c int;

	:c = 0;
	cursor disCur as select distinct a from t_int;
        while fetch disCur into ( :i  ) = true
        begin 
	      :c = :c + 1;
	end;



	if :c = 3
	then
		return 'ok';
	else
		return 'error' | :c;
	end;
     exception when any
     then
	return 'error';
     end;
end;
@


@
create procedure checkInsert ( numIns in int )  return string(20)
begin 

      begin

         var actIns int;

	 :actIns = 0;

	 while :actIns < :numIns
	 begin
		insert into t_ins values ( :actIns , 'XXX');       
		:actIns = :actIns + 1;
	 end;

	 return 'ok';

     exception when any
     then
	return 'error';
     end;

end;
@

@
create procedure checkUpdate ( id in string(30), numUpd in int ) return string(20)
begin 

      
      insert into  t_upd values ( 1 , 'Alpha');
      insert into  t_upd values ( 1 , 'Beta');
      insert into  t_upd values ( 1 , 'Gamma');
      insert into  t_upd values ( 1 , 'Delta');

      begin

            var actUpd int;

      	    :actUpd = 0;

	    while :actUpd < :numUpd
	    begin
	    	update t_upd set  a = a + 1 where b = :id;
		:actUpd = :actUpd + 1;
	    end;
	    return 'ok';

     exception when any
     then
	return 'error';
     end;
end;
@

@
create procedure checkInsertBySelect () return string(20)
begin 

      delete from t_ibs1;
      delete from t_ibs2;

      insert into t_ibs1 values ( 1,  'X' );
      insert into t_ibs1 values ( 2,  'X' );
      insert into t_ibs1 values ( 3,  'X' );
      insert into t_ibs1 values ( 4,  'X' );
      insert into t_ibs1 values ( 5,  'X' );

      begin

	insert into t_ibs2 select a, b from t_ibs1;
	return 'ok';
	
      exception when any
      then
	return 'error';
     end;
end;
@

@
create procedure checkStringFunctions() return string(20)
begin

	insert into  t_strfunc values ( 1 , 'abXXXbb', date('%d.%m.%Y %H:%M:%S','01.01.2006 2:23:00'), 3.44, '42');

	var s string(10);
	var i int;
	var l long;

	cursor trimCur as select trim ( b, 'abc' ) from t_strfunc;
        if fetch trimCur into ( :s  ) = true
        then
		if :s != 'XXX'
		then
			return 'error';
		end;
	else
		return 'error';
	end;

	close trimCur;

	cursor truncCur as select trunc ( d ) from t_strfunc;
        if fetch truncCur into ( :i  ) = true
        then
		if :i != 3
		then
			return 'error';
		end;
	else
		return 'error';
	end;

	close truncCur;

	cursor d2sCur as select date2str(c, '%Y%m%d') from t_strfunc;
        if fetch d2sCur into ( :s ) = true
        then
		if :s != '20060101'
		then
			return 'error';
		end;
	else
		return 'error';
	end;

	close d2sCur;

	cursor lowCur as select lower(b) from t_strfunc;
        if fetch lowCur into ( :s ) = true
        then
		if :s != 'abxxxbb'
		then
			return 'error';
		end;
	else
		return 'error';
	end;

	close lowCur;

	cursor upCur as select upper(b) from t_strfunc;
        if fetch upCur into ( :s ) = true
        then
		if :s != 'ABXXXBB'
		then
			return 'error';
		end;
	else
		return 'error';
	end;

	close upCur;


	cursor leftCur as select left(b, 3) from t_strfunc;
        if fetch leftCur into ( :s ) = true
        then
		if :s != 'abX'
		then
			return 'error';
		end;
	else
		return 'error';
	end;

	close leftCur;

	cursor rightCur as select right(b, 3) from t_strfunc;
        if fetch rightCur into ( :s ) = true
        then
		if :s != 'Xbb'
		then
			return 'error';
		end;
	else
		return 'error';
	end;

	close rightCur;	

	cursor gp1Cur as select getpos(b, 'b') from t_strfunc;
        if fetch gp1Cur into ( :i ) = true
        then
		if :i != 2 
		then
			return 'error';
		end;
	else
		return 'error';
	end;

	close gp1Cur;

	cursor gp2Cur as select getpos(b, 'b', 3, 1) from t_strfunc;
        if fetch gp2Cur into ( :i ) = true
        then
		if :i != 6 
		then
			return 'error';
		end;
	else
		return 'error';
	end;

	close gp2Cur;

	cursor gp3Cur as select getpos(b, 'b', 3, 2) from t_strfunc;
        if fetch gp3Cur into ( :i ) = true
        then
		if :i != 7 
		then
			return 'error';
		end;
	else
		return 'error';
	end;

	close gp3Cur;


	cursor sub1Cur as select substr(b, 2) from t_strfunc;
        if fetch sub1Cur into ( :s ) = true
        then
		if :s != 'bXXXbb'
		then
			return 'error';
	 	end;
	else
		return 'error';
	end;

	close sub1Cur;

	cursor sub2Cur as select substr(b, 2, 1) from t_strfunc;
        if fetch sub2Cur into ( :s ) = true
        then
		if :s != 'b'
		then
			return 'error';
	 	end;
	else
		return 'error';
	end;

	close sub2Cur;

	cursor repCur as select replace(b, 'XXX', 'YYY') from t_strfunc;
        if fetch repCur into ( :s ) = true
        then
		if :s != 'abYYYbb'
		then
			return 'error';
	 	end;
	else
		return 'error';
	end;

	close repCur;

	cursor lenCur as select length(b) from t_strfunc;
        if fetch lenCur into ( :i ) = true
        then
		if :i != 7
		then
			return 'error';
	 	end;
	else
		return 'error';
	end;

	close lenCur;

	cursor s2iCur as select str2int(e) from t_strfunc;
        if fetch s2iCur into ( :i ) = true
        then
		if :i != 42
		then
			return 'error';
	 	end;
	else
		return 'error';
	end;

	close s2iCur;

	cursor s2lCur as select str2long(e) from t_strfunc;
        if fetch s2lCur into ( :l ) = true
        then
		if :l != (long)42
		then
			return 'error';
	 	end;
	else
		return 'error';
	end;

	close s2lCur;


	cursor rsCur as select randstr(10) from t_strfunc;
        if fetch rsCur into ( :s ) = true
        then
		noop;
	else
		return 'error';
	end;

	close rsCur;

	cursor riCur as select randint(1000) from t_strfunc;
        if fetch riCur into ( :i ) = true
        then
		noop;
	else
		return 'error';
	end;

	close riCur;

	return 'ok';

end;
@

@
create procedure checkNestedView() return string(20)
begin

	var i int;

	delete from t_nv1;
	delete from t_nv2;
	delete from t_nv3;
	delete from t_nv4;

	insert into t_nv1 values ( 1, 'xxx');
	insert into t_nv1 values ( 2, 'yyy');
	insert into t_nv1 values ( 3, 'zzz');

	insert into t_nv2 values ( 1, 'xxx');
	insert into t_nv2 values ( 2, 'III');
	insert into t_nv2 values ( 3, 'OOOO');

	insert into t_nv3 values ( 1, 'Iwan');
	insert into t_nv3 values ( 2, 'xxx');
	insert into t_nv3 values ( 3, 'hurga');


	insert into t_nv4 values ( 1, 'alpha');
	insert into t_nv4 values ( 2, 'beta');
	insert into t_nv4 values ( 3, 'gamma');
	insert into t_nv4 values ( 4, 'delta');


	cursor nvCur as select v_nv2.a from v_nv2 v2, t_nv4 where v2.a = t_nv4.g and t_nv4.h = 'beta';
        if fetch nvCur into ( :i ) = true
        then
		if :i != 2
		then
			return 'error';
	 	end;
	else
		return 'error';
	end;

	close nvCur;

	return 'ok';

end;
@


@
create procedure checkOuterJoin() return string(20)
begin

	var i int;


	insert into t_oj1 values ( 1, 'xxx');
	insert into t_oj1 values ( 2, 'RRR');
	insert into t_oj1 values ( 4, 'yyy');

	insert into t_oj2 values ( 1, 'xxx');
	insert into t_oj2 values ( 2, 'vvv');
	insert into t_oj2 values ( 3, 'zzz');


	cursor oj1Cur as select a from t_oj1 inner join t_oj2 on t_oj1.a = t_oj2.c;
        if fetch oj1Cur into ( :i ) = true
        then
		if :i != 1
		then
			return 'error';
	 	end;
	else
		return 'error';
	end;

	close oj1Cur;

	cursor oj2Cur as select a from t_oj1 left outer join t_oj2 on t_oj1.a = t_oj2.c;
        if fetch oj2Cur into ( :i ) = true
        then
		if :i != 1
		then
			return 'error';
	 	end;
	else
		return 'error';
	end;

	close oj2Cur;


	cursor oj3Cur as select a from t_oj1 tx right outer join t_oj2 ty on tx.a = ty.c;
        if fetch oj3Cur into ( :i ) = true
        then
		if :i != 1
		then
			return 'error3';
	 	end;
	else
		return 'error';
	end;

	close oj3Cur;

	cursor oj4Cur as select A from v_oj1 where B = 'xxx';
        if fetch oj4Cur into ( :i ) = true
        then
		if :i != 1
		then
			return 'error';
	 	end;
	else
		return 'error';
	end;
	
	close oj4Cur;

	return 'ok';
end;
@

@
create procedure checkCaseWhen() return string(20)
begin

	var s string(10);

	insert into t_cwc values ( 1, 'xxx');
	insert into t_cwc values ( 1, 'ggg');
	insert into t_cwc values ( 4, 'yyy');
	insert into t_cwc values ( 3, 'zzz');
	
	cursor cwcCur as select case when tx.a = 1 then 'alpha' else 'beta' end from t_cwc tx;
        if fetch cwcCur into ( :s ) = true
        then
		if :s != 'alpha'
		then
			return 'error';
	 	end;
	else
		return 'error';
	end;
	
	close cwcCur;

	return 'ok';
end;
@

@
create procedure checkSubSelect() return string(20)
begin

	delete from t_supplier;
	delete from t_material;
	delete from t_orders;


	-- insert base data
	insert into t_supplier values ( 1, 'Time&Material', 3);
	insert into t_supplier values ( 2, 'Cego AG', 5);
	insert into t_supplier values ( 3, 'Moon Factory', 9);
	insert into t_supplier values ( 4, 'McDagobert', 3);
	insert into t_supplier values ( 11, 'HAL', 2);
	insert into t_supplier values ( 12, 'Abraxas', 1);
	insert into t_supplier values ( 13, 'ThunderStorm', 9);

	insert into t_material values ( 1, 'screw', 11);
	insert into t_material values ( 2, 'nail', 11);
	insert into t_material values ( 3, 'hammer', 1);
	insert into t_material values ( 4, 'saw', 2);
	insert into t_material values ( 5, 'nail', 2);
	insert into t_material values ( 6, 'knife', 1);
	insert into t_material values ( 7, 'screw', 2);
	insert into t_material values ( 8, 'saw', 11);
	insert into t_material values ( 9, 'hammer', 11);

	insert into t_orders values ( 4711, 'screw', sysdate, 23);
	insert into t_orders values ( 4712, 'nail', sysdate, 23);
	insert into t_orders values ( 4713, 'saw', sysdate, 23);
	insert into t_orders values ( 4714, 'hammer', sysdate, 23 );

	var s string(10);

	cursor subCur as select sname from t_supplier sup where not exists
	         ( select * from t_orders ord where not exists 
		        ( select * from t_material mat where mat.snr = sup.snr and mat.mname = ord.mname ));


        if fetch subCur into ( :s ) = true
        then
		if :s != 'HAL'
		then
			return 'error';
	 	end;
	else
		return 'error';
	end;

	close subCur;	

	return 'ok';
end;
@

@
create procedure checkGrouping() return string(20)
begin

	delete from t_material;

	insert into t_material values ( 1, 'screw', 11);
	insert into t_material values ( 2, 'nail', 11);
	insert into t_material values ( 3, 'hammer', 1);
	insert into t_material values ( 4, 'saw', 2);
	insert into t_material values ( 5, 'nail', 2);
	insert into t_material values ( 6, 'knife', 1);
	insert into t_material values ( 7, 'screw', 2);
	insert into t_material values ( 8, 'saw', 11);
	insert into t_material values ( 9, 'hammer', 11);
	insert into t_material values ( 10, 'saw', 1);

	var s string(10);
	var i int;

	cursor groupCur as select mname, count(*) from t_material group by mname;

        while fetch groupCur into ( :s, :i ) = true
        begin
		if :s = 'screw' and :i != 2
		then
			return 'error';
	 	end;
		if :s = 'nail' and :i != 2
		then
			return 'error';
	 	end;
		if :s = 'hammer' and :i != 2
		then
			return 'error';
	 	end;
		if :s = 'saw' and :i != 3
		then
			return 'error';
	 	end;		
	end;

	close groupCur;	

	cursor havCur as select mname, count(*) from t_material group by mname having count(*) = 3;

        if fetch havCur into ( :s, :i ) = true
        then
		if :s != 'saw'
		then
			return 'error';
	 	end;
	end;

	close havCur;	

	return 'ok';
end;
@

@
create procedure checkAgg() return string(20)
begin

	delete from t_material;

	insert into t_material values ( 1, 'screw', 11);
	insert into t_material values ( 2, 'nail', 11);
	insert into t_material values ( 3, 'hammer', 1);
	insert into t_material values ( 4, 'saw', 2);
	insert into t_material values ( 5, 'nail', 2);
	insert into t_material values ( 6, 'knife', 1);
	insert into t_material values ( 7, 'screw', 2);
	insert into t_material values ( 8, 'saw', 11);
	insert into t_material values ( 9, 'hammer', 11);
	insert into t_material values ( 10, 'saw', 1);

	var i int;

	cursor aggCur as select count(*) from t_material;

        if fetch aggCur into ( :i ) = true
        then 
		if :i != 10
		then
			return 'error';
		end;
	end;

	close aggCur;

	return 'ok';
end;
@

@
create procedure checkNestedAgg() return string(20)
begin


	delete from t_supplier;
	delete from t_material;

	-- insert base data
	insert into t_supplier values ( 1, 'Time&Material', 3);
	insert into t_supplier values ( 2, 'Cego AG', 5);
	insert into t_supplier values ( 3, 'Moon Factory', 9);
	insert into t_supplier values ( 4, 'McDagobert', 3);
	insert into t_supplier values ( 11, 'HAL', 2);
	insert into t_supplier values ( 12, 'Abraxas', 1);
	insert into t_supplier values ( 13, 'ThunderStorm', 9);

	insert into t_material values ( 3, 'hammer', 1);
	insert into t_material values ( 6, 'knife', 1);

	insert into t_material values ( 4, 'saw', 2);
	insert into t_material values ( 5, 'nail', 2);
	insert into t_material values ( 7, 'screw', 2);

	insert into t_material values ( 1, 'screw', 11);
	insert into t_material values ( 2, 'nail', 11);
	insert into t_material values ( 8, 'saw', 11);
	insert into t_material values ( 9, 'hammer', 11);

	var snr int;
	var mnr int;

	cursor aggCur as select snr, ( select max(mnr) from t_material m where m.snr = s.snr ) from t_supplier s;

        while fetch aggCur into ( :snr, :mnr ) = true
        begin 
		if :snr = 1 and :mnr != 6 
		then
			return 'error';
		end;

		if :snr = 2 and :mnr != 7 
		then
			return 'error';
		end;

		if :snr = 11 and :mnr != 9 
		then
			return 'error';
		end;

	end;

	close aggCur;

	return 'ok';
end;
@


:r = call checkType('int');
insert into checklog values ('A', 'Integer type check', :r);
:r = call checkType('string');
insert into checklog values ('A', 'String type check', :r);
:r = call checkType('datetime');
insert into checklog values ('A', 'Datetime type check', :r);
:r = call checkType('long');
insert into checklog values ('A', 'Long type check', :r);
:r = call checkType('bool');
insert into checklog values ('A', 'Bool type check', :r);
:r = call checkType('bigint');
insert into checklog values ('A', 'Bigint type check', :r);
:r = call checkType('float');
insert into checklog values ('A', 'Float type check', :r);
:r = call checkType('double');
insert into checklog values ('A', 'Double type check', :r);
:r = call checkType('decimal');
insert into checklog values ('A', 'Decimal type check', :r);
:r = call checkType('fixed');
insert into checklog values ('A', 'Fixed type check', :r);
:r = call checkType('smallint');
insert into checklog values ('A', 'Small int type check', :r);
:r = call checkType('tinyint');
insert into checklog values ('A', 'Tiny int type check', :r);
:r = call checkType('null');
insert into checklog values ('A', 'Null type check', :r);
:r = call checkNull();
insert into checklog values ('A', 'Null check', :r);
:r = call checkForeignKey();
insert into checklog values ('A', 'Foreign key check', :r);
:r = call checkTransaction();
insert into checklog values ('A', 'Transaction check', :r);
:r = call checkSelectForUpdate();
insert into checklog values ('A', 'Select for update check', :r);
:r = call checkDistinct();
insert into checklog values ('A', 'Distinct check', :r);
:r = call checkInsert(100);
insert into checklog values ('A', 'Insert check', :r);
:r = call checkUpdate('beta', 100);
insert into checklog values ('A', 'Update check', :r);
:r = call checkInsertBySelect();
insert into checklog values ('A', 'Insert by select check', :r);
:r = call checkStringFunctions();
insert into checklog values ('A', 'String functions check', :r);
:r = call checkNestedView();
insert into checklog values ('A', 'Nested view check', :r);
:r = call checkOuterJoin();
insert into checklog values ('A', 'Outer join check', :r);
:r = call checkCaseWhen();
insert into checklog values ('A', 'Case when check', :r);
:r = call checkSubSelect();
insert into checklog values ('A', 'Subselect check', :r);
:r = call checkGrouping();
insert into checklog values ('A', 'Grouping check', :r);
:r = call checkAgg();
insert into checklog values ('A', 'Aggregation check', :r);

:r = call checkNestedAgg();
insert into checklog values ('A', 'Nested Aggregation check', :r);


drop procedure checkType;
drop procedure checkNull;
drop procedure checkForeignKey;
drop procedure checkTransaction;
drop procedure checkSelectForUpdate;
drop procedure checkDistinct;
drop procedure checkInsert;
drop procedure checkUpdate;
drop procedure checkInsertBySelect;
drop procedure checkStringFunctions;
drop procedure checkNestedView;
drop procedure checkOuterJoin;
drop procedure checkCaseWhen;
drop procedure checkSubSelect;
drop procedure checkGrouping;
drop procedure checkAgg;
drop procedure checkNestedAgg;


drop view v_nv1;
drop view v_nv2;
drop view v_oj1;


drop table t_int;
drop table t_string;
drop table t_datetime;
drop table t_long;
drop table t_bool;
drop table t_bigint;
drop table t_float;
drop table t_double;
drop table t_decimal;
drop table t_fixed;
drop table t_smallint;
drop table t_tinyint;
drop table t_null;
drop table t_fk1;
drop table t_fk2;
drop table t_ta;
drop table t_sfu;
drop table t_upd;
drop table t_ins;
drop table t_ibs1;
drop table t_ibs2;
drop table t_strfunc;
drop table t_nv1;
drop table t_nv2;
drop table t_nv3;
drop table t_nv4;
drop table t_oj1;
drop table t_oj2;
drop table t_cwc;
drop table t_supplier;
drop table t_orders;
drop table t_material;