Outer join query help

[sonaliak]

Whats wrong with my query ?

SQLWKS> SELECT Exp_Work_ID AS Work_ID, Exp_Res_ID AS Res_ID, Exp_Role_ID AS Role_ID, Exp_Category AS MatCat_ID,
2> to_date(to_char(Exp_Post_Date, 'yyyymmdd'), 'yyyymmdd') AS Exp_Date,
3> mwebWork.Work_Int_Ext8 AS Company_ID,
4> Exp_reported_amount*NVL(currency.fx_rate,NVL(null_curr_date.fx_rate,1)) as Expense_Amount,
5> DECODE(Exp_Type, 10, 0 ,20, Exp_reported_amount*NVL(currency.fx_rate,NVL(null_curr_date.fx_rate,1))) as Billed_Expense
6> FROM mwebExpense, mwebwork , mwebFXRate Currency, mwebFXRate Null_Curr_Date
7> WHERE mwebExpense.Exp_Work_ID = mwebWork.Work_ID
8> AND mwebWork.Work_Currency = Currency.FX_Currency_ID (+)
9> AND mwebExpense.Exp_Post_Date >= Currency.FX_Start_Date (+)
10> AND mwebExpense.Exp_Post_Date <= Currency.FX_Finish_Date (+)
11> AND mwebWork.Work_Currency = Null_Curr_Date.FX_Currency_ID (+)
12> AND Null_Curr_Date.FX_Start_Date IS NULL
13> AND Null_Curr_Date.FX_Finish_Date IS NULL ;
AND mwebWork.Work_Currency = Currency.FX_Currency_ID (+)
*
ORA-01417: a table may be outer joined to at most one other table


Any help please, I want to show records from mwebexpense, mwebwork even if now rows returned by mwebFXRate Currency, mwebFXRate Null_Curr_Date.

Also, in the following query I am trying to get that DECODE to work -

SELECT DECODE(1, (sign(mwebRes.Res_Entity_Type - 3) and sign(mwebRes.Res_Int_Ext7-10)), ('Mgr ' + Manager.Res_Name), ' ') Manager_Name
........
........

Just like this case statement -

Select
CASE WHEN mwebRes.Res_Entity_Type = 3
AND mwebRes.Res_Int_Ext7 <> 10 THEN ('Mgr ' + Manager.Res_Name)
ELSE ' ' END as Manager_Name

Any help, please

Thanks


[Edited by sonaliak on 01-14-2002 at 03:22 PM]

[Shestakov]

I tried to undestand what are you want ?
This is model of your select.
Oracle can't execute querys like this.


!-------------! ..(+) ..... !-----------! .(+) ..... !----------------!
! mwebExpense......! -----> ! Currency .....! -----> ! Null_Curr_Date ....!
!-------------! ............. !-----------! .......... !----------------!
.........! ............................................ !
........V .......................................... ! (+)
!-----------! ..................... !
! mwebwork .......!-------------
!-----------!

You, in my mind, should divide this query to set of queries and use UNION(MINES) for get result.
This is only 1 way to get result.

[sonaliak]

1. I have simplified this query for this forum, I tried union it does not work... Is there any way to do this query in Oracle 8.0.6 ?


SELECT mwebMatrix.Mat_Work_ID, mwebMatrix.Mat_Date, Currency.FX_Start_Date,
Currency.FX_Finish_Date, Null_Curr_Date.FX_Currency_ID,Currency.FX_Currency_ID
FROM mwebMatrix , mwebFXRate Currency, mwebWork Work_Link ,
mwebFXRate Null_Curr_Date
WHERE mwebMatrix.Mat_Work_ID = Work_Link.Work_ID
AND Work_Link.Work_Currency = Currency.FX_Currency_ID (+)
AND mwebMatrix.Mat_Date >= Currency.FX_Start_Date (+)
AND mwebMatrix.Mat_Date <= Currency.FX_Finish_Date (+)
AND Work_Link.Work_Currency = Null_Curr_Date.FX_Currency_ID (+)
AND Null_Curr_Date.FX_Start_Date IS NULL
AND Null_Curr_Date.FX_Finish_Date IS NULL
AND mwebMatrix.Mat_Category < 4000;


2. Also, in the following query I am trying to get that DECODE to work -

SELECT DECODE(1, (sign(mwebRes.Res_Entity_Type - 3) and sign(mwebRes.Res_Int_Ext7-10)), ('Mgr ' + Manager.Res_Name), ' ') Manager_Name
........
........

Just like this case statement -

Select
CASE WHEN mwebRes.Res_Entity_Type = 3
AND mwebRes.Res_Int_Ext7 <> 10 THEN ('Mgr ' + Manager.Res_Name)
ELSE ' ' END as Manager_Name


Any help please...

Thanks
Sonali

[Edited by sonaliak on 01-15-2002 at 02:10 PM]

[Shestakov]

One possible way for use :

1. create view
v_currency
Currency -(+)-> Null_Curr_Date

2. write select with union

mwebwork --> v_Currency
and
mwebExpense --> v_Currency
union
mwebExpense -(+)-> v_Currency
(where not exists mwebwork --> v_Currency)
union
mwebwork -(+)-> v_Currency
(where not exists mwebExpense --> v_Currency)

may this information will be helpfull.

[vgade]

Hi,
I don't have those tables, so couldn't run the script.
but try to change
AND mwebMatrix.Mat_Date >= Currency.FX_Start_Date (+)
AND mwebMatrix.Mat_Date <= Currency.FX_Finish_Date (+)

to AND mwebMatrix.Mat_Date between
nvl(Currency.FX_Start_Date ,mwebMatrix.Mat_Date )
and nvl(FX_Finish_Date ,mwebMatrix.Mat_Date )

u can use the same creteria in <= or >= conditions?

try this and see if it helps u!!
-venu

[vgade]

Regarding ur other query,
why not use the decode directly?

decode(A,3,decode(B,10,null,'mgr'||C))


?
-venu.

[sonaliak]

thanks
DECODE works like a charm,

but the outer join query takes too long.

Atleast it works, thanks again. I will also try UNion query to see if it is any faster.

Sonali