ca.portal.admin

Re:Re: SQL question

Discussion created by ca.portal.admin on Oct 10, 2008
I have an idea, but it isn't pretty. SQL doesn't support sets. So to
make IDMS-SQL work, I believe they use a foreign key. Since each record
type is the equivalent of a table, could you do a table scan and select
the rows where the foreign key is null?

Lutz Petzold
TDM UDB/IDMS Support
401-782-2265
Page 860 366 0865 or Telalert



This e-mail may contain confidential or privileged information. If
you think you have received this e-mail in error, please advise the
sender by reply e-mail and then delete this e-mail immediately.
Thank you. Aetna
"
IDMS Public Discussion Forum
IDMS-L@LISTSERV.IUASSN.COM
SMTP
IDMS-L@LISTSERV.IUASSN.COM
IDMS-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 =3D '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