How to get the difference of sets?

[Former Member]

Hi all,

I would like to know how to get the difference set with ESP

For example, I have two windows like below:

CREATE INPUT WINDOW A SCHEMA (aa INTEGER) PRIMARY KEY(aa);

CREATE INPUT WINDOW B SCHEMA (bb INTEGER) PRIMARY KEY(bb);

And I want to get C (=A-B).

TIA,

JC

Message was edited by: Robert Waywell - Edited variable names for clarity

[Former Member]

You can do an outer join and filter out all the matching rows.

CREATE LOCAL WINDOW ABJoin PRIMARY KEY (Aaa)

AS SELECT A.aa Aaa, B.bb Bbb FROM A LEFT JOIN B ON A.aa = B.bb;

CREATE OUTPUT WINDOW C PRIMARY KEY DEDUCED

AS SELECT Aaa aa FROM ABJoin WHERE isnull(B.bb);

This effectively does what you need.