/*  Define the source tables  */

drop table if exists memory\T1;
create table memory\T1 (T1_ID integer);
insert into memory\T1 values (1);
insert into memory\T1 values (2);
insert into memory\T1 values (3);

drop table if exists memory\T2;
create table memory\T2 (T2_ID integer, FK integer);
insert into memory\T2 values (1,1);
insert into memory\T2 values (2,2);
insert into memory\T2 values (3,3);

/*  Query expression:

    select T1.T1_ID, T2.T2_ID
    from memory\T1
    --join memory\T2 on
    --left outer join memory\T2 on
    right outer join memory\T2 on
      T2.FK = T1.T1_ID
      and T2.FK = 2;

*/

/*  Apply the search condition to the cross product of T1, T2  */

select T1.*, T2.*
into memory\TN
from memory\T1, memory\T2
where
  T2.FK = T1.T1_ID
  and T2.FK = 2;

/*  Additional steps for left outer join  */

-- 1: Process the collection of rows of T1 which have matching rows in TN

select *
into memory\P1
from memory\T1
where T1_ID in (select T1_ID from memory\TN);

-- 2: Process the collection of rows of T1 which don't exist in P1

select *
into memory\U1
from memory\T1
where T1_ID not in (select T1_ID from memory\P1);

-- 3: Extend U1 on the right with columns from T2

select
  *,
  cast(0 as integer),  -- T2_ID
  cast(0 as integer)   -- FK
into memory\XN1
from memory\U1;

/* Additional steps for right or full outer join  */

-- 1: Process the collection of rows of T2 which have matching rows in TN

select *
into memory\P2
from memory\T2
where T2_ID in (select T2_ID from memory\TN);

-- 2: Process the collection of rows of T2 which don't exist in P2

select *
into memory\U2
from memory\T2
where T2_ID not in (select T2_ID from memory\P2);

-- 3: Extend U2 on the left with columns from T1

select
  cast(0 as integer),  -- T1_ID
  *
into memory\XN2
from memory\U2;

/*  Inner join  */

-- Apply the select list

select T1_ID, T2_ID
from memory\TN;

/*  Left outer join  */

-- Process the joined table

select *
into memory\SN
from memory\TN
union all
select * from memory\XN1;

-- Apply the select list

select T1_ID, T2_ID
from memory\SN;

/*  Right outer join  */

-- Process the joined table

select *
into memory\SN
from memory\TN
union all
select * from memory\XN2;

-- Apply the select list

select T1_ID, T2_ID
from memory\SN;

/*  Full outer join  */

-- Process the joined table

select *
into memory\SN
from memory\TN
union all
select * from memory\XN1
union all
select * from memory\XN2;

-- Apply the select list

select T1_ID, T2_ID
from memory\SN;

---

 Comments

---

The problem was with the double-dash (--) comments. The TDBISAMQuery component was not reading them properly in terms of parsing the individual SQL statements from the script.

---

 Resolution

---

Fixed Problem on 6/11/2004 in version 4.09 build 1