Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: including 'is null' brings cartesian result

Re: including 'is null' brings cartesian result

From: Malcolm Dew-Jones <yf110_at_vtn1.victoria.tc.ca>
Date: 2 Mar 2005 12:36:52 -0800
Message-ID: <422623e4@news.victoria.tc.ca>


drdave_at_canoemail.com wrote:
: Sorry if this is not the group for pl/sql questions but here goes..

: I have a query that works fine but does not give me all the records I
: need due to 1 of the columns allowing null. When I include the join
: statement and include 'or columname' is null the result is a cartesian
: product.

: here is an abbreviated cross view of the data and columnames..

: Minimum_wage table

: id amount effective_date mw_ind jurisdiction_id variation_id
: 1, 1.25, 7/1/1965, 1, 1, null,
: 2, 1.65, 7/1/1970, 0, 1, null,
: 3, 1.75, 7/1/1971, 0, 1, 87,
: 4, 1.75, 7/1/1971, 0, 1, 88,

: MINIMUM_WAGE_VARIATION
: minimum_wage_variation_id englishtext
: 87 blah
: 88 blah blah

: So when I run the following query I get the non-null values only

: SELECT mw.minimum_wage_amount, mw.effective_date, mw.prov_prov_id,
: mw.mwv_minimum_wage_variation_id,
: mwv.minimum_wage_variation_id,mwv.english_text AS variationname
: FROM MINIMUM_WAGE mw,MINIMUM_WAGE_VARIATION mwv
: WHERE mw.mwv_minimum_wage_variation_id = mwv.minimum_wage_variation_id
: AND mw.expiry_date is null
: AND mw.minimum_wage_ind = 1
: AND mw.jt_jurisdiction_type_id = 2

Two ways to do this come to mind, though I haven't tested either technique recently so if it were me these would just be starting points.

-1- Convert the nulls into something that can't match and then do an outer join on the other table

	FROM MINIMUM_WAGE mw,MINIMUM_WAGE_VARIATION mwv
	WHERE 
		nvl(mw.mwv_minimum_wage_variation_id,-1000000) 
	      = mwv.minimum_wage_variation_id(+)

(there may be some limitation on what oracle will do here, so i'm not sure about this one).

-2- Select the nulls seperately and union them with the join

	SELECT  "the above"
	--
	UNION ALL
	--
	SELECT columns that will have the same layout as above
	FROM 
		Minimum_wage
	WHERE
		minimum_wage_variation_id is null

$0.02

--

This space not for rent.
Received on Wed Mar 02 2005 - 14:36:52 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US