| ORA-01799 [message #379845] |
Thu, 08 January 2009 02:22  |
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 #379849 is a reply to message #379845] |
Thu, 08 January 2009 02:33  |
trivendra
Messages: 211
Registered: October 2007
Location: Phoenix
|
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 #379865 is a reply to message #379849] |
Thu, 08 January 2009 03:23 |
trivendra
Messages: 211
Registered: October 2007
Location: Phoenix
|
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 |
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 |
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 #379896 is a reply to message #379845] |
Thu, 08 January 2009 04:38 |
trivendra
Messages: 211
Registered: October 2007
Location: Phoenix
|
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 |
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 |
trivendra
Messages: 211
Registered: October 2007
Location: Phoenix
|
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 |
flyboy
Messages: 1903
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?
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
More Coffee 