Outer Join - Oracle and ANSI SQL Syntax Answer

This  refers to the previous blog here.
The Standard Oracle syntax is pretty straightforward.

SELECT fooa.foo1,
  fooa.foo2,
  foob.foo2
FROM foo fooa,
  foo foob
WHERE fooa.foo1 =foob.foo1(+)
AND fooa.foo3   ='Coming'
AND foob.foo3(+)='Going'


The ANSI SQL syntax is a little trickier.

SELECT fooa.foo1,
fooa.foo2,
foob.foo2
FROM foo fooa
LEFT OUTER JOIN foo foob
ON (fooa.foo1 =foob.foo1
AND foob.foo3 ='Going')
WHERE fooa.foo3='Coming'

There you go for outer joins.

This should be a very good interview question.
 

Outer Join - Oracle and ANSI SQL Syntax Question

Outer joins are really fun. Well kind of. Anyway, here is an example.

The Optimizer blog from Oracle is very useful as well. Oracle Optimizer Team Blog

The setup.

create table  foo (foo1 number, foo2 varchar2(5),foo3 varchar2(10));

insert into foo values (1,'In1','Coming');

insert into  foo values (1,'Out1','Going');
insert into  foo values (2,'In2','Coming');
commit;

select * from foo;

FOO1	FOO2	FOO3
1	In1	Coming
1	Out1	Going
2	In2	Coming

 We need the following output for a report.

 

FOO1	FOO2	FOO2
1	In1	Out1
2	In2	(Null)

 

Try this for yourself. The answer is in the next blog.

An excellent reference.

https://blogs.oracle.com/optimizer/entry/outerjoins_in_oracle

 

 

OBIEE 11.1.1.7 is now out

OK..it has been a while. I am hoping to have some time to update this blog.

Oracle  released the OBIEE 11.1.1.7 version of the software. It is available for download.

The Link

As for Exalytics, this version is not certified yet. It will be soon.