Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2670
  • 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
NEW Veeam Backup for Microsoft Office 365 1.5

With Office 365, it’s your data and your responsibility to protect it. NEW Veeam Backup for Microsoft Office 365 eliminates the risk of losing access to your Office 365 data.

 
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

[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

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