CODEWITHSUNDEEP
sqloracle-databasejoin
hsuk
hsuk

Reputation: 6870

FULL OUTER JOIN in oracle

I just noticed FULL OUTER JOIN is not working in Oracle.

Other queries work fine but when I fire the query with Full outer join, it takes time and gets disconnected throwing the error.

ORA-03113: end-of-file on communication channel

RIGHT OUTER JOIN and LEFT OUTER JOIN works fine.

What can be alternative for the query below where I am using full outer join to get all records ?

select * from 
(
    select 
    FTM_OFFICE_ID,
    NVL(SUM(FTD_NRS_AMOUNT),0) as TOTAL 
    from FMS_TRANS_DTL 
    inner join FMS_TRANS_MST ON FMS_TRANS_MST.FTM_TRANS_MST_ID = FMS_TRANS_DTL.FTD_TRANS_MST_ID 
    inner join FMS_FC_VOUCHER_CONFIG ON FMS_FC_VOUCHER_CONFIG.FFVC_VOUCHER_ID = FMS_TRANS_MST.FTM_VOUCHER_ID 
    where FFVC_ACCOUNT_TYPE = 3 and FTM_FISCAL_YEAR='2066/67'
    and FTD_ACC_ID in (select distinct FDP_DHARAUTI_C_ACC_ID from FMS_DHARAUTI_PARAMETER where FDP_FISCAL_YEAR='2066/67' )
    /*and FTD_ACC_ID in (591)*/
    group by FTM_OFFICE_ID 
) T1
full outer join
(
    select 
    FTM_OFFICE_ID,
    NVL(SUM(FTD_NRS_AMOUNT),0) as TOTAL 
    from FMS_TRANS_DTL 
    inner join FMS_TRANS_MST ON FMS_TRANS_MST.FTM_TRANS_MST_ID = FMS_TRANS_DTL.FTD_TRANS_MST_ID 
    inner join FMS_FC_VOUCHER_CONFIG ON FMS_FC_VOUCHER_CONFIG.FFVC_VOUCHER_ID = FMS_TRANS_MST.FTM_VOUCHER_ID 
    where FFVC_ACCOUNT_TYPE = 3 and FTM_FISCAL_YEAR='2066/67'
    /*and FTD_ACC_ID in (592)*/
    and FTD_ACC_ID in 
    (select distinct FDP_DHARAUTI_L_ACC_ID from FMS_DHARAUTI_PARAMETER where FDP_FISCAL_YEAR='2066/67' )
    group by FTM_OFFICE_ID 
) T2
on T1.FTM_OFFICE_ID=T2.FTM_OFFICE_ID
/*
 The no. of rows that T1 can have can be different that no. of rows T2 can have. 
 Its not necessary any OFFICE_ID must have amount under any FTD_ACC_ID.
*/

Just noticed that if I remove the sub query in condition for FTD_ACC_ID, the query runs perfectly. Why ?

Upvotes: 1

Views: 4867

Answers (6)

NiteshG86
NiteshG86

Reputation: 85

Use UNION instead of FULL OUTER JOIN in your query like this

select * from 
(

select 
FTM_OFFICE_ID,
NVL(SUM(FTD_NRS_AMOUNT),0) as TOTAL 
from FMS_TRANS_DTL 
inner join FMS_TRANS_MST ON FMS_TRANS_MST.FTM_TRANS_MST_ID = FMS_TRANS_DTL.FTD_TRANS_MST_ID 
inner join FMS_FC_VOUCHER_CONFIG ON FMS_FC_VOUCHER_CONFIG.FFVC_VOUCHER_ID = FMS_TRANS_MST.FTM_VOUCHER_ID 
where FFVC_ACCOUNT_TYPE = 3 and FTM_FISCAL_YEAR='2066/67'
and FTD_ACC_ID = 105
group by FTM_OFFICE_ID ) T1 union (select FTM_OFFICE_ID,
NVL(SUM(FTD_NRS_AMOUNT),0) as TOTAL 
from FMS_TRANS_DTL 
inner join FMS_TRANS_MST ON FMS_TRANS_MST.FTM_TRANS_MST_ID = FMS_TRANS_DTL.FTD_TRANS_MST_ID 
inner join FMS_FC_VOUCHER_CONFIG ON FMS_FC_VOUCHER_CONFIG.FFVC_VOUCHER_ID = FMS_TRANS_MST.FTM_VOUCHER_ID 
where FFVC_ACCOUNT_TYPE = 3 and FTM_FISCAL_YEAR='2066/67'
and FTD_ACC_ID = 110
group by FTM_OFFICE_ID )T2

Upvotes: 0

DazzaL
DazzaL

Reputation: 21993

workaround based on your query + followup comments is:

alter session set "_optimizer_cost_based_transformation"=off;

eg:

SQL> alter session set "_optimizer_cost_based_transformation"=off;

Session altered.

or in the sql as a hint 

/*+ opt_param('_optimizer_cost_based_transformation', 'off')

eg

select /*+ opt_param('_optimizer_cost_based_transformation', 'off') */ * from 
(
    select FTM_OFFICE_ID,

you're possibly hitting bug:

BUG 4204383: ORA-7445[KKQTNLOCBK] USING QUERY WITH SUBQUERY AND FULL OUTER JOIN

which has patched available only on 10.2.0.2 onwards (fixed fully in 10.2.0.4).

Upvotes: 4

Ben
Ben

Reputation: 1927

FULL OUTER JOIN is supported just fine in Oracle.

The error you're experiencing sounds like an Oracle bug, or possible some form of corruption. You should find an error recorded in the alert.log whenever an ORA-03113 occurs.

Typically if you receive this error it's because the background oracle system process for that session has died (which is almost always due to an Oracle internal error).

This is a problem for your friendly local DBA.

Upvotes: 4

Brian
Brian

Reputation: 7299

Looks like the only thing different is separate totals for FTD_ACC_ID values of 105 and 110. One approach would be to use CASE statements to total those separately. 

select 
FTM_OFFICE_ID,
NVL(SUM(CASE FTD_ACC_ID WHEN 105 THEN FTD_NRS_AMOUNT ELSE 0 END),0) as TOTAL_105,
NVL(SUM(CASE FTD_ACC_ID WHEN 110 THEN FTD_NRS_AMOUNT ELSE 0 END),0) as TOTAL_110,
 from FMS_TRANS_DTL 
inner join FMS_TRANS_MST ON FMS_TRANS_MST.FTM_TRANS_MST_ID = FMS_TRANS_DTL.FTD_TRANS_MST_ID 
inner join FMS_FC_VOUCHER_CONFIG ON FMS_FC_VOUCHER_CONFIG.FFVC_VOUCHER_ID = FMS_TRANS_MST.FTM_VOUCHER_ID 
where FFVC_ACCOUNT_TYPE = 3 and FTM_FISCAL_YEAR='2066/67'
and FTD_ACC_ID in (105,110)
group by FTM_OFFICE_ID

Upvotes: 1

Frank Schmitt
Frank Schmitt

Reputation: 30845

If indeed (for whatever reason) a full outer join doesn't work for you, use

-- all rows present in both t1 and t2
select * from t1 inner join t2
union all
-- all rows present in t1 but not in t2
select * from t1 left outer join t2 where t2.pk is null
union all
-- all rows present in t2 but not in t1
select * from t1 right outer join t2 where t1.pk is null

instead of

select * from t1 full outer join t2

This will return the same result.

Upvotes: 2

Akash KC
Akash KC

Reputation: 16310

You can try this query:

 SELECT 
    FTM_OFFICE_ID,
    NVL(SUM(FTD_NRS_AMOUNT),0) AS TOTAL 
    FROM FMS_TRANS_DTL 
    INNER JOIN FMS_TRANS_MST ON FMS_TRANS_MST.FTM_TRANS_MST_ID = FMS_TRANS_DTL.FTD_TRANS_MST_ID 
    INNER JOIN FMS_FC_VOUCHER_CONFIG ON FMS_FC_VOUCHER_CONFIG.FFVC_VOUCHER_ID = FMS_TRANS_MST.FTM_VOUCHER_ID 
    WHERE FFVC_ACCOUNT_TYPE = 3 AND FTM_FISCAL_YEAR='2066/67'
    AND FTD_ACC_ID IN (
            SELECT DISTINCT FDP_DHARAUTI_C_ACC_ID FROM FMS_DHARAUTI_PARAMETER WHERE FDP_FISCAL_YEAR='2066/67'
            UNION
            SELECT DISTINCT FDP_DHARAUTI_L_ACC_ID FROM FMS_DHARAUTI_PARAMETER WHERE FDP_FISCAL_YEAR='2066/67'   
            )
    GROUP BY FTM_OFFICE_ID

Upvotes: 1

Related Questions