Left Outer Join

[sekar211]

I have a report in which i have set the "Preserve all final pass result" in the VLDB properties of the report.The SQL is as below:

select distinct a12.BAN BAN,
a12.MED_BILL_NAME MED_BILL_NAME,
a13.ACCT_STATUS ACCT_STATUS,
a14.ACCOUNT_STATUS ACCOUNT_STATUS
from ODS_BILL_MEDIA a12
left outer join ODS_ACCOUNT_STATUS a13
on (a12.BAN = a13.ACCOUNT_NUM_BAN)
left outer join ODS_REF_ACCOUNT_STATUS_CODE a14
on (a13.ACCT_STATUS = a14.ACCTSTATCD)


The SQL i am expecting is as follows:

select distinct a12.BAN BAN,
a12.MED_BILL_NAME MED_BILL_NAME,
a13.ACCT_STATUS ACCT_STATUS,
a14.ACCOUNT_STATUS ACCOUNT_STATUS
from ODS_BILL_MEDIA a12
join ODS_ACCOUNT_STATUS a13 ***No left outer Join***
on (a12.BAN = a13.ACCOUNT_NUM_BAN)
left outer join ODS_REF_ACCOUNT_STATUS_CODE a14
on (a13.ACCT_STATUS = a14.ACCTSTATCD)

I need left out join for only one join not for both.Is it possible to get it?

Thank You

 

[JaC74]

I had similar issue that I tried to fix by using the VLDB setting but was never able to specify only one left outer join. It will do it for all. I got around it by creating a view that did the correct join and brought it in to the project. I am almost certain that the VLDB settings can not accomplish what you are looking for.

 

[entaroadun]

Actually, in the Report Data Options, there's a section for specifying join type for attributes. It's really more for controlling the behavior of attribute element filters, but you may have luck with it.

I don't use MSTR any more, so I don't have the application in front of me to look at the options. Give it a shot and let us know what you turn up.

 

[sekar211]

By default all the attributes are outer join in report data option.I worked around it but din't make any difference.

I think as Jac74 said i have to work with view or in my case i can add a null row in the the table and use applysimple to convert that null value to some other value like "0" to make a regular inner join.

Do you think this can be over come if we have privilege to hardcode the sql?