--
-- ### Gate C
-- 
--


create table t1 ( primary a int not null, b string(30) );
create table t2 ( primary c int not null, d string(30));
create view v1 as select a from t1;

insert into t1 values ( 1, 'hugo');
insert into t1 values ( 2, 'hugo');
insert into t1 values ( 3, 'walter');
insert into t1 values ( 4, 'bert');
insert into t1 values ( 5, 'bert');
insert into t1 values ( 6, 'bert');
insert into t1 values ( 7, 'bert');
insert into t1 values ( 8, 'bert');
insert into t1 values ( 9, 'bert');
insert into t1 values ( 10, 'bert');


insert into t2 values ( 1, 'hugo');
insert into t2 values ( 2, 'hugo');
insert into t2 values ( 3, 'walter');
insert into t2 values ( 4, 'bert');
insert into t2 values ( 5, 'bert');
insert into t2 values ( 16, 'bert');
insert into t2 values ( 17, 'bert');
insert into t2 values ( 18, 'bert');
insert into t2 values ( 19, 'bert');
insert into t2 values ( 20, 'bert');

@
create procedure checkSelect001(msg out string(20)) return string(10)
begin
	var res string(10) = 'error';
	var i int;
	
	cursor xCur as select count(*) from t1 inner join t2 on t1.a = t2.c;
        if fetch xCur into ( :i ) = true
        then 
	     if :i = 5
	     then
		:res = 'ok';
	     end;
	end;
	close xCur;

	:msg = 'Inner join check';

	return :res;
end;
@

@
create procedure checkSelect002(msg out string(20)) return string(10)
begin
	var res string(10) = 'error';
	var i int;
	
	cursor xCur as select count(*) from t1 left outer join t2 on t1.a = t2.c;
        if fetch xCur into ( :i ) = true
        then 
	     if :i = 10
	     then
		:res = 'ok';
	     end;
	end;
	close xCur;

	:msg = 'Left outer join check';

	return :res;
end;
@


@
create procedure checkSelect003(msg out string(20)) return string(10)
begin
	var res string(10) = 'error';
	var i int;

	cursor xCur as select count(*) from t1 left outer join t2 on t1.a = t2.c;

        if fetch xCur into ( :i ) = true
        then 
	      if :i = 10
	      then
	 	:res = 'ok';
	      end;
	end;
	close xCur;

	:msg = 'Right outer join check';

	return :res;
end;
@


@
create procedure checkSelect004(msg out string(20)) return string(10)
begin
	var res string(10) = 'error';
	var i int;

	cursor xCur as select count(*) from t1 inner join t2 on t1.a = t2.c where t1.b = 'bert' or t1.b = 'walter';

        if fetch xCur into ( :i ) = true
        then 
	      if :i = 3
	      then
	 	:res = 'ok';
	      end;
	end;
	close xCur;

	:msg = 'Inner join check with condition';

	return :res;
end;
@


@
create procedure checkSelect005(msg out string(20)) return string(10)
begin
	var res string(10) = 'error';
	var i int;

	cursor xCur as select count(*) from t1 left outer join t2 on t1.a = t2.c where a = 10;

        if fetch xCur into ( :i ) = true
        then 
	      if :i = 1
	      then
	 	:res = 'ok';
	      end;
	end;
	close xCur;

	:msg = 'Left join check with condition';

	return :res;
end;
@

@
create procedure checkSelect006(msg out string(20)) return string(10)
begin
	var res string(10) = 'error';
	var i int;

	cursor xCur as select count(*) from t1 right outer join t2 on t1.a = t2.c;

        if fetch xCur into ( :i ) = true
        then 
	      if :i = 10
	      then
	 	:res = 'ok';
	      end;
	end;
	close xCur;

	:msg = 'Right join check with condition';

	return :res;
end;
@

@
create procedure checkSelect007(msg out string(20)) return string(10)
begin
	var res string(10) = 'error';
	var i int;

	cursor xCur as select count(*) from t1 where a between 3 and 5;

        if fetch xCur into ( :i ) = true
        then 
	      if :i = 3
	      then
	 	:res = 'ok';
	      end;
	end;
	close xCur;

	:msg = 'Between query check';

	return :res;
end;
@

@
create procedure checkSelect008(msg out string(20)) return string(10)
begin
	var res string(10) = 'error';
	var i int;

	cursor xCur as select count(*) from v1 where a between 3 and 5;

        if fetch xCur into ( :i ) = true
        then 
	      if :i = 3
	      then
	 	:res = 'ok';
	      end;
	end;
	close xCur;

	:msg = 'Between query check on view';

	return :res;
end;
@


:r = call checkSelect001(:msg);
insert into checklog values ('C', :msg, :r);
:r = call checkSelect002(:msg);
insert into checklog values ('C', :msg, :r);
:r = call checkSelect003(:msg);
insert into checklog values ('C', :msg, :r);
:r = call checkSelect004(:msg);
insert into checklog values ('C', :msg, :r);
:r = call checkSelect005(:msg);
insert into checklog values ('C', :msg, :r);
:r = call checkSelect006(:msg);
insert into checklog values ('C', :msg, :r);
:r = call checkSelect007(:msg);
insert into checklog values ('C', :msg, :r);
:r = call checkSelect008(:msg);
insert into checklog values ('C', :msg, :r);

drop procedure checkSelect001;
drop procedure checkSelect002;
drop procedure checkSelect003;
drop procedure checkSelect004;
drop procedure checkSelect005;
drop procedure checkSelect006;
drop procedure checkSelect007;
drop procedure checkSelect008;

drop table t1;
drop table t2;
drop view v1;
