| Left Outer join [message #356824] |
Sun, 02 November 2008 11:44  |
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 #356855 is a reply to message #356824] |
Sun, 02 November 2008 21:40  |
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 #356921 is a reply to message #356824] |
Mon, 03 November 2008 03:08 |
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 #416397 is a reply to message #356939] |
Sat, 01 August 2009 10:03 |
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 |
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 
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
