Left outer joins 1

[iolaper]

Hello guys,
I am trying to generate a report with 3 columns to give out results which have NULLs on certain cells. I realized that the rows with NULLs were not being displayed. I am trying to play with the VLDB to perform a LEFT OUTER JOIN. But the SQL only has JOIN between each table. How do I make MSTR perform a Left outer join.

Thank you so much!!
Herez the code incase you need to refer

select distinct a13.InvoiceDate InvoiceDate,
a18.TenantCompanyId TenantCompanyId,
a18.TenantCompanyDesc TenantCompanyDesc,
a19.TenantRepCompanyId TenantRepCompanyId,
a19.TenantRepCompanyDesc TenantRepCompanyDesc,
a11.BuildingId PROPERTYID,
a11.BuildingDesc PropertyDesc,
a11.BuildingAddress1 PropertyAddress1,
a11.BuildingCity PropertyCity,
a11.BuildingAddress2 PropertyAddress2,
a11.BuildingPhone PropertyPhone,
a11.BuildingState PropertyState,
a11.BuildingZip PropertyZip,
a16.LandLordRepId LandLordRepId,
a17.LandlordRepDesc LandlordRepDesc,
a17.LandlordRepCompanyId LandlordRepCompanyId,
a17.LandlordRepCompanyDesc LandlordRepCompanyDesc,
a12.RepresentationId RepresentationId,
a112.RepresentationDesc RepresentationDesc,
a12.ProductTypeId ProductTypeId,
a111.ProductTypeDesc ProductTypeDesc,
a12.LeaseTypeId LeaseTypeId,
a110.LeaseTypeDesc LeaseTypeDesc,
a16.DealSize DealSize,
a16.DealSizeUnit DealSizeUnit
from LU_BUILDING a11
join LU_DEAL a12
on (a11.DealId = a12.DealId)
join LU_INVOICE a13
on (a11.DealId = a13.DealId)
join LU_INVOICE_CTI_DATA a14
on (a13.InvoiceId = a14.InvoiceId and
a13.TransactionTypeId = a14.TransactionTypeId)
join FACT_DEAL a16
on (a11.DealId = a16.DealId and
a12.CustomerId = a16.CustomerId)
join LU_LANDLORD_REP a17
on (a11.DealId = a17.DealId and
a16.LandLordRepId = a17.LandLordRepId)
join LU_TENANT a18
on (a11.DealId = a18.DealId and
a16.TenantId = a18.TenantId)
join LU_TENANT_REP a19
on (a11.DealId = a19.DealId)
join LU_LEASE_TYPE a110
on (a12.LeaseTypeId = a110.LeaseTypeId)
join LU_PRODUCT_TYPE a111
on (a12.ProductTypeId = a111.ProductTypeId)
join LU_REPRESENTATION a112
on (a12.RepresentationId = a112.RepresentationId)
where (a13.InvoiceDate between CONVERT(datetime, '2004-01-01 00:00:00', 120) and CONVERT(datetime, '2004-04-02 00:00:00', 120)
and a12.LeaseTypeId in (1, 4, 5, 6, 7, 9, 10, 12)
and a12.DealClassificationId in (3, 4))

 

[JaC74]

The code has no aggregates (metrics). You want to place metrics on the report and make the join types Outer Join. The SQL passes will then left outer join and give you the results you want. I'm assuming that a16.DealSizeUnit should be a metric that an attribute. Create a metric out of the 3 columns you mentioned and outer join them.

 

[iolaper]

Hey JaC,
Didn't work. It says fact not defined at that level. I am not sure which attribute is causing that problem. I am not performing any mathematical calculations with DealSizeUnit anyway so I don't want to add a metric into the report.
Also notice the tables LU_BUILDING, LU_DEAL after the FROM clause. Can that order be changed?
I want it as follows
FROM LU_DEAL join...

Thanks a million!!

 

[JaC74]

MicroStrategy has specific order in the from clause.

1. Fact Tables
2. Metric Qualification (MQ) Tables
3. Relationship Tables
4. Lookup Tables

VLDB Settings provide minimal modifications to this order. You can check out the 3 options in VLDB Properties / Joins / From Clause Order

While you are there, check out the last option under VLDB Properties / Joins / Preserve all look up table elements.

I think this is the only setting that will force a left outer join in the from clause. Check the last option under this setting.