Home » SQL & PL/SQL » SQL & PL/SQL » full outer join (merged) (8i, xp)
full outer join (merged) [message #425453] Fri, 09 October 2009 03:44 Go to next message
rzkhan
Messages: 345
Registered: March 2005
Senior Member
how can I get
rows from A that don't have corresponding rows in B 
rows from B that don't have corresponding rows in A 

using group by in query;



I need my result like this (see 09-OCT-2009)

DATE          SUM(A.AMOUNT)   Sum(B.AMOUNT)   
--------- ---------------------------------
26-SEP-09             3000
29-SEP-09           166345
30-SEP-09           225900
01-OCT-09           252401
09-OCT-09              500        12345678
29-DEC-09            10630
30-DEC-09            28380






I tried full outer join like below, but no success.

MESS@orcl SQL>ed
Wrote file afiedt.buf

  1  select recd_date,sum(recd_amount)
  2  from recd
  3  where recd_date>'19-SEP-2009'
  4  group by recd_date
  5  UNION
  6  select payment_date,sum(amount)
  7  from payments
  8* group by payment_date
MESS@orcl SQL>/

RECD_DATE SUM(RECD_AMOUNT)
--------- ----------------
26-SEP-09             3000
29-SEP-09           166345
30-SEP-09           225900
01-OCT-09           252401
09-OCT-09              500
09-OCT-09         12345678
29-DEC-09            10630
30-DEC-09            28380

8 rows selected.

MESS@orcl SQL>


any idea?

Riaz
Re: full outer join [message #425460 is a reply to message #425453] Fri, 09 October 2009 03:54 Go to previous messageGo to next message
rzkhan
Messages: 345
Registered: March 2005
Senior Member
I can solve this using 10g, as below
MESS@orcl SQL>ed
Wrote file afiedt.buf

  1  select recd_date,sum(recd_amount)as a,payment_date,sum(payments.amount) as b
  2  from recd full outer join payments
  3  on recd_date=payment_date
  4  where recd_date>'19-SEP-2009'
  5* group by recd_date,payment_date
MESS@orcl SQL>/

RECD_DATE          A PAYMENT_D          B
--------- ---------- --------- ----------
01-OCT-09     252401
30-SEP-09     225900
30-DEC-09      28380
26-SEP-09       3000
09-OCT-09        500 09-OCT-09   12345678
29-SEP-09     166345
29-DEC-09      10630

7 rows selected.

MESS@orcl SQL>



But I want to do it in 8i.


please

Riaz
Re: full outer join [message #425462 is a reply to message #425453] Fri, 09 October 2009 03:56 Go to previous messageGo to next message
Michel Cadot
Messages: 64140
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
FULL OUTER JOIN does not exist in 8i, you have to simulate it with:
SELECT T1.c, T2.d
FROM T1 FULL OUTER JOIN T2
ON T1.x = T2.y;

has to written as:
SELECT T1.c, T2.d
FROM T1, T2
WHERE T1.x = T2.y (+)
UNION ALL
SELECT NULL, T2.d
FROM T2
WHERE NOT EXISTS
(SELECT 1 FROM T1 WHERE T1.x = T2.y);

Regards
Michel
Re: full outer join [message #425463 is a reply to message #425460] Fri, 09 October 2009 04:01 Go to previous messageGo to next message
Michel Cadot
Messages: 64140
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
'19-SEP-2009' is NOT a date it is a string and '20-JAN-1000' is greater than '19-SEP-2009'.

Regards
Michel
Re: full outer join [message #425464 is a reply to message #425462] Fri, 09 October 2009 04:07 Go to previous messageGo to next message
rzkhan
Messages: 345
Registered: March 2005
Senior Member
thanks for correct use of data type.

but will this (8i) approach work on 10g. ?

here is cant.

MESS@orcl SQL>ed
Wrote file afiedt.buf

  1  SELECT recd_date,sum(recd_amount), sum(amount)
  2  FROM recd, payments
  3  WHERE recd.recd_date = payments.payment_date (+)
  4  and recd_date>19-SEP-2009
  5  group by recd_date
  6  UNION ALL
  7  select null,null,sum(payments.amount)
  8  from payments
  9  group by payment_date
 10  where not EXISTS
 11* (select 1 from recd where recd.recd_date=payments.payment_date)
MESS@orcl SQL>/
where not EXISTS
*
ERROR at line 10:
ORA-00933: SQL command not properly ended


MESS@orcl SQL>

[Updated on: Fri, 09 October 2009 04:10]

Report message to a moderator

Re: full outer join [message #425471 is a reply to message #425464] Fri, 09 October 2009 04:50 Go to previous messageGo to next message
Michel Cadot
Messages: 64140
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Generally speaking WHERE must be before GROUP but I think you don't want to group on each query but on the result of the union, doesn't it?

Regards
Michel
Re: full outer join [message #425473 is a reply to message #425471] Fri, 09 October 2009 04:57 Go to previous messageGo to next message
rzkhan
Messages: 345
Registered: March 2005
Senior Member
I want to group each query result first.
Re: full outer join [message #425475 is a reply to message #425473] Fri, 09 October 2009 05:05 Go to previous messageGo to next message
Michel Cadot
Messages: 64140
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
So it is not the same thing than grouping a full outer join query.

Regards
Michel
Re: full outer join [message #425479 is a reply to message #425475] Fri, 09 October 2009 05:18 Go to previous messageGo to next message
rzkhan
Messages: 345
Registered: March 2005
Senior Member
group the data first by date in each query, then group the result of both queries again by date. if I am not wrong.


like


MESS@orcl SQL>ed
Wrote file afiedt.buf

  1  select recd_date,sum(recd_amount)as a,payment_date,sum(payments.amount) as b
  2  from recd full outer join payments
  3  on recd_date=payment_date
  4  where recd_date>'19-SEP-2009'
  5* group by recd_date,payment_date

MESS@orcl SQL>/


RECD_DATE          A PAYMENT_D          B
--------- ---------- --------- ----------
01-OCT-09     252401
30-SEP-09     225900
30-DEC-09      28380
26-SEP-09       3000
09-OCT-09        500 09-OCT-09   12345678
29-SEP-09     166345
29-DEC-09      10630


[Updated on: Fri, 09 October 2009 05:19]

Report message to a moderator

Re: full outer join [message #425485 is a reply to message #425479] Fri, 09 October 2009 05:28 Go to previous messageGo to next message
Michel Cadot
Messages: 64140
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I don't know, I have neither your table nor your data nor the result you expect.

Post a working Test case: create table and insert statements along with the result you want with these data.

Regards
Michel
Re: full outer join [message #425493 is a reply to message #425485] Fri, 09 October 2009 05:50 Go to previous messageGo to next message
rzkhan
Messages: 345
Registered: March 2005
Senior Member
test case



create table recd(
recd_date DATE,
recd_amount number(8)
)


create table payments(
payment_date DATE,
amount number(8)
)
/




insert into recd values('01-OCT-2009',252400);
insert into recd values('01-OCT-2009',1);
insert into recd values('09-OCT-2009',500);
insert into payments values('09-OCT-2009',12345678);
insert into payments values('08-OCT-2009',678);



  1  select recd_date,sum(recd_amount)as a,payment_date,sum(payments.amount) as b
  2    from recd full outer join payments
  3    on recd_date=payment_date
  4*  group by recd_date,payment_date
SCOTT@orcl SQL>/

RECD_DATE          A PAYMENT_D          B
--------- ---------- --------- ----------
01-OCT-09     252401
09-OCT-09        500 09-OCT-09   12345678
                     08-OCT-09        678

SCOTT@orcl SQL>




Re: full outer join [message #425519 is a reply to message #425493] Fri, 09 October 2009 06:59 Go to previous messageGo to next message
Prajakta001
Messages: 52
Registered: June 2008
Location: Mumbai
Member

Will this work?

SELECT   recd_date, SUM (recd_amount) AS a, payment_date,
         SUM (payments.amount) AS b
    FROM recd, payments
   WHERE recd_date = payment_date(+)
GROUP BY recd_date, payment_date
UNION
SELECT   recd_date, SUM (recd_amount) AS a, payment_date,
         SUM (payments.amount) AS b
    FROM recd, payments
   WHERE recd_date(+) = payment_date
GROUP BY recd_date, payment_date


Regards
Prajakta
Re: full outer join [message #425529 is a reply to message #425464] Fri, 09 October 2009 07:53 Go to previous messageGo to next message
joy_division
Messages: 4643
Registered: February 2005
Location: East Coast USA
Senior Member
rzkhan wrote on Fri, 09 October 2009 05:07
thanks for correct use of data type.


then...

Quote:

4 and recd_date>19-SEP-2009


That's pretty funny.
Re: full outer join [message #425695 is a reply to message #425519] Sun, 11 October 2009 22:23 Go to previous messageGo to next message
rzkhan
Messages: 345
Registered: March 2005
Senior Member
@ Prajakta

Yes, it does work fine in 10g.
But I am behid a query for 8i.. If some can can guide me.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Production
With the Partitioning, OLAP and Data Mining options

SCOTT@orcl SQL>SELECT   recd_date, SUM (recd_amount) AS a, payment_date,
  2           SUM (payments.amount) AS b
  3      FROM recd, payments
  4     WHERE recd_date = payment_date(+)
  5  GROUP BY recd_date, payment_date
  6  UNION
  7  SELECT   recd_date, SUM (recd_amount) AS a, payment_date,
  8           SUM (payments.amount) AS b
  9      FROM recd, payments
 10     WHERE recd_date(+) = payment_date
 11  GROUP BY recd_date, payment_date
 12  /

RECD_DATE          A PAYMENT_D          B
--------- ---------- --------- ----------
01-OCT-09     252401
09-OCT-09        500 09-OCT-09   12345678
                     08-OCT-09        678

SCOTT@orcl SQL>

Riaz



@ joy_division

I am not concerned about this problem. I sought help in main question. anyhow ...thanks for pointing it out..

Riaz

[Updated on: Mon, 12 October 2009 01:15]

Report message to a moderator

Re: full outer join [message #425750 is a reply to message #425695] Mon, 12 October 2009 01:49 Go to previous messageGo to next message
ayush_anand
Messages: 417
Registered: November 2008
Senior Member
As Michel said you have to simulate a full outer join
  1   select recd_date,sum(recd_amount)as a,payment_date,sum(payments.amount) as b
  2  from recd,payments
  3  where recd.recd_date = payments.payment_date(+)
  4  group by recd_date,payment_date
  5  UNION
  6  select null,null,payment_date,sum(p.amount) as b
  7  from payments p
  8  WHERE NOT EXISTS
  9  (
 10  select 1 from recd r where r.recd_date = p.payment_date
 11  )
 12* group by payment_date
SQL> /

RECD_DATE          A PAYMENT_D          B
--------- ---------- --------- ----------
01-OCT-09     252401
09-OCT-09        500 09-OCT-09   12345678
                     08-OCT-09        678


you can use a union of both right and left outer join also as shown by Prajakta
as Union does an implicit distinct
Re: full outer join [message #425757 is a reply to message #425750] Mon, 12 October 2009 02:16 Go to previous messageGo to next message
rzkhan
Messages: 345
Registered: March 2005
Senior Member
But 08-Oct-2009 should be before 09-Oct-2009. I mean all the output should be ORDER BY (recd_Date,payment_date) which ever is earlier........


Riaz



Re: full outer join [message #425759 is a reply to message #425757] Mon, 12 October 2009 02:20 Go to previous messageGo to next message
Michel Cadot
Messages: 64140
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
So add it.
Any query you use, if you want a specific order you have to add an ORDER BY clause.

Regards
Michel

[Updated on: Mon, 12 October 2009 02:21]

Report message to a moderator

Re: full outer join [message #425773 is a reply to message #425757] Mon, 12 October 2009 03:03 Go to previous messageGo to next message
rzkhan
Messages: 345
Registered: March 2005
Senior Member
ORDER BY clause does not effect the result.


  1     select recd_date,sum(recd_amount)as a,payment_date,sum(payments.amount) as b
  2      from recd,payments
  3      where recd.recd_date = payments.payment_date(+)
  4      group by recd_date,payment_date
  5      UNION
  6      select null,null,payment_date,sum(p.amount) as b
  7      from payments p
  8      WHERE NOT EXISTS
  9      (
 10     select 1 from recd r where r.recd_date = p.payment_date
 11     )
 12  group by payment_date
 13* order by recd_date,payment_date
SCOTT@orcl SQL>/

RECD_DATE          A PAYMENT_D          B
--------- ---------- --------- ----------
01-OCT-09     252401
09-OCT-09        500 09-OCT-09   12345678
                     08-OCT-09        678




Riaz

Re: full outer join [message #425780 is a reply to message #425773] Mon, 12 October 2009 03:24 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Assuming the results that you're looking for are:
RECD_DATE          A PAYMENT_D          B
--------- ---------- --------- ----------
01-OCT-09     252401
                     08-OCT-09        678
09-OCT-09        500 09-OCT-09   12345678
(I say assuming because you haven't been too clear about your actal requirement) I think what you need is
ORDER BY least (nvl(recd_date,to_date('31-12-2999','dd-mm-yyyy') ,nvl(payment_date,to_date('31-12-2999','dd-mm-yyyy'))
Re: full outer join [message #425781 is a reply to message #425773] Mon, 12 October 2009 03:24 Go to previous messageGo to next message
ayush_anand
Messages: 417
Registered: November 2008
Senior Member
Quote:
order by recd_date,payment_date

this means order by recd_date first
then within same recd_date order by payment_date

Re: full outer join [message #425784 is a reply to message #425780] Mon, 12 October 2009 03:34 Go to previous messageGo to next message
rzkhan
Messages: 345
Registered: March 2005
Senior Member
exacty same is my requirement. sorry for (my English)I couldnt describe it clearly.

But it still shows an error...

Wrote file afiedt.buf

  1     select recd_date,sum(recd_amount)as a,payment_date,sum(payments.amount) as b
  2      from recd,payments
  3      where recd.recd_date = payments.payment_date(+)
  4      group by recd_date,payment_date
  5          UNION
  6      select null,null,payment_date,sum(p.amount) as b
  7      from payments p
  8      WHERE NOT EXISTS
  9      (
 10     select 1 from recd r where r.recd_date = p.payment_date
 11  )
 12  group by payment_date
 13  ORDER BY least (
 14  nvl(recd_date,to_date('31-12-2999','dd-mm-yyyy'),
 15  nvl(payment_date,to_date('31-12-2999','dd-mm-yyyy')
 16* )
 17  /
nvl(recd_date,to_date('31-12-2999','dd-mm-yyyy'),
*
ERROR at line 14:
ORA-00909: invalid number of arguments



Riaz

[Updated on: Mon, 12 October 2009 03:35]

Report message to a moderator

Re: full outer join [message #425786 is a reply to message #425784] Mon, 12 October 2009 03:42 Go to previous messageGo to next message
Michel Cadot
Messages: 64140
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
It seems you are unable to copy what has been posted.

Regards
Michel
Re: full outer join [message #425787 is a reply to message #425786] Mon, 12 October 2009 03:44 Go to previous messageGo to next message
ayush_anand
Messages: 417
Registered: November 2008
Senior Member
I was able to copy paste but getting another error

SQL> ed
Wrote file afiedt.buf

  1    select recd_date,sum(recd_amount)as a,payment_date,sum(payments.amount) as b
  2   from recd,payments
  3   where recd.recd_date = payments.payment_date(+)
  4   group by recd_date,payment_date
  5   UNION
  6    select recd_date,sum(recd_amount)as a,payment_date,sum(payments.amount) as b
  7   from recd,payments
  8   where recd.recd_date(+) = payments.payment_date
  9   group by recd_date,payment_date
 10  ORDER BY least ( nvl(recd_date,to_date('31-12-2999','dd-mm-yyyy'))
 11* ,nvl(payment_date,to_date('31-12-2999','dd-mm-yyyy')))
SQL> /
ORDER BY least ( nvl(recd_date,to_date('31-12-2999','dd-mm-yyyy'))
         *
ERROR at line 10:
ORA-01785: ORDER BY item must be the number of a SELECT-list expression
Re: full outer join (merged) [message #425788 is a reply to message #425453] Mon, 12 October 2009 03:47 Go to previous messageGo to next message
cookiemonster
Messages: 12422
Registered: September 2008
Location: Rainy Manchester
Senior Member
Actually he has copied what has been posted. Seems the original is missing a couple of brackets.
Should be:
ORDER BY least (nvl(recd_date,to_date('31-12-2999','dd-mm-yyyy')), nvl(payment_date,to_date('31-12-2999','dd-mm-yyyy')))
Re: full outer join [message #425789 is a reply to message #425787] Mon, 12 October 2009 03:49 Go to previous messageGo to next message
ayush_anand
Messages: 417
Registered: November 2008
Senior Member
But able to do by this
  select recd_date,sum(recd_amount)as a,payment_date,sum(payments.amount) as b,least ( nvl(recd
,nvl(payment_date,to_date('31-12-2999','dd-mm-yyyy')))
 from recd,payments
 where recd.recd_date = payments.payment_date(+)
 group by recd_date,payment_date
 UNION
  select recd_date,sum(recd_amount)as a,payment_date,sum(payments.amount) as b,least ( nvl(recd
,nvl(payment_date,to_date('31-12-2999','dd-mm-yyyy')))
 from recd,payments
 where recd.recd_date(+) = payments.payment_date
 group by recd_date,payment_date
order by 5
Re: full outer join [message #425790 is a reply to message #425789] Mon, 12 October 2009 04:00 Go to previous messageGo to next message
rzkhan
Messages: 345
Registered: March 2005
Senior Member
I may not be so expert in copying--paste
still it throws error here...

SCOTT@orcl SQL>ed
Wrote file afiedt.buf

  1  select recd_date,sum(recd_amount)as a,
  2  payment_date,sum(payments.amount) as b,
  3  least ( nvl(recd,nvl(payment_date,to_date('31-12-2999','dd-mm-yyyy')))
  4  from recd,payments
  5  where recd.recd_date = payments.payment_date(+)
  6  group by recd_date,payment_date
  7   UNION
  8  select recd_date,sum(recd_amount)as a,
  9  payment_date,sum(payments.amount) as b,
 10  least ( nvl(recd,nvl(payment_date,to_date('31-12-2999','dd-mm-yyyy')))
 11  from recd,payments
 12  where recd.recd_date(+) = payments.payment_date
 13  group by recd_date,payment_date
 14* order by 5
SCOTT@orcl SQL>/
from recd,payments
*
ERROR at line 4:
ORA-00907: missing right parenthesis


SCOTT@orcl SQL>
Re: full outer join [message #425791 is a reply to message #425790] Mon, 12 October 2009 04:03 Go to previous messageGo to next message
ayush_anand
Messages: 417
Registered: November 2008
Senior Member
One works one does not .
Can anyone please explain why? or point to doc
SQL> select *
  2  from
  3  (
  4   select recd_date,sum(recd_amount)as a,payment_date,sum(payments.amount) as b
  5   from recd,payments
  6   where recd.recd_date = payments.payment_date(+)
  7   group by recd_date,payment_date
  8   UNION
  9    select recd_date,sum(recd_amount)as a,payment_date,sum(payments.amount) as b
 10   from recd,payments
 11   where recd.recd_date(+) = payments.payment_date
 12   group by recd_date,payment_date
 13  )
 14  ORDER BY least (nvl(recd_date,to_date('31-12-2999','dd-mm-yyyy')), nvl(payment_date,to_date('31
-12-2999','dd-mm-yyyy')))
 15  /

RECD_DATE          A PAYMENT_D          B
--------- ---------- --------- ----------
01-OCT-09     252401
                     08-OCT-09        678
09-OCT-09        500 09-OCT-09   12345678

SQL> ed
Wrote file afiedt.buf

  1   select recd_date,sum(recd_amount)as a,payment_date,sum(payments.amount) as b
  2   from recd,payments
  3   where recd.recd_date = payments.payment_date(+)
  4   group by recd_date,payment_date
  5   UNION
  6    select recd_date,sum(recd_amount)as a,payment_date,sum(payments.amount) as b
  7   from recd,payments
  8   where recd.recd_date(+) = payments.payment_date
  9   group by recd_date,payment_date
 10* ORDER BY least (nvl(recd_date,to_date('31-12-2999','dd-mm-yyyy')), nvl(payment_date,to_date('31
 11  /
ORDER BY least (nvl(recd_date,to_date('31-12-2999','dd-mm-yyyy')), nvl(payment_date,to_date('31-12-2
         *
ERROR at line 10:
ORA-01785: ORDER BY item must be the number of a SELECT-list expression
Re: full outer join [message #425792 is a reply to message #425790] Mon, 12 October 2009 04:09 Go to previous messageGo to next message
cookiemonster
Messages: 12422
Registered: September 2008
Location: Rainy Manchester
Senior Member
rzkhan wrote on Mon, 12 October 2009 10:00
I may not be so expert in copying--paste
still it throws error here...

SCOTT@orcl SQL>ed
Wrote file afiedt.buf

  1  select recd_date,sum(recd_amount)as a,
  2  payment_date,sum(payments.amount) as b,
  3  least ( nvl(recd,nvl(payment_date,to_date('31-12-2999','dd-mm-yyyy')))
  4  from recd,payments
  5  where recd.recd_date = payments.payment_date(+)
  6  group by recd_date,payment_date
  7   UNION
  8  select recd_date,sum(recd_amount)as a,
  9  payment_date,sum(payments.amount) as b,
 10  least ( nvl(recd,nvl(payment_date,to_date('31-12-2999','dd-mm-yyyy')))
 11  from recd,payments
 12  where recd.recd_date(+) = payments.payment_date
 13  group by recd_date,payment_date
 14* order by 5
SCOTT@orcl SQL>/
from recd,payments
*
ERROR at line 4:
ORA-00907: missing right parenthesis


SCOTT@orcl SQL>


ayush_anand's example was incomplete.
This:
least ( nvl(recd,nvl(payment_date,to_date('31-12-2999','dd-mm-yyyy')))

Needs to be the equivalent of this:
ORDER BY least (nvl(recd_date,to_date('31-12-2999','dd-mm-yyyy')), nvl(payment_date,to_date('31-12-2999','dd-mm-yyyy')))


You're missing a to_date and some brackets.
Re: full outer join [message #425793 is a reply to message #425792] Mon, 12 October 2009 04:11 Go to previous messageGo to next message
ayush_anand
Messages: 417
Registered: November 2008
Senior Member
@ cookiemonster

Quote:
ayush_anand's example was incomplete.

because of sql plus truncating my edit
Sorry

Please look into my last post .Can you explain this behaviour

[Updated on: Mon, 12 October 2009 04:36]

Report message to a moderator

Re: full outer join [message #425815 is a reply to message #425793] Mon, 12 October 2009 06:00 Go to previous messageGo to next message
rzkhan
Messages: 345
Registered: March 2005
Senior Member
It is working.......Thank you all..



SCOTT@orcl SQL>ed
Wrote file afiedt.buf

  1  select * from(
  2     select recd_date,sum(recd_amount)as a,payment_date,sum(payments.amount) as b
  3       from recd,payments
  4       where recd.recd_date = payments.payment_date(+)
  5       AND recd_date='01-OCT-2009'
  6       group by recd_date,payment_date
  7       UNION
  8        select recd_date,sum(recd_amount)as a,payment_date,sum(payments.amount) as b
  9      from recd,payments
 10      where recd.recd_date(+) = payments.payment_date
 11      group by recd_date,payment_date
 12     )
 13  ORDER BY least (nvl(recd_date,to_date('31-12-2999','dd-mm-yyyy')),
 14* nvl(payment_date,to_date('31-12-2999','dd-mm-yyyy')))
SCOTT@orcl SQL>/

RECD_DATE          A PAYMENT_D          B
--------- ---------- --------- ----------
01-OCT-09     252401
                     08-OCT-09        678
09-OCT-09        500 09-OCT-09   12345678


Riaz
Re: full outer join [message #425846 is a reply to message #425791] Mon, 12 October 2009 08:18 Go to previous messageGo to next message
cookiemonster
Messages: 12422
Registered: September 2008
Location: Rainy Manchester
Senior Member
ayush_anand wrote on Mon, 12 October 2009 10:03
One works one does not .
Can anyone please explain why? or point to doc
SQL> select *
  2  from
  3  (
  4   select recd_date,sum(recd_amount)as a,payment_date,sum(payments.amount) as b
  5   from recd,payments
  6   where recd.recd_date = payments.payment_date(+)
  7   group by recd_date,payment_date
  8   UNION
  9    select recd_date,sum(recd_amount)as a,payment_date,sum(payments.amount) as b
 10   from recd,payments
 11   where recd.recd_date(+) = payments.payment_date
 12   group by recd_date,payment_date
 13  )
 14  ORDER BY least (nvl(recd_date,to_date('31-12-2999','dd-mm-yyyy')), nvl(payment_date,to_date('31
-12-2999','dd-mm-yyyy')))
 15  /

RECD_DATE          A PAYMENT_D          B
--------- ---------- --------- ----------
01-OCT-09     252401
                     08-OCT-09        678
09-OCT-09        500 09-OCT-09   12345678

SQL> ed
Wrote file afiedt.buf

  1   select recd_date,sum(recd_amount)as a,payment_date,sum(payments.amount) as b
  2   from recd,payments
  3   where recd.recd_date = payments.payment_date(+)
  4   group by recd_date,payment_date
  5   UNION
  6    select recd_date,sum(recd_amount)as a,payment_date,sum(payments.amount) as b
  7   from recd,payments
  8   where recd.recd_date(+) = payments.payment_date
  9   group by recd_date,payment_date
 10* ORDER BY least (nvl(recd_date,to_date('31-12-2999','dd-mm-yyyy')), nvl(payment_date,to_date('31
 11  /
ORDER BY least (nvl(recd_date,to_date('31-12-2999','dd-mm-yyyy')), nvl(payment_date,to_date('31-12-2
         *
ERROR at line 10:
ORA-01785: ORDER BY item must be the number of a SELECT-list expression


When I first saw this I thought it was a bug, but I get the same behaviour and a quick google makes it seem like this is expected behaviour.

I can't find anything in the docs that explicitly states this should happen. Closest I can find is this:

If the select list preceding the set operator contains an expression, then you must provide a column alias for the expression in order to refer to it in the order_by_clause.

From here

I think the documentation needs updating.

icon14.gif  Re: full outer join [message #425847 is a reply to message #425846] Mon, 12 October 2009 08:25 Go to previous message
ayush_anand
Messages: 417
Registered: November 2008
Senior Member
Thanks Cookiemonster.
That answers my question.
Previous Topic: similar plsql function to BigDecimal java function
Next Topic: converting unknown amount of rows to columns
Goto Forum:
  


Current Time: Fri Dec 09 06:13:17 CST 2016

Total time taken to generate the page: 0.13737 seconds