Re: Two SQL questions.

From: Dan Clamage <clamage_at_mime.dw.lucent.com>
Date: 1997/09/18
Message-ID: <01bcc41e$5345af30$54110b87@clamagent>#1/1

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