Outer join query help
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 7 of 7

Thread: Outer join query help

  1. #1
    Join Date
    Jan 2001
    Posts
    318
    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

  2. #2
    Join Date
    Sep 2001
    Location
    NJ, USA
    Posts
    1,287
    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.

  3. #3
    Join Date
    Jan 2001
    Posts
    318
    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

  4. #4
    Join Date
    Sep 2001
    Location
    NJ, USA
    Posts
    1,287
    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.

  5. #5
    Join Date
    Jan 2002
    Posts
    3

    Talking

    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

  6. #6
    Join Date
    Jan 2002
    Posts
    3
    Regarding ur other query,
    why not use the decode directly?

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


    ?
    -venu.

  7. #7
    Join Date
    Jan 2001
    Posts
    318
    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
  •  


Click Here to Expand Forum to Full Width