Home » SQL & PL/SQL » SQL & PL/SQL » Problem with FULL OUTER JOIN
Problem with FULL OUTER JOIN [message #284001] Wed, 28 November 2007 10:06 Go to next message
klkuab
Messages: 25
Registered: November 2007
Junior Member
I am trying to run the following sql but am getting an Oracle error ORA-00918: column ambiguously defined

select b.acct_unit, sum(b.budget_amount), sum(s.spend_amount)
from uabhssd.vwuabbudget b full outer join
uabhssd.vwuabspend s on
b.fiscal_year = s.fiscal_year and
b.acct_period = s.acct_period and
b.account = s.account
group by b.acct_unit, s.acct_unit

it executes fine if I replace the "full outer join" with either "right outer join" or "left outer join", however I need to use the "full outer join" to return the results that my users want.

thoughts, ideas?
Re: Problem with FULL OUTER JOIN [message #284002 is a reply to message #284001] Wed, 28 November 2007 10:10 Go to previous messageGo to next message
pablolee
Messages: 2834
Registered: May 2007
Location: Scotland
Senior Member
When you post code in Orafaq, please remember to use code tags to help create a readable output.
have a look at our guidelines

Why are you grouping by s.acct_unit?
Re: Problem with FULL OUTER JOIN [message #284004 is a reply to message #284002] Wed, 28 November 2007 10:17 Go to previous messageGo to next message
klkuab
Messages: 25
Registered: November 2007
Junior Member
sorry, I should have removed that. been playing with this code for a while now with various combinations.

if i remove that and only group by b.acct_unit I get the same result ORA-00918: column ambiguously defined when trying to use the FULL OUTER JOIN, works fine with RIGHT or LEFT.

thanks!
Re: Problem with FULL OUTER JOIN [message #284005 is a reply to message #284004] Wed, 28 November 2007 10:19 Go to previous messageGo to next message
pablolee
Messages: 2834
Registered: May 2007
Location: Scotland
Senior Member
Strange.
Not that we don't believe you Wink but can you post a copy and paste of your session for us (just in case anything was 'lost in translation'
Re: Problem with FULL OUTER JOIN [message #284007 is a reply to message #284001] Wed, 28 November 2007 10:21 Go to previous messageGo to next message
klkuab
Messages: 25
Registered: November 2007
Junior Member
here is more readable code:

select b.acct_unit, sum(b.budget_amount), 
             sum (s.spend_amount)
from uabhssd.vwuabbudget b full outer join
     uabhssd.vwuabspend s on 
     b.fiscal_year = s.fiscal_year and
     b.acct_period = s.acct_period and
     b.account     = s.account
group by b.acct_unit
Re: Problem with FULL OUTER JOIN [message #284008 is a reply to message #284007] Wed, 28 November 2007 10:25 Go to previous messageGo to next message
pablolee
Messages: 2834
Registered: May 2007
Location: Scotland
Senior Member
OK, I don't think that this will resolve it but try

select b.acct_unit
           , sum(b.budget_amount)
           , sum (s.spend_amount)
from uabhssd.vwuabbudget b 
full outer join uabhssd.vwuabspend s 
USING(fiscal_year,acct_period,account)
group by b.acct_unit
and copy and paste that session here.
Re: Problem with FULL OUTER JOIN [message #284009 is a reply to message #284001] Wed, 28 November 2007 10:26 Go to previous messageGo to next message
klkuab
Messages: 25
Registered: November 2007
Junior Member
No Message Body
Re: Problem with FULL OUTER JOIN [message #284011 is a reply to message #284009] Wed, 28 November 2007 10:29 Go to previous messageGo to next message
pablolee
Messages: 2834
Registered: May 2007
Location: Scotland
Senior Member
Cheers for that, any chance you could try that in sql*plus (just in case it's a toad 'undocumented feature'. I can't see anything wrong with what you are doing.
Re: Problem with FULL OUTER JOIN [message #284012 is a reply to message #284008] Wed, 28 November 2007 10:29 Go to previous messageGo to next message
klkuab
Messages: 25
Registered: November 2007
Junior Member
that gave me the same error message
Re: Problem with FULL OUTER JOIN [message #284013 is a reply to message #284012] Wed, 28 November 2007 10:30 Go to previous messageGo to next message
pablolee
Messages: 2834
Registered: May 2007
Location: Scotland
Senior Member
Do you mean the sample code, or running it in sqlplus?
Re: Problem with FULL OUTER JOIN [message #284015 is a reply to message #284001] Wed, 28 November 2007 10:32 Go to previous messageGo to next message
klkuab
Messages: 25
Registered: November 2007
Junior Member
I've tried the same code in Oracle SQL Developer, and get the same result.

I've used the FULL OUTER JOIN before with no problems.
Re: Problem with FULL OUTER JOIN [message #284019 is a reply to message #284015] Wed, 28 November 2007 10:36 Go to previous messageGo to next message
Mohammad Taj
Messages: 2412
Registered: September 2006
Location: Dubai, UAE
Senior Member

ohhh...please run your query on SQLPLUS command line and post here your session output with error message.

1. START ---> RUN ----> sqlplus /nolog <<hit enter>>

[Updated on: Wed, 28 November 2007 10:36]

Report message to a moderator

Re: Problem with FULL OUTER JOIN [message #284023 is a reply to message #284001] Wed, 28 November 2007 10:39 Go to previous messageGo to next message
klkuab
Messages: 25
Registered: November 2007
Junior Member
No Message Body
Re: Problem with FULL OUTER JOIN [message #284027 is a reply to message #284015] Wed, 28 November 2007 10:41 Go to previous messageGo to next message
pablolee
Messages: 2834
Registered: May 2007
Location: Scotland
Senior Member
I take it both eitheruabhssd.vwuabbudget and uabhssd.vwuabspend are tables and not views (I'm not even sure that that is relevant Sad )
Have you tried the Oracle syntax i.e. using the where clause.
Try unioning the left and right outer joins to see if that works (this is all a bit 'Ready Fire AIM' as one of the gurus here would say)
Re: Problem with FULL OUTER JOIN [message #284029 is a reply to message #284023] Wed, 28 November 2007 10:47 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
klkuab wrote on Wed, 28 November 2007 17:39

Nothing

Who, what, when, why gave people the idea that MicroSoft Word is the tool for sharing screenshots? Wordprocessors are, well for processing text. Maybe add some pictures, but mainly text.
If you want to share pictures, save them as pictures. Preferrably PNG or JPG.

And do you really thing that creating a screenshot, opening Word, pasting the screenshot in Word, saving the document, attaching the document to the reply is easier then copying the text from sqlplus and paste it in the message-body?
Re: Problem with FULL OUTER JOIN [message #284030 is a reply to message #284001] Wed, 28 November 2007 10:47 Go to previous messageGo to next message
klkuab
Messages: 25
Registered: November 2007
Junior Member
They are 2 Views.

Can't use the WHERE clause in place of the ON, it gives an INVALID SQL STATEMENT error message.
Re: Problem with FULL OUTER JOIN [message #284031 is a reply to message #284029] Wed, 28 November 2007 10:49 Go to previous messageGo to next message
klkuab
Messages: 25
Registered: November 2007
Junior Member
If you're working in TOAD or SQL DEVELOPER and NOT in SQL PLUS, then YES I DO think pasting into WORD works fine.
Re: Problem with FULL OUTER JOIN [message #284033 is a reply to message #284031] Wed, 28 November 2007 10:51 Go to previous messageGo to next message
pablolee
Messages: 2834
Registered: May 2007
Location: Scotland
Senior Member
The problem with this is that most people will not open .doc files etc (as they may contain viruses) many others simply can't open the (don't have the software, adhering to company policy. In addition it is a LOT easier for use to view well formatted code within the thread rather than all these attachments.
Re: Problem with FULL OUTER JOIN [message #284035 is a reply to message #284001] Wed, 28 November 2007 10:53 Go to previous messageGo to next message
klkuab
Messages: 25
Registered: November 2007
Junior Member
duly noted
Re: Problem with FULL OUTER JOIN [message #284037 is a reply to message #284030] Wed, 28 November 2007 10:54 Go to previous messageGo to next message
pablolee
Messages: 2834
Registered: May 2007
Location: Scotland
Senior Member
klkuab wrote on Wed, 28 November 2007 16:47

They are 2 Views.

Can't use the WHERE clause in place of the ON, it gives an INVALID SQL STATEMENT error message.

1. Please post the definition of the views.
2. if it gave an invalid statement error then you issued incorrect syntax. what did you use?
3. Did you try the union method?

Quote:

duly noted

Thumbs Up cheers

[Updated on: Wed, 28 November 2007 10:55]

Report message to a moderator

Re: Problem with FULL OUTER JOIN [message #284038 is a reply to message #284001] Wed, 28 November 2007 10:59 Go to previous messageGo to next message
klkuab
Messages: 25
Registered: November 2007
Junior Member
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options

SQL> select b.acct_unit, sum(b.budget_amount), sum(s.spend_amount)
  2  from uabhssd.vwuabbudget b FULL OUTER JOIN uabhssd.vwuabspend s
  3  ON (b.fiscal_year = s.fiscal_year and
  4      b.acct_period = s.acct_period and
  5      b.account     = s.account)
  6  group by b.acct_unit;
select b.acct_unit, sum(b.budget_amount), sum(s.spend_amount)
*
ERROR at line 1:
ORA-00918: column ambiguously defined


SQL> 


If you use WHERE in place of ON you get a SQL syntax error.

 SQL> desc uabhssd.vwuabbudget;
 Name                                      Null?    Type
 ----------------------------------------- -------- ------------
 FISCAL_YEAR                                        NUMBER(4)
 ACCT_PERIOD                                        NUMBER
 ACCOUNT                                            NUMBER(6)
 ACCT_UNIT                                          CHAR(15)
 SUMRY_ACCT_ID                                      NUMBER(6)
 BUDGET_AMOUNT                                      NUMBER(18,2)

SQL> desc uabhssd.vwuabspend;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------
 FISCAL_YEAR                               NOT NULL NUMBER(4)
 ACCT_PERIOD                               NOT NULL NUMBER(2)
 ACCOUNT                                   NOT NULL NUMBER(6)
 ACCT_UNIT                                 NOT NULL CHAR(15)
 SUMRY_ACCT_ID                             NOT NULL NUMBER(6)
 SPEND_AMOUNT                                       NUMBER 

Re: Problem with FULL OUTER JOIN [message #284039 is a reply to message #284038] Wed, 28 November 2007 11:02 Go to previous messageGo to next message
pablolee
Messages: 2834
Registered: May 2007
Location: Scotland
Senior Member
Quote:


If you use WHERE in place of ON you get a SQL syntax error.

Yes you do. But then, that is not how you outer join using where clause.

Left outer join
from tab1, tab2
where tab1.col1 = tab2.col1(+)

right outer join
from tab1, tab2
where tab1.col1(+) = tab2.col1


Again I ask you if you have attempted the union method that I suggested
Also, I should have been clearer in my request, can you post the DDL of the views. TA.

Note, I'm leaving the office in a few minutes, I'll look in a little later on this evening to see if you have a resolution. Good luck, hopefully someone will see what I am obviously not seeing.

[Updated on: Wed, 28 November 2007 11:05]

Report message to a moderator

Re: Problem with FULL OUTER JOIN [message #284049 is a reply to message #284001] Wed, 28 November 2007 11:29 Go to previous messageGo to next message
klkuab
Messages: 25
Registered: November 2007
Junior Member
I can use the following with the UNION ALL and get the data back, except that it comes in 2 rows per ACCT_UNIT, one from VWUABBUDGET and one from VWUABSPEND. I need the data on the same row

select b.acct_unit, sum(b.budget_amount)
from uabhssd.vwuabbudget b
where b.fiscal_year = 2008 and
	  b.acct_period = 1 and
	  b.account = 702010
group by b.acct_unit
union all
select s.acct_unit, sum(s.spend_amount)
from uabhssd.vwuabspend s
where s.fiscal_year = 2008 and
	  s.acct_period = 1 and
	  s.account = 702010
group by s.acct_unit
order by acct_unit
Re: Problem with FULL OUTER JOIN [message #284052 is a reply to message #284001] Wed, 28 November 2007 11:44 Go to previous messageGo to next message
klkuab
Messages: 25
Registered: November 2007
Junior Member
I think this is what you were asking for:

CREATE OR REPLACE VIEW VWUABBUDGET
(FISCAL_YEAR, ACCT_PERIOD, ACCOUNT, ACCT_UNIT, SUMRY_ACCT_ID, 
 BUDGET_AMOUNT)
AS 
select b.fiscal_year, 1 as acct_period, b.account, b.acct_unit, 
       c.sumry_acct_id, b.db_amount_01 as budget_amount
from uabhssd.fbdetail b,
     uabhssd.glchartdtl c
where b.company     = 100  and
      b.budget_nbr  = 1    and
	  c.account     = b.account
union all
select b.fiscal_year, 2 as acct_period, b.account, b.acct_unit, 
       c.sumry_acct_id, b.db_amount_02 as budget_amount
from uabhssd.fbdetail b,
     uabhssd.glchartdtl c
where b.company     = 100  and
      b.budget_nbr  = 1    and
	  c.account     = b.account
union all
select b.fiscal_year, 3 as acct_period, b.account, b.acct_unit, 
       c.sumry_acct_id, b.db_amount_03 as budget_amount
from uabhssd.fbdetail b,
     uabhssd.glchartdtl c
where b.company     = 100  and
      b.budget_nbr  = 1    and
	  c.account     = b.account
union all
select b.fiscal_year, 4 as acct_period, b.account, b.acct_unit, 
       c.sumry_acct_id, b.db_amount_04 as budget_amount
from uabhssd.fbdetail b,
     uabhssd.glchartdtl c
where b.company     = 100  and
      b.budget_nbr  = 1    and
	  c.account     = b.account
union all
select b.fiscal_year, 5 as acct_period, b.account, b.acct_unit, 
       c.sumry_acct_id, b.db_amount_05 as budget_amount
from uabhssd.fbdetail b,
     uabhssd.glchartdtl c
where b.company     = 100  and
      b.budget_nbr  = 1    and
	  c.account     = b.account
union all
select b.fiscal_year, 6 as acct_period, b.account, b.acct_unit, 
       c.sumry_acct_id, b.db_amount_06 as budget_amount
from uabhssd.fbdetail b,
     uabhssd.glchartdtl c
where b.company     = 100  and
      b.budget_nbr  = 1    and
	  c.account     = b.account
union all
select b.fiscal_year, 7 as acct_period, b.account, b.acct_unit, 
       c.sumry_acct_id, b.db_amount_07 as budget_amount
from uabhssd.fbdetail b,
     uabhssd.glchartdtl c
where b.company     = 100  and
      b.budget_nbr  = 1    and
	  c.account     = b.account
union all
select b.fiscal_year, 8 as acct_period, b.account, b.acct_unit, 
       c.sumry_acct_id, b.db_amount_08 as budget_amount
from uabhssd.fbdetail b,
     uabhssd.glchartdtl c
where b.company     = 100  and
      b.budget_nbr  = 1    and
	  c.account     = b.account
union all
select b.fiscal_year, 9 as acct_period, b.account, b.acct_unit, 
       c.sumry_acct_id, b.db_amount_09 as budget_amount
from uabhssd.fbdetail b,
     uabhssd.glchartdtl c
where b.company     = 100  and
      b.budget_nbr  = 1    and
	  c.account     = b.account
union all
select b.fiscal_year, 10 as acct_period, b.account, b.acct_unit, 
       c.sumry_acct_id, b.db_amount_10 as budget_amount
from uabhssd.fbdetail b,
     uabhssd.glchartdtl c
where b.company     = 100  and
      b.budget_nbr  = 1    and
	  c.account     = b.account
union all
select b.fiscal_year, 11 as acct_period, b.account, b.acct_unit, 
       c.sumry_acct_id, b.db_amount_11 as budget_amount
from uabhssd.fbdetail b,
     uabhssd.glchartdtl c
where b.company     = 100  and
      b.budget_nbr  = 1    and
	  c.account     = b.account
union all
select b.fiscal_year, 12 as acct_period, b.account, b.acct_unit, 
       c.sumry_acct_id, b.db_amount_12 as budget_amount
from uabhssd.fbdetail b,
     uabhssd.glchartdtl c
where b.company     = 100  and
      b.budget_nbr  = 1    and
	  c.account     = b.account
/




CREATE OR REPLACE VIEW VWUABSPEND
(FISCAL_YEAR, ACCT_PERIOD, ACCOUNT, ACCT_UNIT, SUMRY_ACCT_ID, 
 SPEND_AMOUNT)
AS 
select t.fiscal_year, t.acct_period, t.account, t.acct_unit,
       c.sumry_acct_id, sum(t.base_amount) as spend_amount
from uabhssd.gltrans t,
     uabhssd.glchartdtl c
where t.company     = 100  and
      t.r_status    = 9    and
	  c.account     = t.account
group by t.fiscal_year, t.acct_period, t.account, t.acct_unit, c.sumry_acct_id
/

Re: Problem with FULL OUTER JOIN [message #284073 is a reply to message #284052] Wed, 28 November 2007 14:38 Go to previous messageGo to next message
pablolee
Messages: 2834
Registered: May 2007
Location: Scotland
Senior Member
Try:
select b.acct_unit, sum(b.budget_amount), sum(s.spend_amount)
    from uabhssd.vwuabbudget b 
LEFT OUTER JOIN uabhssd.vwuabspend s
    ON (b.fiscal_year = s.fiscal_year and
        b.acct_period = s.acct_period and
        b.account     = s.account)
    group by b.acct_unit
UNION
select b.acct_unit, sum(b.budget_amount), sum(s.spend_amount)
    from uabhssd.vwuabbudget b 
RIGHT OUTER JOIN uabhssd.vwuabspend s
    ON (b.fiscal_year = s.fiscal_year and
        b.acct_period = s.acct_period and
        b.account     = s.account)
    group by b.acct_unit

Even if this does work, I can't see why you are getting your previously mentioned error...
Re: Problem with FULL OUTER JOIN [message #284472 is a reply to message #284073] Thu, 29 November 2007 16:45 Go to previous messageGo to next message
klkuab
Messages: 25
Registered: November 2007
Junior Member
This last one with the union works,
but it puts the results on separate rows (1 row from each select statement), whereas I want the results to be side by side.



Re: Problem with FULL OUTER JOIN [message #284473 is a reply to message #284001] Thu, 29 November 2007 17:01 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
>whereas I want the results to be side by side.
So what is stopping YOU from modifying the SQL to actually do what you want?
Re: Problem with FULL OUTER JOIN [message #284669 is a reply to message #284001] Fri, 30 November 2007 08:22 Go to previous message
klkuab
Messages: 25
Registered: November 2007
Junior Member
unsure of how to do that, that's why I posted my question/problem here. i'm a newbie.
Previous Topic: Copying Several Seq. Flat Files into ONE
Next Topic: invalid relational operator - Error
Goto Forum:
  


Current Time: Wed Dec 07 22:37:15 CST 2016

Total time taken to generate the page: 0.22819 seconds