ca.portal.admin

SQL question

Discussion created by ca.portal.admin on Oct 10, 2008
Hi everyone,

I have a SQL question from a developper that I can't answer. He wants to g=
et all B records from a set A-B, but only the B that are not an
active member of the set A-B. He tried with NOT A-B, and it doesn't
work. Is there a way to do this in SQL?

Thanks,
Laura Rochon
Ajilon
"
IDMS 3rd-party providers forum
IDMSVENDOR-L@LISTSERV.IUASSN.COM
SMTP
IDMSVENDOR-L@LISTSERV.IUASSN.COM
IDMSVENDOR-L@LISTSERV.IUASSN.COM
SMTP








Normal

Normal
Re: SQL question
"There is a way to do it, but it is very inefficient.

SELECT DUCONT_LAST_NAME
FROM DUSESCHM.DUCONT
WHERE DUCONT_LAST_NAME NOT IN
(SELECT DUCONT_LAST_NAME
FROM DUSESCHM.DUDEPT,
DUSESCHM.DUCONT
WHERE DUDEPT_NAME = 'DHS'
AND ""DUDEPT-DUCONT"")
;

Here are the results of the EXPLAIN for this query:

-- QUERY BLOCK 1

STEP 1 - ACCESS TABLE 'DUSESCHM.DUCONT' VIA AREA SWEEP

-- QUERY BLOCK 2

STEP 1 - ACCESS TABLE 'DUSESCHM.DUDEPT' VIA CALC KEY

STEP 2 - ACCESS TABLE 'DUSESCHM.DUCONT' VIA SET 'DUDEPT-DUCONT' (MEMBER)

STEP 3 - JOIN RESULTS OF STEPS 1 AND 2 USING SET

Outcomes