Solved

Use of outer joins with a constant in Oracle discoverer

Posted on 2006-07-18
10
2,454 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 142

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
 

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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
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:lappins
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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Article by: Swadhin
From the Oracle SQL Reference (http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/queries006.htm) we are told that a join is a query that combines rows from two or more tables, views, or materialized views. This article provides a glimps…
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 Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.

912 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

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now