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 -> Select on Outer Join field failing the parent record

Select on Outer Join field failing the parent record

From: David Berg <dbergquist_at_bd.com>
Date: 14 Feb 2003 18:11:29 -0800
Message-ID: <1274998b.0302141811.9ffcd66@posting.google.com>


I've done extensive searches on null records in outer join and different ways to use NVL(), i just can't get it.

What i have is...

Table 1

ID1	FRUIT
---	-------
1	cherries
2	apples
3	apricots
4	bananas


Table 2

ID2	ID1 (FK)	TYPE	VALUE
----	----		-----	-----
1	1		color	red
2	1		size	1 inch
3	1		brand	bing
4	2		color	green
5	2		brand	golden
6	3		size	
	

------------------------

I am getting...

VIEW_WITH_MISSING_DATA

ID1	FRUIT		TYPE	VALUE
----	-----		------	------
1	cherries	size	1 inch
3	apricots	size

Select ID1, FRUIT, TYPE, VALUE
From TABLE1, TABLE2
Where TABLE1.ID1=TABLE2.ID1 (+)

        and TABLE2.TYPE ="size"



What i want for output is ....

VIEW1

ID1	FRUIT		TYPE	VALUE
----	-----		-----	------
1	cherries	size	1 inch
2	apples		size
3	appricots	size
4	bananas		size


------------------------

Extended explanation ....

My true preference for output is more like below. I would like to select 1 or more Types with an IN clause and get back a column for each type but don't know if it is possible to account for the number of variations of Types to transform to columns and that this View would be used in Crystal Reports.....

VIEW2

ID1	FRUIT		COLOR	SIZE
----	-----		------	------
1	cherries	red	1 inch
2	apples		green	
3	appricots	
4	bananas		


Dozens of different versions of the report are needed using 1-7 of 500 possible Types (displayed as columns) depending upon the characteristics that are needed to define that category of "fruit". Any attached characteristic to that fruit can have no Value (see Table2 record 6). So there are 500 possible Types used throughout system though only 5-40 will be assigned to that "fruit" and stored in Table2 of which only 0-7 would need to be listed as columns on any report.

Whatever View I come up with will be used in conjuction with a Crystal Report so i believe the field names need to be static for every time the view is used. For this reason i was thinking that a view that returned 1 column Type at a time would suffice as i specified in View1. I would link from the main report tables to view where the View.Type="DesiredColumn" that i want for that particular report and then link up to 7 additional times for each column that is needed and select the 1 Value column i need for view to return for that Type.

I'm a report writer by trade, so i am a little bit out of my territory when it comes to designing these sorts of views. Received on Fri Feb 14 2003 - 20:11:29 CST

Original text of this message

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