-
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]
Sonali
-
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.
-
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]
Sonali
-
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.
-
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
-
Regarding ur other query,
why not use the decode directly?
decode(A,3,decode(B,10,null,'mgr'||C))
?
-venu.
-
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
Sonali
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|