I am very new to this product and am having trouble understanding how to perform things that were very simple when dealing with direct SQL.
I am useing Adapter since this needs to be performed in Oracle, ACCESS and SQLServer databases.
I am such a serious jam right now and would appreciate some help (code examples please). If I can accomplish these simple tasks I'm not sure what to do.
I have one table named
ID, IDValue
1 3
1 4
1 5
2 4
I need to perform three types of operations, Intersect, Substract and Union.
INTERSECT:
INSERT INTO Table1 (ID, IDValue) 3, L1.ID FROM TABLE1 L1, TABLE1 L2 WHERE L1.ID = L2.ID AND L1.hitlistid = 1 AND L2.hitlistid = 2
The result on the above table would be two new entries with ID 3:
Table 1
ID, IDValue
1 3
1 4
1 5
2 4
2 5
3 4
3 5
SUBTRACT:
INSERT INTO Table1 (ID, IDValue) 3, L1.ID FROM TABLE1 L1, TABLE1 L2 WHERE L1.hitlistid = 1 AND NOT EXISTS (SELECT 1 FROM TABLE1 L2 WHERE L2.hitlistID =2 AND L2.ID=L1.ID)
The result on the above table would be one new entries with ID 3:
Table 1
ID, IDValue
1 3
1 4
1 5
2 4
2 5
3 3
UNION:
INSERT INTO Table1 (ID, IDValue) 3 SELECT 3 AS hitlistID, L1.ID FROM table1 L1 WHERE L1.hitlistID=1 UNION SELECT 3 hitlistID, L2.ID FROM Table1 L2 WHERE L2.hitlistID = 2
The result on the above table would be three new entries with ID 3:
Table 1
ID, IDValue
1 3
1 4
1 5
2 4
2 5
3 3
3 4
3 5
