DA0005(Ora-01417 a table may be outer joined to at most one)

server administration Web Intelligence 6.x Servers
1

We started seeing this error on a number of reports in both full client and WebI right after we upgraded to 6.5.1. We did not have this issue with version 6.1b.

Automation exception BusinessObjects server process raised an automation exception. (Error: BOMGR 0060) IDispatch error #109 Connection or SQL sentence error: (DA0005): [Exception: DBD, ORA-01417: a table may be outer joined to at most one other table State: N/A] The following data providers have not been successfully refreshed: DEFECTS.
(returned by function Refreshing Document) )

BhaskerB (BOB member since 2005-05-02)

2

Can you post the SQLs that are generated by 6.1x and 6.5x ? What’s the difference between SQLs?

shamit (BOB member since 2004-07-01)

3

Shamit,
Alreay we have migrated to 6.5,don’t have the query of 6.1b.Please see the query of 6.5 below.

SELECT DISTINCT
  DECODE(ONESOURCE_DEFECTS.ORIGSOURCE,'L','MANUFACTURING','S','SOURCE INSPECTION','R','RECEIVING'),
  ONESOURCE_DEFECTS.REMARKS,
  ONESOURCE_DEFECTS.SBUCODE,
  CASPERDBA.PART_INFO_TBL.CMDTY_CD,
  CASPERDBA.PART_INFO_TBL.BUYER_CD,
  CASPERDBA.PART_INFO_TBL.PART_NBR,
  CASPERDBA.PART_INFO_TBL.PART_DESC,
  CASPERDBA.PART_INFO_TBL.STD_COST,
  ONESOURCE_DEFECTS.FAIL_PART_QTY,
  TO_CHAR(TO_DATE(CASPERDBA.SHOP_CAL_TBL.X_FISCAL_MO, 'YYMM'),'MON'),
  CASPERDBA.SHOP_CAL_TBL.X_FISCAL_MO,
  CASPERDBA.SUPPLIER_INFO_TBL.SUPPLIER_CD,
  CASPERDBA.SUPPLIER_INFO_TBL.SUPPLIER_NM,
  CASPERDBA.PART_INFO_TBL.STD_COST*ONESOURCE_DEFECTS.FAIL_PART_QTY,
  ONESOURCE_DEFECTS.FAILED_PART_NBR,
  ONESOURCE_DEFECTS.DISP_DT,
  ONESOURCE_DEFECTS.REFNUMBER,
  TO_CHAR(CASPERDBA.SHOP_CAL_TBL.CAL_DT,'YYMM'),
  TO_CHAR(CASPERDBA.SHOP_CAL_TBL.CAL_DT,'MON'),
  rpts.get_cmdty_resp_emp(CASPERDBA.PART_INFO_TBL.DIV_CD,CASPERDBA.PART_INFO_TBL.CMDTY_CD,CASPERDBA.PART_INFO_TBL.PART_NBR,NULL,'EMP_NAME' )
FROM
  ONESOURCE_DEFECTS,
  CASPERDBA.SHOP_CAL_TBL,
  CASPERDBA.SUPPLIER_INFO_TBL,
  CASPERDBA.PART_INFO_TBL
WHERE
  ( TRUNC(ONESOURCE_DEFECTS.DISP_DT)=TRUNC(CASPERDBA.SHOP_CAL_TBL.CAL_DT(+))  )
  AND  ( ONESOURCE_DEFECTS.DIV_CD(+)=CASPERDBA.PART_INFO_TBL.DIV_CD  )
  AND  ( CASPERDBA.SUPPLIER_INFO_TBL.SUPPLIER_CD=ONESOURCE_DEFECTS.RECV_SUPPLIER(+)  )
  AND  ( ONESOURCE_DEFECTS.FAILED_PART_NBR(+)=CASPERDBA.PART_INFO_TBL.PART_NBR  )
  AND  ( CASPERDBA.SUPPLIER_INFO_TBL.DIV_CD=ONESOURCE_DEFECTS.DIV_CD(+)  )
  AND  
  ONESOURCE_DEFECTS.DIV_CD  =  'CM'
  AND  ONESOURCE_DEFECTS.DISP_DT  BETWEEN  @variable('1 Start Date') AND @variable('2 End Date')

Please suggest me the solution.

BhaskerB (BOB member since 2005-05-02)

4

Today we got the solution from BO Tech support, Time being its seems to be worling fine.

Businees Objects (CaseId:302556975 - Outer join error)

the deletion of the two parameters “left_outer and right_outer joins” in oracle .prm file was a work around in 5x version and seems to work in 6x also.

For further information if ANSI_92 is set to NO then joins comes in the “where class” while if it is set to YES then the join comes in the “from class”

BhaskerB (BOB member since 2005-05-02)

5

After implementation of this solution out endusers are complaining that the reports are displaying wrong results.
We have analyzed the problem and found that with this solution outer join is trated as normal join, means that its not including outer join information in report sql.

Finally we came to know that solution suggested by BO tech support is not the correct one.

Please can some body suggest me the alternate solution if available.

Thanks in advance.

BhaskerB (BOB member since 2005-05-02)

6

Bhasker,
the error “ORA-01417: a table may be outer joined to at most one other table” has nothing to do with BO. Its an oracle error. The cause of this is that in a particular query, you can have atmost 1 table being outerjoined to other tables. This is plain oracle. (Please read the oracle docs/books for more on outer joins)
In your query, ONSOURCE_DEFECTS seems to be the fact table. So according to me, it should be outerjoined to other tables.
In you qeury below -

WHERE 
  ( TRUNC(ONESOURCE_DEFECTS.DISP_DT)=TRUNC(CASPERDBA.SHOP_CAL_TBL.CAL_DT(+))  ) 
  AND  ( ONESOURCE_DEFECTS.DIV_CD(+)=CASPERDBA.PART_INFO_TBL.DIV_CD  ) 
  AND  ( CASPERDBA.SUPPLIER_INFO_TBL.SUPPLIER_CD=ONESOURCE_DEFECTS.RECV_SUPPLIER(+)  ) 
  AND  ( ONESOURCE_DEFECTS.FAILED_PART_NBR(+)=CASPERDBA.PART_INFO_TBL.PART_NBR  ) 
  AND  ( CASPERDBA.SUPPLIER_INFO_TBL.DIV_CD=ONESOURCE_DEFECTS.DIV_CD(+)  )

you are outer joining SHOP_CAL_TBL to ONESOURCE_DEFECTS that means all rows from ONESOURCE_DEFECTS not having matching rows in SHOP_CAL_TBL would be returned. This is fine.

Your ONESOURCE_DEFECTS table is the main table.

ONESOURCE_DEFECTS.column = other_table.column(+) is correct
ONESOURCE_DEFECTS.column(+) = other_table.column is INCORRECT
becasue in first join, you have specified ONESOURCE_DEFECTS to be the main table(from where non matching rows would be returned)

If you modify your query as below -

WHERE
( TRUNC( ONESOURCE_DEFECTS.DISP_DT)=TRUNC(CASPERDBA.SHOP_CAL_TBL.CAL_DT(+))  ) 
  AND  ( ONESOURCE_DEFECTS.DIV_CD=CASPERDBA.PART_INFO_TBL.DIV_CD(+)  ) 
  AND  ( CASPERDBA.SUPPLIER_INFO_TBL.SUPPLIER_CD(+)=ONESOURCE_DEFECTS.RECV_SUPPLIER  ) 
  AND  ( ONESOURCE_DEFECTS.FAILED_PART_NBR=CASPERDBA.PART_INFO_TBL.PART_NBR(+)  ) 
  AND  ( CASPERDBA.SUPPLIER_INFO_TBL.DIV_CD(+)=ONESOURCE_DEFECTS.DIV_CD )

it will work.

I guess this should solve your problem and give you correct results businesswise.

In any case, let me know whether it helped you or not.

divymani (BOB member since 2005-02-04)

7

Divymani,
Thanks for the solution. I understood this solution is going to work, but the problem here is all these reports not developed by us, just we are maintaining(Administration) the application(Containing more than 700 reports and 100 universes).

Initially we told all the report (we have around 5 to 6 reports with this problem) owners that this problem is not due to BO, It’s a problem of Oracle. All the users are saying that these reports used to work fine with 6.1b, It’s happened because of migration.

Actually all these reports used work fine in BO 6.1b, recently we migrated the instance from 6.1b to 6.5.After migration we are getting Ora-1417 error.

BhaskerB (BOB member since 2005-05-02)

8

Change your universe parameter for ANSI92 from No to Yes, and this issue will go away. Oracle SQL written in the ANSI92 format will allow this type of outer joining.

paulmaric (BOB member since 2004-02-09)

9

Hi,
I got the same issue, have you already fixed this issue ? would you please help to share the solution, I need it urgent … Many many thanks !

Eva

yoyo-eva (BOB member since 2009-11-02)