Solved

Use of outer joins with a constant in Oracle discoverer

Posted on 2006-07-18
10
2,411 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]
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
>>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
Comment Utility
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
Comment Utility
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
Comment Utility
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.

Join & Write a Comment

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…
Introduction A previously published article on Experts Exchange ("Joins in Oracle", http://www.experts-exchange.com/Database/Oracle/A_8249-Joins-in-Oracle.html) makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

772 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

12 Experts available now in Live!

Get 1:1 Help Now