[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2713
  • Last Modified:

Use of outer joins with a constant in Oracle discoverer

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
pjd1
Asked:
pjd1
3 Solutions
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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
 
pjd1Author Commented:
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
 
MohanKNairCommented:
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
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
pjd1Author Commented:
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
 
sathyagiriCommented:
>>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
 
riazpkCommented:
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
 
riazpkCommented:
well, we are intrested in resolving the issue but it there is no reply from Author after my reply.
0
 
Stephen LappinSenior TechnologistCommented:
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

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now