Home » SQL & PL/SQL » SQL & PL/SQL » ORA-01799 (Oracle 10g)
ORA-01799 [message #379845] Thu, 08 January 2009 02:22 Go to next message
Olivia
Messages: 519
Registered: June 2008
Senior Member
I am trying to run the following query on oracle 10g & I endup with an
error.

ORA-01799: a column may not be outer-joined to a subquery

 select  p.product_id,a.object_item_id,
           to_char(oic1.updt_dte, 'mm-dd-yyyy') , 
           oic1.comments
    from object_item_tbl a,
         object_item i,
         product_tbl p,
		 object_item_category c,
         object_item_remarks oic1
    where a.status_cde in ('CLS')
    and a.object_item_id = i.object_item_id
	and i.cat_item_id = c.cat_item_id
    and i.product_request_item_id = p.order_item_id
    and c.CUST_ID = oic1.CUST_ID(+)
    and oic1.updt_dte(+) = nvl((select max(updt_dte) 
					  from object_item_comment oic2
					  where oic1.CUST_ID = oic2.CUST_ID),null)



How can I modify the query.

Regards,
Oli
Re: ORA-01799 [message #379846 is a reply to message #379845] Thu, 08 January 2009 02:28 Go to previous messageGo to next message
dr.s.raghunathan
Messages: 540
Registered: February 2008
Senior Member
is the outer join column is the same data type and length. There is a difference between number and number(16,0)
yours
dr.s.raghunathan
Re: ORA-01799 [message #379849 is a reply to message #379845] Thu, 08 January 2009 02:33 Go to previous messageGo to next message
trivendra
Messages: 208
Registered: October 2007
Location: Noida, India
Senior Member
I hope this can help you

SELECT p.product_id, a.object_item_id, TO_CHAR (oic1.updt_dte, 'mm-dd-yyyy'),
       oic1.comments
  FROM object_item_tbl a,
       object_item i,
       product_tbl p,
       object_item_category c,
       object_item_remarks oic1,
       (SELECT MAX (updt_dte) AS updt_dte
          FROM object_item_comment) oic2
 WHERE a.status_cde = 'CLS'
   AND a.object_item_id = i.object_item_id
   AND i.cat_item_id = c.cat_item_id
   AND i.product_request_item_id = p.order_item_id
   AND c.cust_id = oic1.cust_id(+)
   AND oic1.updt_dte(+) = updt_dte
   AND oic1.cust_id = oic2.cust_id


use it as inline view.

Thanks
Trivendra

[Updated on: Thu, 08 January 2009 02:35]

Report message to a moderator

Re: ORA-01799 [message #379851 is a reply to message #379849] Thu, 08 January 2009 02:35 Go to previous messageGo to next message
tahpush
Messages: 961
Registered: August 2006
Location: Stockholm/Sweden
Senior Member

Also '+' is the "old" syntax for joins
Re: ORA-01799 [message #379852 is a reply to message #379851] Thu, 08 January 2009 02:39 Go to previous messageGo to next message
Littlefoot
Messages: 20901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
tahpush wrote on Thu, 08 January 2009 09:35
Also '+' is the "old" syntax for joins

Outer joins; right?
Re: ORA-01799 [message #379855 is a reply to message #379852] Thu, 08 January 2009 02:44 Go to previous messageGo to next message
tahpush
Messages: 961
Registered: August 2006
Location: Stockholm/Sweden
Senior Member

Littlefoot wrote on Thu, 08 January 2009 09:39
tahpush wrote on Thu, 08 January 2009 09:35
Also '+' is the "old" syntax for joins

Outer joins; right?


Typo Rolling Eyes More Coffee Thumbs Up



Re: ORA-01799 [message #379860 is a reply to message #379849] Thu, 08 January 2009 03:06 Go to previous messageGo to next message
Olivia
Messages: 519
Registered: June 2008
Senior Member
I got this error now: oic2.cust_id invalid identifier
Re: ORA-01799 [message #379864 is a reply to message #379860] Thu, 08 January 2009 03:19 Go to previous messageGo to next message
Littlefoot
Messages: 20901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
In that case, use a valid identifier instead of an invalid one.
Re: ORA-01799 [message #379865 is a reply to message #379849] Thu, 08 January 2009 03:23 Go to previous messageGo to next message
trivendra
Messages: 208
Registered: October 2007
Location: Noida, India
Senior Member
Sorry for this ,

Little update

SELECT p.product_id, a.object_item_id, TO_CHAR (oic1.updt_dte, 'mm-dd-yyyy'),
       oic1.comments
  FROM object_item_tbl a,
       object_item i,
       product_tbl p,
       object_item_category c,
       object_item_remarks oic1,
       (SELECT   MAX (updt_dte) AS updt_dte, cust_id
            FROM object_item_comment
        GROUP BY cust_id) oic2
 WHERE a.status_cde = 'CLS'
   AND a.object_item_id = i.object_item_id
   AND i.cat_item_id = c.cat_item_id
   AND i.product_request_item_id = p.order_item_id
   AND c.cust_id = oic1.cust_id(+)
   AND oic1.updt_dte(+) = updt_dte
   AND oic1.cust_id = oic2.cust_id


Thanks
Trivendra
Re: ORA-01799 [message #379867 is a reply to message #379865] Thu, 08 January 2009 03:25 Go to previous messageGo to next message
Olivia
Messages: 519
Registered: June 2008
Senior Member

Here is the error now I am getting
SQL Error: ORA-01417: a table may be outer joined to at most one other table
Re: ORA-01799 [message #379875 is a reply to message #379845] Thu, 08 January 2009 03:58 Go to previous messageGo to next message
Olivia
Messages: 519
Registered: June 2008
Senior Member
I Created a view...
					  
Create or replace view vw_object_item_comment
as
select max(updt_dte) updt_dte,cust_id 
	from object_item_comment
		group by cust_id;


Modified the query above like this way. Need your advice if I am wrong here!
					  
	select * from (				  
    select  p.product_id,a.object_item_id,
           to_char(oic1.updt_dte, 'mm-dd-yyyy') , 
           oic1.comments,
		   -- Modified, added below two fields in the select query
		   oic1.CUST_ID cust_id,oic1.updt_dte updt_dte
    from object_item_tbl a,
         object_item i,
         product_tbl p,
		 object_item_category c,
         object_item_remarks oic1
    where a.status_cde in ('CLS')
    and a.object_item_id = i.object_item_id
	and i.cat_item_id = c.cat_item_id
    and i.product_request_item_id = p.order_item_id
    and c.CUST_ID = oic1.CUST_ID(+) ) subq, vw_object_item_comment oic2
	where subq.updt_dte(+) = oic.updt_dte
	and oic1.cust_id=oic2.cust_id;
	

but this is just a part of the SELECT UNION.
there is multiple select in the union clause
and upd_tms is based on different subquery condition and in 10g its not supporting.
Regards,
Oli

[Updated on: Thu, 08 January 2009 04:08]

Report message to a moderator

Re: ORA-01799 [message #379878 is a reply to message #379845] Thu, 08 January 2009 04:09 Go to previous messageGo to next message
trivendra
Messages: 208
Registered: October 2007
Location: Noida, India
Senior Member
Use new Joining syntax for
Outer Join.

http://www.oreillynet.com/pub/a/network/2002/04/23/fulljoin.html


Thanks
Trivendra
Re: ORA-01799 [message #379884 is a reply to message #379878] Thu, 08 January 2009 04:25 Go to previous messageGo to next message
Olivia
Messages: 519
Registered: June 2008
Senior Member
But I am still not being able to find a way..Please help!
Re: ORA-01799 [message #379887 is a reply to message #379875] Thu, 08 January 2009 04:28 Go to previous messageGo to next message
Olivia
Messages: 519
Registered: June 2008
Senior Member
Everything is ok other than

where subq.updt_dte(+) = oic.updt_dte


How I can modify ?
Re: ORA-01799 [message #379896 is a reply to message #379845] Thu, 08 January 2009 04:38 Go to previous messageGo to next message
trivendra
Messages: 208
Registered: October 2007
Location: Noida, India
Senior Member
Try with this

SELECT p.product_id, a.object_item_id, TO_CHAR (oic1.updt_dte, 'mm-dd-yyyy'),
       oic1.comments
  FROM object_item_tbl a,
       object_item i,
       product_tbl p,
       object_item_category c,
       (SELECT   MAX (updt_dte) AS updt_dte, cust_id
            FROM object_item_comment
        GROUP BY cust_id) oic2
       RIGHT OUTER JOIN
       object_item_remarks oic1 ON oic1.updt_dte = updt_dte
 WHERE a.status_cde = 'CLS'
   AND a.object_item_id = i.object_item_id
   AND i.cat_item_id = c.cat_item_id
   AND i.product_request_item_id = p.order_item_id
   AND c.cust_id = oic1.cust_id(+)
   AND oic1.cust_id = oic2.cust_id


Cheers !!!
Trivnedra
Re: ORA-01799 [message #379918 is a reply to message #379896] Thu, 08 January 2009 07:15 Go to previous messageGo to next message
Olivia
Messages: 519
Registered: June 2008
Senior Member
Getting below error now:

SQL Error: ORA-25156: old style outer join (+) cannot be used with ANSI joins
25156. 00000 - "old style outer join (+) cannot be used with ANSI joins"
Re: ORA-01799 [message #379921 is a reply to message #379918] Thu, 08 January 2009 07:40 Go to previous messageGo to next message
trivendra
Messages: 208
Registered: October 2007
Location: Noida, India
Senior Member
SELECT p.product_id, a.object_item_id, TO_CHAR (oic1.updt_dte, 'mm-dd-yyyy'),
       oic1.comments
  FROM object_item_tbl a, object_item i, product_tbl p, (SELECT   MAX
                                                                     (updt_dte
                                                                     )
                                                                     AS updt_dte,
                                                                  cust_id
                                                             FROM object_item_comment
                                                         GROUP BY cust_id) oic2
       RIGHT OUTER JOIN
       object_item_remarks oic1 ON oic1.updt_dte = updt_dte
       LEFT OUTER JOIN object_item_category c ON c.cust_id = oic1.cust_id
 WHERE a.status_cde = 'CLS'
   AND a.object_item_id = i.object_item_id
   AND i.cat_item_id = c.cat_item_id
   AND i.product_request_item_id = p.order_item_id
   AND oic1.cust_id = oic2.cust_id



I guess this can help....
Re: ORA-01799 [message #379928 is a reply to message #379867] Thu, 08 January 2009 08:24 Go to previous message
flyboy
Messages: 1832
Registered: November 2006
Senior Member
Olivia wrote on Thu, 08 January 2009 10:25

Here is the error now I am getting
SQL Error: ORA-01417: a table may be outer joined to at most one other table

This is logical error - so you shall re-think, what you want to get and whether it makes sense.

Now, you LEFT JOIN C and OIC2 to OIC1.
You take all rows from C and map rows in OIC2; if there is no corresponding row, fill OIC2 columns with NULLs.
You take all rows from OIC1 and map rows in OIC2; if there is no corresponding row, fill OIC2 columns with NULLs.
The question is: how to map (join) C and OIC1 rows with NULL values in OIC2 (mapping table) columns?
Previous Topic: Building Strings (merged)
Next Topic: SQL failing when the language is NL
Goto Forum:
  


Current Time: Sat Dec 10 16:28:33 CST 2016

Total time taken to generate the page: 0.08909 seconds