MicroStrategy ONE

Full Outer Join Support

Full Outer Join Support is an advanced property that is hidden by default. For information on how to display this property, see Viewing and Changing Advanced VLDB Properties.

The Full Outer Join Support property specifies whether the database platform supports full outer join syntax:

  • No support (default): Full outer joins are not supported or processed to return results. This can help to prevent costly outer join queries and also avoids errors for databases that do not support full outer joins. Additionally, if your database does not support the COALESCE function, you should set this property to No support.
  • Support: Full outer joins are attempted when required by your report or dashboard actions. By selecting this option, the Join Type VLDB property is assumed to be Join 92 and any other setting in Join Type is ignored. Additionally, the COALESCE function can be included in the SQL query.

Since full outer joins can require a lot of database and Intelligence Server resources, and full outer joins are not supported for all databases, it is recommended to enable support for individual reports first. If your results are returned successfully and full outer joins are used often for your report or dashboard environment, you can consider enabling support for the entire database. However, enabling full outer join support for specific reports is recommended if full outer joins are only used for a small to moderate amount of reporting needs. Creating a template with full outer join support enabled can save report developers time when requiring full outer joins.

Levels at Which You Can Set This

Database instance, report, and template

Examples

Full Outer Join Not Supported (default)

select a12.YEAR_ID YEAR_ID,
 sum(a11.TOT_SLS_DLR) TOTALSALESCO
into #ZZTIS00H5MJMD000
from HARI_REGION_DIVISION a11
 join HARI_LOOKUP_DAY a12
 on (a11.CUR_TRN_DT = a12.CUR_TRN_DT)
where a12.MONTH_ID = 199411
group by a12.YEAR_ID
select a12.YEAR_ID YEAR_ID,
 sum(a11.TOT_SLS_DLR) TOTALSALESCO
into #ZZTIS00H5MJMD001
from HARI_REGION_DIVISION a11
 join HARI_LOOKUP_DAY a12
 on (a11.CUR_TRN_DT = a12.CUR_TRN_DT)
where a12.MONTH_ID = 199311
group by a12.YEAR_ID
select pa1.YEAR_ID YEAR_ID
into #ZZTIS00H5MJOJ002
from #ZZTIS00H5MJMD000 pa1
union
select pa2.YEAR_ID YEAR_ID
from #ZZTIS00H5MJMD001 pa2
select distinct pa3.YEAR_ID YEAR_ID,
 a11.YEAR_DESC YEAR_DESC,
 pa1.TOTALSALESCO TOTALSALESCO,
 pa2.TOTALSALESCO TOTALSALESCO1
from #ZZTIS00H5MJOJ002 pa3
 left outer join #ZZTIS00H5MJMD000 pa1
 on (pa3.YEAR_ID = pa1.YEAR_ID)
 left outer join #ZZTIS00H5MJMD001 pa2
 on (pa3.YEAR_ID = pa2.YEAR_ID)
 left outer join HARI_LOOKUP_YEAR a11
 on (pa3.YEAR_ID = a11.YEAR_ID)

Full Outer Join Supported 

select a12.YEAR_ID YEAR_ID,
 sum(a11.TOT_SLS_DLR) TOTALSALESCO
into #ZZTIS00H5MKMD000
from HARI_REGION_DIVISION a11
 join HARI_LOOKUP_DAY a12
 on (a11.CUR_TRN_DT = a12.CUR_TRN_DT)
where a12.MONTH_ID = 199411
group by a12.YEAR_ID
select a12.YEAR_ID YEAR_ID,
 sum(a11.TOT_SLS_DLR) TOTALSALESCO
into #ZZTIS00H5MKMD001
from HARI_REGION_DIVISION a11
 join HARI_LOOKUP_DAY a12
 on (a11.CUR_TRN_DT = a12.CUR_TRN_DT)
where a12.MONTH_ID = 199311
group by a12.YEAR_ID
select distinct coalesce(pa1.YEAR_ID,
pa2.YEAR_ID) YEAR_ID,
 a11.YEAR_DESC YEAR_DESC,
 pa1.TOTALSALESCO TOTALSALESCO,
 pa2.TOTALSALESCO TOTALSALESCO1
from #ZZTIS00H5MKMD000 pa1
 full outer join #ZZTIS00H5MKMD001 pa2
 on (pa1.YEAR_ID = pa2.YEAR_ID)
 left outer join HARI_LOOKUP_YEAR a11
on (coalesce(pa1.YEAR_ID, pa2.YEAR_ID) = a11.YEAR_ID)