Home » Developer & Programmer » Warehouse Builder » Left Outer join (10.2)
Left Outer join [message #356824] Sun, 02 November 2008 11:44 Go to next message
nithinlenin
Messages: 9
Registered: October 2008
Location: Bangalore
Junior Member
Can i use a left outer join in OWB as in Oracle 10g . i.e using the key word left outer join rather than using the + syntax
Re: Left Outer join [message #356837 is a reply to message #356824] Sun, 02 November 2008 14:44 Go to previous messageGo to next message
Littlefoot
Messages: 20901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Did you try? What happened?
Re: Left Outer join [message #356855 is a reply to message #356824] Sun, 02 November 2008 21:40 Go to previous messageGo to next message
nithinlenin
Messages: 9
Registered: October 2008
Location: Bangalore
Junior Member
Yapp..
I had tried the join in OWB using the (+) syntax ..

I will give u a brief summary of the scenario

I have two tables
tab A ( with column sales date and country_code ... )
tab B (with columns country_id , valid_to_date, valid_from_date)

my joins goes like this

select *
from
tab A,
tab B
where
tab A. country_code = tab B.country_id (+)
and
tab A.sales_date between tab B.valid_from_date (+) and tab B.valid_to_date(+)

for this am getting all the records in table A and corresponding columns in Table B , but the values for Table B columns are showing as null


But when i use the following query in Oracle 10g database .

Select * from
tab A left outer join
tab B
ON
tab A. country_code = tab B.country_id
and
tab A.sales_date between tab B.valid_from_date and tab B.valid_to_date

I am getting the desired output ..

So , is it possible to use the key word left outer join in OWB , then i can use the same .
Re: Left Outer join [message #356905 is a reply to message #356855] Mon, 03 November 2008 01:38 Go to previous messageGo to next message
Littlefoot
Messages: 20901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
That's what I have asked: did you use the key word "left outer join" in OWB? What happened?
Re: Left Outer join [message #356910 is a reply to message #356824] Mon, 03 November 2008 01:47 Go to previous messageGo to next message
nithinlenin
Messages: 9
Registered: October 2008
Location: Bangalore
Junior Member
yapp .. i had done that
Re: Left Outer join [message #356919 is a reply to message #356910] Mon, 03 November 2008 02:51 Go to previous messageGo to next message
Littlefoot
Messages: 20901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
So? What did you find out? Can you use "left outer join" in OWB or not?
Re: Left Outer join [message #356921 is a reply to message #356824] Mon, 03 November 2008 03:08 Go to previous messageGo to next message
nithinlenin
Messages: 9
Registered: October 2008
Location: Bangalore
Junior Member
iam not able to use it ..
Its giving me a validation error saying invalid relational operator .

I can't use the + syntax as i am having an OR condition ..

ie
((tab A . sales_date between tab B.valid from date and tab B.valid to date )
OR
(tab A . sales_date > tab B.valid from date and tab B.valid to date is null))

Currently i have found a solution using DECODE keyword ..

but if i am able to use the keyword left outer join in OWB , my code would look much simpler
Re: Left Outer join [message #356928 is a reply to message #356921] Mon, 03 November 2008 04:24 Go to previous messageGo to next message
Littlefoot
Messages: 20901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
OK, I understand it now.

I'm glad you've managed to solve the problem. Perhaps someone who knows OWB will be able to explain whether "LEFT OUTER JOIN" really can not be used there, or you just didn't find a way to implement it.
Re: Left Outer join [message #356939 is a reply to message #356824] Mon, 03 November 2008 04:59 Go to previous messageGo to next message
nithinlenin
Messages: 9
Registered: October 2008
Location: Bangalore
Junior Member
The truth is that i was not able to implement it , that leading me to find other alternative Razz

Thanks for responding .
I believe we need to make this frum more lively Razz
Re: Left Outer join [message #416397 is a reply to message #356939] Sat, 01 August 2009 10:03 Go to previous messageGo to next message
lijosv
Messages: 2
Registered: August 2009
Location: India
Junior Member
ANSI SQL joins were introduced in Oracle 9i.

u cannot use ANSI syntaxs like FULL OUTER JOIN in mappings.
Mapping anyways needs to be created using (+)
But if u keep the ANSI sql syntax parameter in the mapping configuration to TRUE, the code generated will have FULL OUTER JOIN.

Not sure if there are any new features available in OWB to do this.
Re: Left Outer join [message #416400 is a reply to message #356824] Sat, 01 August 2009 10:10 Go to previous message
lijosv
Messages: 2
Registered: August 2009
Location: India
Junior Member
also reg below

I can't use the + syntax as i am having an OR condition ..

i guess this error can be avoided using sub queries Smile
Previous Topic: owb study guide for frshers
Next Topic: owb hang creating/designing/editing cube
Goto Forum:
  


Current Time: Sat Dec 10 05:20:14 CST 2016

Total time taken to generate the page: 0.09558 seconds