minus in oracle

[oradba101]

In Oracle there is a command that is part of the select statement called minus. Simplified this is the way it works:

SELECT deptno
FROM department
MINUS
SELECT deptno
FROM department
WHERE deptno IN (SELECT city
FROM locations
WHERE city = 'Boston')

Is there an equivilent in Pervasive.SQL?

Thanks,

Bill Chadbourne
DBA
TCMHS

 

[mirtheil]

Not that I'm aware of. What does the MINUS command do?

Mirtheil
Certified Pervasive Developer
Certified Pervasive Technician
Custom VB and Btrieve development.

http://www.mirtheil.com

 

[oradba101]

Sorry, Mirtheil. I should have given a better example. There are actually three different commands: UNION, MINUS, and INTERSECT. UNION you already know about so here is an example of the other two.

Sometimes you need to combine information of a similar type from more than one table. A classic example of this is merging two or more mailing lists prior to a mailing campaign.

One list contains longtime employees and the other contains prospects. There are names that appear in both lists and you need only the ones that are not duplicates so:

SELECT * FROM longtime
MINUS
SELECT * FROM prospects

The names in the prospects table are subtracted from those in the longtime table.

SELECT * FROM longtime
INTERSECT
SELECT * FROM prospects

The result will be only the names that appear in both lists.