SQL MINUS

[Guest_imported]

I am trying to build a report from a SQL statement. Here is the Statement:
SELECT DONA_IDNO, I_LAST_NAME, I_FIRST_NAME
FROM DONATIONS, INDV
WHERE DONA_IDNO = I_NUMBER
AND DONA_CODE =ANY('P25','P100','P500','P1K')
AND DONA_DATE >= '01-SEP-00'
MINUS
SELECT DONA_IDNO, I_LAST_NAME, I_FIRST_NAME
FROM DONATIONS, INDV
WHERE DONA_IDNO = I_NUMBER
AND DONA_CODE =ANY('P25','P100','P500','P1K')
AND DONA_DATE < '01-SEP-00'
/
The problem is with the key-word MINUS (Not Including). I want to get the first select statement minus the records included in the second select statement. How can this be done within crystal?

 

[IdoMillet]

Are you using Crystal SQL Designer to
build this query?

What is the DBMS?

Cheers,
- Ido

ixm7@psu.edu

 

[balves]

You don't get what you don't ask for. There's no need for the MINUS Select statement. MINUS is not a SQL reserved word that I am aware of.

Secondly, standard SQL is IN ('A', 'B', 'C') .... not ANY

 

[IdoMillet]

balves,

Actually it depends on the DBMS.
ORACLE handles both MINUS and ANY operators.

Cheers,
- Ido ixm7@psu.edu

 

[balves]

You're right. MINUS works in Oracle. It's not part of ANSI SQL, so I would try to avoid it. The ODBC driver may do weird things with it.

For example, in SQL Server:

SELECT * FROM TABLE A
MINUS
SELECT * FROM TABLE A WHERE ...

In this case, MINUS is not a reserved word, so it becomes a table alias for TABLE A.