Inner Join

No PostgreSQL specific customizations were made for this feature.

The following sample query is how MicroStrategy supports inner join against PostgreSQL.

Copy

SELECT
c1.C_ADDRESS,  
c1.C_NAME customer_1,
c2.C_NAME customer_2  
FROM  
tpch.CUSTOMER c1  
INNER JOIN tpch.CUSTOMER c2 ON c1.C_CUSTKEY> c2.C_CUSTKEY  
AND c1.C_CUSTKEY<10  
ORDER BY  
customer_1,   
customer_2;

How to Define Join Behavior

You can define join behavior through Report Data Options in each individual report in design view.

For attributes, the default join type is outer join.

For metrics, the default join type is inner join.

If the join type is changed, the SQL statements would also change. For example:

Metric, by default, is inner join. The SQL statement appears as follows:

Copy

select    pa11.C_CUSTKEY  C_CUSTKEY,
    pa11.WJXBFS1  WJXBFS1,
    pa12.WJXBFS1  WJXBFS2
from    (select    a11.O_CUSTKEY  C_CUSTKEY,
        sum(a11.O_TOTALPRICE)  WJXBFS1
    from    ORDERS    a11
    where    a11.O_CUSTKEY <=  2
    group by    a11.O_CUSTKEY
    )    pa11
    join    (select    a11.C_CUSTKEY  C_CUSTKEY,
        a11.C_ACCTBAL  WJXBFS1
    from    CUSTOMER    a11
    where    a11.C_CUSTKEY <=  2
    )    pa12
      on     (pa11.C_CUSTKEY = pa12.C_CUSTKEY)

However, if you change metric to outer join, the SQL statement would change to:

Copy

select    coalesce(pa11.C_CUSTKEY, pa12.C_CUSTKEY)  C_CUSTKEY,
    pa11.WJXBFS1  WJXBFS1,
    pa12.WJXBFS1  WJXBFS2
from    (select    a11.O_CUSTKEY  C_CUSTKEY,
        sum(a11.O_TOTALPRICE)  WJXBFS1
    from    ORDERS    a11
    where    a11.O_CUSTKEY <=  2
    group by    a11.O_CUSTKEY
    )    pa11
    full outer join    (select    a11.C_CUSTKEY  C_CUSTKEY,
        a11.C_ACCTBAL  WJXBFS1
    from    CUSTOMER    a11
    where    a11.C_CUSTKEY <=  2
    )    pa12
      on     (pa11.C_CUSTKEY = pa12.C_CUSTKEY)