How can I subtract result of 3 queries from a 4. query

[jarimi]

Please help me in making a SQL script for DB2 that Subtract result of 3 queries from a 4. query

I would like to subtract results from query B,C,D from query result of A.

The end result is a list of valid scratch tapes for free slots in a TSM tape robot. At the moment i'm using external scripting from a client computer with ODBC. I want to have it all on the server and it would be nice to have it all in SQL script, can you help me...DB2 Newbe


Select statments:
A.:
SELECT DISTINCT VOLHISTORY.VOLUME_NAME FROM VOLHISTORY VOLHISTORY ORDER BY VOLHISTORY.VOLUME_NAME

B.:
SELECT DRMEDIA.VOLUME_NAME FROM DRMEDIA DRMEDIA

C.:
SELECT LIBVOLUMES.VOLUME_NAME FROM LIBVOLUMES LIBVOLUMES

D.:
SELECT VOLUMES.VOLUME_NAME FROM VOLUMES VOLUMES WHERE (VOLUMES.STGPOOL_NAME<>'DISK_BU')


Results from above select statments
a.:
DEC500
DEC501
DEC502
DEC503
DEC504
DEC505
DEC506
DEC507
DEC508
DEC509
DEC510
DEC511
DEC512
DEC513
DEC514
DEC515
DEC516
DEC517
DEC518
DEC519
DEC520
DEC521
DEC522
DEC523
DEC524
DEC525
DEC526
DEC527

b.:
DEC501
DEC502
DEC504
DEC505
DEC506
DEC508
DEC514
DEC511
DEC512

c.:
DEC500
DEC503
DEC507
DEC508
DEC509
DEC510
DEC513
DEC515
DEC522
DEC524

d.:
DEC500
DEC501
DEC502
DEC504
DEC505
DEC506
DEC507
DEC508
DEC509
DEC515
DEC522

B,C,D subtracted from A.:
DEC516
DEC517
DEC518
DEC519
DEC520
DEC521
DEC523
DEC525
DEC526
DEC527

 

[blom0344]

Don't know if one script with multiple except statements will work (have no time to test it), but the following should do the trick.

Create database view 'BCD' containing all data from b,c,d:

SELECT DRMEDIA.VOLUME_NAME AS VOLNAME FROM DRMEDIA
UNION
SELECT LIBVOLUMES.VOLUME_NAME AS VOLNAME FROM LIBVOLUMES
UNION
SELECT VOLUMES.VOLUME_NAME AS VOLNAME FROM VOLUMES WHERE (VOLUMES.STGPOOL_NAME<>'DISK_BU')

Now use the except statement to subtract contents of view from A:

SELECT DISTINCT VOLHISTORY.VOLUME_NAME FROM VOLHISTORY
EXCEPT
SELECT VOLNAME FROM BCD;

But I expect another forum-member to come up with one SQL to do the job (too little time left for me to think this through)

T. Blom
Information analyst
tbl@shimano-eu.com

 

[MarcLodge]

Hi,
You could do something like the following, which will work, although I'm not sure it is the best way to do it...

SELECT A.VOLUME_NAME
FROM VOLHISTORY A

WHERE A.VOLUME_NAME NOT IN
(SELECT B.VOLUME_NAME
FROM DRMEDIA B)

AND A.VOLUME_NAME NOT IN
(SELECT C.VOLUME_NAME
FROM LIBVOLUMES C)

AND A.VOLUME_NAME NOT IN
(SELECT D.VOLUME_NAME
FROM VOLUMES D)

 

[jarimi]

Thanks for your responses.

I found the following solution to my problem. I tried using JOIN and EXCEPT, but it looks like the DB I'm quering is a limited version of DB2 without support of these commands.

SELECT DISTINCT VOLUME_NAME FROM VOLHISTORY where volume_name not in (SELECT
VOLUME_NAME FROM LIBVOLUMES) and volume_name not in (SELECT VOLUME_NAME FROM
DRMEDIA) and volume_name not in (SELECT VOLUMES.VOLUME_NAME FROM VOLUMES
VOLUMES WHERE (VOLUMES.STGPOOL_NAME<>'DISK_BU')) ORDER BY
VOLHISTORY.VOLUME_NAME

 

[MarcLodge]

Looks dangerously like what I wrote! We are obviously singing from the same songsheet