Re: SQL question

From: Wilco Oosterom <wilco_at_motor.demon.nl>
Date: Tue, 20 Jul 1999 22:16:54 +0200
Message-ID: <3794D936.8F9FF982@motor.demon.nl>

Have ypu trid rewriting the subselect to an EXISTS clause? Should be like this:

SELECT	A.* 
,	B.LIC_K_ID
,	B.BROKER_ID
,	B.OWNER
,	C.DSCR                LIC_TYPE_DSCR
,	D.LIC_STATUS_TYPE_ID
,	E.DSCR                LIC_STATUS_DSCR
FROM	LIC A
,	LIC_K B
,	LIC_TYPE C
,	LIC_STATUS D
,	LIC_STATUS_TYPE E
WHERE   B.BROKER_ID             = :BROKER_ID 
AND     A.LIC_ID                = B.LIC_ID 
AND	C.LIC_TYPE_ID           = A.LIC_TYPE_ID 
AND	D.LIC_ID(+)             = B.LIC_ID
AND	E.LIC_STATUS_TYPE_ID(+) = D.LIC_STATUS_TYPE_ID
AND EXISTS
	( SELECT 'x'
	  FROM   LIC_STATUS F
 	  WHERE  (      F.LIC_ID  = B.LIC_ID
	         AND    F.LIC_STATUS_ID = D.LIC_STATUS_ID
		 )
	  OR D.LIC_ID IS NULL
	)

This includes the d.LIC_ID IS NULL clause (Which is why you do the outer join) NULL = NULL is always False!

I believe this works, because It's rewritten as Exists it should even be faster too

Wilco

Jim Poe wrote:
>
> In the following select I am trying to retrieve a list of LIC
> information. The LIC table has a detail table LIC_STATUS. I'm only
> interested in retrieving one row per license. The outer joins allow me
> to retrieve rows without any status records. The subselect allows me to
> retrieve only one row for any license with multiple status rows. My
> problem is that the subselect overrides my outer joins and won't return
> a LIC row if there are no LIC_STATUS rows. Is this possible?
>
> SELECT A.* , B.LIC_K_ID, B.BROKER_ID, B.OWNER,
> C.DSCR LIC_TYPE_DSCR,D.LIC_STATUS_TYPE_ID, E.DSCR
> LIC_STATUS_DSCR
> FROM LIC A, LIC_K B, LIC_TYPE C, LIC_STATUS D,
> LIC_STATUS_TYPE E
> WHERE B.BROKER_ID=:BROKER_ID AND
> A.LIC_ID=B.LIC_ID AND
> C.LIC_TYPE_ID=A.LIC_TYPE_ID AND
> D.LIC_ID(+)=B.LIC_ID AND
> E.LIC_STATUS_TYPE_ID(+)=D.LIC_STATUS_TYPE_ID AND
> D.LIC_STATUS_ID=( SELECT MAX( F.LIC_STATUS_ID )
> FROM LIC_STATUS F
> WHERE F.LIC_ID=B.LIC_ID);
>
> Thanks
>
> --
> Jim Poe
> <jpoe_at_fulcrumit.com>
> Fulcrum InteTech, Inc.
Received on Tue Jul 20 1999 - 15:16:54 CDT