Incorrect SQL generated

[Former Member]

Hi,

I am facing the exact problem described in the following SAP KBA, however following the document does not fix the issue.

https://websmp230.sap-ag.de/sap/support/notes/1200111

Issue : The join between tables is defined as a left outer join, but the SQL generated returns a right outer join instead.

BOE version : 4.0 SP6

DB : Oracle 11g

Please suggest me on this.

Thanks & Regards,

Sastry

[Former Member · Accepted solution]

Hi Sastry,

Please check if the universe has below parameter set under File>Parameters>Parameters tab and oracle.prm file

EXT_JOIN_INVERT=YES

By default this would be taken as YES,so in your case set it to Explicit NO by adding it in File>Parameters>Parameters tab , so that join reversing do not happen in the universe.

If its through IDT, add it in data foundation>properties>parameters

Thanks

Mallik

[Former Member · Accepted solution]

Hi Mallik,

Thank you for the prompt response.

Oracle.prm file has this option set to Yes, so i changed this the value to No, whereas there was no such parameter exist under File->Parameters->Parameters. So i manually added the parameter and made sure that the value set to No, however the issue still exist.

Following are my Original and Incorrect (Generated by Universe) SQLs :

******************************Original*******************************************

SELECT

"VU_RPT_AUTH_MASTER"."MEH_IPA_ID",

"VU_RPT_AUTH_MASTER"."PCP_LONG_NAME",

"VU_RPT_AUTH_MASTER"."MEMBER_NAME",

"VU_RPT_AUTH_MASTER"."MM_SUBSCRIBER_ID",

"VU_RPT_AUTH_MASTER"."AUTH_NUMBER",

"VU_RPT_AUTH_MASTER"."AUTH_TYPE",

"VU_RPT_AUTH_MASTER"."AUTH_STATUS",

"VU_RPT_AUTH_MASTER"."DIAGNOSIS_1",

"VU_RPT_AUTH_MASTER"."DIAGNOSIS_2",

"VU_RPT_AUTH_MASTER"."INSERT_USER",

"VU_RPT_AUTH_MASTER"."DIAGNOSIS_3",

"VU_RPT_AUTH_MASTER"."DIAGNOSIS_4",

"VU_RPT_AUTH_MASTER"."DIAGNOSIS_5",

"VU_RPT_AUTH_MASTER"."DIAGNOSIS_6",

"VU_RPT_AUTH_MASTER"."DIAGNOSIS_7",

"VU_RPT_AUTH_MASTER"."DIAGNOSIS_8",

"VU_RPT_AUTH_MASTER"."DIAGNOSIS_9",

"Denied_REASON_CODE_MASTER"."REASON_CODE",

"Denied_REASON_CODE_MASTER"."DESCRIPTION",

"Held_REASON_CODE_MASTER"."REASON_CODE",

"Held_REASON_CODE_MASTER"."DESCRIPTION",

"Closed_REASON_CODE_MASTER"."REASON_CODE",

"Closed_REASON_CODE_MASTER"."DESCRIPTION",

"VU_RPT_AUTH_MASTER"."SEC_AUTH_NUMBER",

"VU_RPT_AUTH_MASTER"."INTAKE_DATE_TIME",

"IPA_MASTER"."NAME"

FROM

((("DORIS"."VU_RPT_AUTH_MASTER" "VU_RPT_AUTH_MASTER"

LEFT OUTER JOIN

"DORIS"."REASON_CODE_MASTER" "Closed_REASON_CODE_MASTER" ON "VU_RPT_AUTH_MASTER"."CLOSED_REASON"="Closed_REASON_CODE_MASTER"."REASON_CODE")

LEFT OUTER JOIN

"DORIS"."REASON_CODE_MASTER" "Denied_REASON_CODE_MASTER" ON "VU_RPT_AUTH_MASTER"."DENIED_REASON"="Denied_REASON_CODE_MASTER"."REASON_CODE")

LEFT OUTER JOIN

"DORIS"."REASON_CODE_MASTER" "Held_REASON_CODE_MASTER" ON "VU_RPT_AUTH_MASTER"."HOLD_REASON"="Held_REASON_CODE_MASTER"."REASON_CODE") LEFT OUTER JOIN

"DORIS"."IPA_MASTER" "IPA_MASTER" ON "VU_RPT_AUTH_MASTER"."MEH_IPA_ID"="IPA_MASTER"."IPA_ID"

ORDER BY

"VU_RPT_AUTH_MASTER"."MEH_IPA_ID", "VU_RPT_AUTH_MASTER"."PCP_LONG_NAME", "VU_RPT_AUTH_MASTER"."MEMBER_NAME"

************************In Correct****************************************

SELECT DISTINCT

  VU_RPT_AUTH_MASTER.MEH_IPA_ID,

  VU_RPT_AUTH_MASTER.PCP_LONG_NAME,

  VU_RPT_AUTH_MASTER.MEMBER_NAME,

  VU_RPT_AUTH_MASTER.MM_SUBSCRIBER_ID,

  VU_RPT_AUTH_MASTER.AUTH_NUMBER,

  VU_RPT_AUTH_MASTER.AUTH_TYPE,

  VU_RPT_AUTH_MASTER.AUTH_STATUS,

  VU_RPT_AUTH_MASTER.DIAGNOSIS_1,

  VU_RPT_AUTH_MASTER.DIAGNOSIS_2,

  VU_RPT_AUTH_MASTER.INSERT_USER,

  VU_RPT_AUTH_MASTER.DIAGNOSIS_3,

  VU_RPT_AUTH_MASTER.DIAGNOSIS_4,

  VU_RPT_AUTH_MASTER.DIAGNOSIS_5,

  VU_RPT_AUTH_MASTER.DIAGNOSIS_6,

  VU_RPT_AUTH_MASTER.DIAGNOSIS_7,

  VU_RPT_AUTH_MASTER.DIAGNOSIS_8,

  VU_RPT_AUTH_MASTER.DIAGNOSIS_9,

  REASON_CODE_MASTER.REASON_CODE,

  REASON_CODE_MASTER.DESCRIPTION,

  REASON_CODE_MASTER.REASON_CODE,

  REASON_CODE_MASTER.DESCRIPTION,

  REASON_CODE_MASTER.REASON_CODE,

  REASON_CODE_MASTER.DESCRIPTION,

  VU_RPT_AUTH_MASTER.SEC_AUTH_NUMBER,

  VU_RPT_AUTH_MASTER.INTAKE_DATE_TIME,

  IPA_MASTER.NAME

FROM

IPA_MASTER LEFT OUTER JOIN VU_RPT_AUTH_MASTER ON (IPA_MASTER.IPA_ID=VU_RPT_AUTH_MASTER.MEH_IPA_ID)

RIGHT OUTER JOIN REASON_CODE_MASTER ON (VU_RPT_AUTH_MASTER.HOLD_REASON=REASON_CODE_MASTER.REASON_CODE  AND  VU_RPT_AUTH_MASTER.CLOSED_REASON=REASON_CODE_MASTER.REASON_CODE  AND  VU_RPT_AUTH_MASTER.DENIED_REASON=REASON_CODE_MASTER.REASON_CODE)

[Former Member · Accepted solution]

Hi Sastry,

Did you ensure that you save the universe and export it after the parameter update so that the query generation reflects it?

Also test it by building a new webi report with same objects while testing it and confirm if you still see the issue.

Thanks

Mallik

[Former Member · Accepted solution]

Mallik,

Yes. I did that. I saved and exported once i am done with parameter update but still i get the same issue.

Best Regards,

Sastry

[Former Member · Accepted solution]

Hi,

After exporting the modified universe to repository try below.

Just drop the object which relates to the troubled join(s) in report query panel and drag it again to query panel and check the sql.

Cheers,

Suresh Aluri.

[Former Member · Accepted solution]

Hi,

Issue got. Joins were incorrectly defined. Thank you all for the support on this.

Best Regards,

Sastry

[Former Member]

hi

Post your query

Regards

Rathish