Re: What the hell is going on with these correlation tables?

well, it _should_ work, according to Oracle 8 documentation. So, we are down to guesswork.

Try defining a type for col1-4, and then do a CAST(MULTISET( select ..). Perhaps this will force Oracle to come up with a homogenous collection, including the rows on which one or more of the outer joins are missing.

Andrew Schonberger

On Thu, 10 Dec 1998 20:44:38 GMT, buzkoff_at_my-dejanews.com wrote:

>
>
>I'm trying to update column with a SELECT which is a joint using the following
>request:
>
>
> UPDATE supertable i1
> SET
> (col1_name,
> col2_name,
> col3_description,
> col4_description) =
> (SELECT
> Distinct
> NVL(c1.col_description,' '),
> NVL(c2.col_description,' '),
> NVL(c3.col_description,' '),
> NVL(c4.col_description,' ')
> FROM business s1,
> codes s2,
> accounts s3,
> offices s4
> WHERE
> s1.col_number(+) = i1.col1_number
> and
> s2.col_number(+) = i1.col2_number
> and
> s2.col_number(+) = i1.col3_number
> and
> s4.col_number(+) = i1.col3_number
>
>
>
>Outer join is used because columns in supertable may and maynot be translated
>with the satellites (business,codes,accounts,offices). The problem is that
>updated are only the records for which all 4 values may be found in
>translation tables. But when I check the SELECT with
>
>
>SELECT
> Distinct
> NVL(c1.col_description,' '),
> NVL(c2.col_description,' '),
> NVL(c3.col_description,' '),
> NVL(c4.col_description,' '),
> i1.*
> FROM business s1,
> codes s2,
> accounts s3,
> offices s4,
> supertable i1
> WHERE
> s1.col_number(+) = i1.col1_number
> and
> s2.col_number(+) = i1.col2_number
> and
> s2.col_number(+) = i1.col3_number
> and
> s4.col_number(+) = i1.col3_number
>
>
>I see all records retrieved. DOes anybody know what the trick is?
>
>-----------== Posted via Deja News, The Discussion Network ==----------
>http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own
Received on Mon Dec 14 1998 - 00:00:00 CST