Solved

Use of outer joins with a constant in Oracle discoverer

Posted on 2006-07-18
10
2,524 Views
Last Modified: 2013-12-12
Hi
I'm trying to write a report that has an outer join to a constant.  I have the following SQL:

SELECT .......
FROM   ........
     WHERE ext_trad_ref.cmpy_num(+) = trade.cmpy_num
     AND ext_trad_ref.trad_tag(+) = trade.trad_tag
     AND ext_trad_ref.ref_type(+) = 'DEA'

The first two lines were easy enough to replicate in the admin edition and work fine, however the 3rd line is the sticking point.  I originally set it up as a condition as normal but I can't see anywhere where you can specify that it's to be an outer join.
Has anyone an idea of how to do this.  The report is perfect except for this 1 line.

thanks

Paul
0
Comment
Question by:pjd1
10 Comments
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 17128930
SELECT .......
FROM   trade
LEFT JOIN ext_trad_ref
ON  ext_trad_ref.cmpy_num = trade.cmpy_num
AND ext_trad_ref.trad_tag  = trade.trad_tag
AND ext_trad_ref.ref_type = 'DEA'
0
 

Author Comment

by:pjd1
ID: 17128970
thanks for the above but to clarify, my issue is not with the SQL - that works fine in SQL plus and I get the results I want.  What I can't do is get the equivalent line into Discoverer.  The line I need is

AND ext_trad_ref.ref_type(+) = 'DEA'

thanks
0
 
LVL 16

Accepted Solution

by:
MohanKNair earned 100 total points
ID: 17129164
Use of outer joins with a constant has no effect. Use inline view

(select 'DEA' name from dual)


SELECT .......
FROM  ext_trad_ref, trade, (select 'DEA' name from dual) c
     WHERE ext_trad_ref.cmpy_num(+) = trade.cmpy_num
     AND ext_trad_ref.trad_tag(+) = trade.trad_tag
     AND ext_trad_ref.ref_type(+) = c.name

0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:pjd1
ID: 17129227
If I take the outer join out I do get different results.  With the outer join there I get all the rows I need - if I take the (+) off I do not get the rows returned where the ref type is null.

I did try the above changes though but got an error because outer joins can only be referenced to one table.

thanks
0
 
LVL 14

Assisted Solution

by:sathyagiri
sathyagiri earned 100 total points
ID: 17132336
>>if I take the (+) off I do not get the rows returned where the ref type is null.
You use this then probably
SELECT .......
FROM   ........
     WHERE ext_trad_ref.cmpy_num(+) = trade.cmpy_num
     AND ext_trad_ref.trad_tag(+) = trade.trad_tag
     AND nvl(ext_trad_ref.ref_type,'DEA') = 'DEA'
0
 
LVL 13

Assisted Solution

by:riazpk
riazpk earned 300 total points
ID: 17136287
SELECT .......
FROM   ........
     WHERE ext_trad_ref.cmpy_num(+) = trade.cmpy_num
     AND ext_trad_ref.trad_tag(+) = trade.trad_tag
     AND (ext_trad_ref.ref_type = 'DEA' OR ext_trad_ref.ref_type IS NULL)
0
 
LVL 13

Expert Comment

by:riazpk
ID: 17736828
well, we are intrested in resolving the issue but it there is no reply from Author after my reply.
0
 
LVL 7

Expert Comment

by:Stephen Lappin
ID: 17858898
You require to use Discoverer administrator to allow/create outer joins in Discoverer queries.

Open the business are and folder containing the Discoverer views. When you expand out the Dicoverer view to show the list of column, the defined joins are listed below them.

Right click on relevant join and select Edit Join. Then select the Options tab.
Under "Choose any optional join configurations", ensure either "Outer join on detail" or  "Outer join on master" is checked as appropriate.

Sláinte mhath
Stephen


0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Use of Exception to end a Loop 3 54
Oracle dataguard 5 58
ER Diagram 3 40
subtr returning incorrect value 8 65
Why doesn't the Oracle optimizer use my index? Querying too much data Most Oracle developers know that an index is useful when you can use it to restrict your result set to a small number of the total rows in a table. So, the obvious side…
How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…

756 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question