Re: Two SQL questions.

Is the data set flawed? If not then your solution does not work. On Thu, 18 Sep 1997 17:26:31 GMT, "Dan Clamage" <clamage_at_mime.dw.lucent.com> wrote:

>
>
>stephen <c6635500_at_comp.polyu.edu.hk> wrote in article
><34218647.1CDD_at_comp.polyu.edu.hk>...
>> 1)How to know the version of SQL*NET in SQL*PLUS? (i.e. by which
>> SQL statement)
>I've looked high and low, and as far as I can see, you can't except when
>you first start SQL*plus (that's when the release version is displayed).
>The Sqlplus version isn't stored in the database. Perhaps that's because
>more than one version of sqlplus can be used against any particular server
>version; they're backward-compatible. The closest thing I found was
>v$session.program, which tells you the program invocation for each user
>connected to the server. Now, if you made a copy of sqlplus with a new name
>including the version (as a suffix perhaps) then you could parse it out. I
>haven't tried it though.
>>
>> 2)Assume three tables in Oracle.
>> Table Attributes
>> ----- ----------
>> T1 A, B
>> T2 A, C
>> T3 A, D
>> A B A C A D
>> ----- ---- -----
>> a1 b1 a1 c1 a1 d1
>> a2 b2 a2 c2
>> a3 b3 a3 d3
>> I want the result is:
>> row1: a1 b1 c1 d1
>> row2: a2 b2 c2
>> row3: a3 b3 d3
>> How to do this by a single SQL statement?
>[BTW the data set you presented above for T2 and T3 was flawed; I corrected
>them above (you had rows (a1,c2) and (a1,d3) by mistake) - djc]
>
>You need to perform an outer join on the tables (T2, T3) which may not have
>matching rows in the main table (T1) on the specified key (a). You want a
>row returned for T1 regardless of whether a matching row was found in T2 or
>T3. The value returned for columns in T2 or T3 when no matching row exists
>is NULL.
>select T1.a, T1.b, T2.c, T3.d
>from T1, T2, T3
>where
> T2.a (+) = T1.a AND
> T3.a (+) = T1.a;
>Returns the data set you want.
>
>The (+) symbol means outer join; since it's next to T2 and T3, their
>columns may be NULL if no matching row is found in these tables.
>Note that if you chained the keys as with:
>where
> T2.a (+) = T1.a AND
> T3.a (+) = T2.a;
>Then row 3 would be:
>A B C D
>-- -- -- --
>a3 b3
>Which isn't what you want (you lost d3).
>
>If you tried:
>where
> T2.a (+) = T1.a AND
> T2.a (+) = T3.a;
>You get ORA-01417: a table may be outer joined to at most one other table.
>
>- Dan Clamage dclamage_at_idcomm.com
Received on Thu Sep 18 1997 - 00:00:00 CDT