SELECT ‘MicroStrategy’ FROM DUAL;

I often use the DUAL pseudo table in Oracle to generate constants or execute functions, like in SELECT SYSDATE FROM DUAL;

Appearently there is no easy way to convince MicroStrategy to use DUAL in FROM clause because it doesn’t appear in the list of available tables.

Until now I used to create a Logical view with the syntax “SELECT * FROM DUAL”, but I found a more elegant shortcut.

1. Open the Warehouse Schema windows:

2 Click on <Options…>, select <Read Setting> category, and click on <Settings…>

3. Change the default catalog SQL statement (SELECT DISTINCT OWNER NAME_SPACE, TABLE_NAME TAB_NAME FROM ALL_TABLES WHERE OWNER = ‘#LOGIN_NAME#’ AND DROPPED = ‘NO’ UNION SELECT DISTINCT OWNER NAME_SPACE, VIEW_NAME TAB_NAME FROM ALL_VIEWS WHERE OWNER = ‘#LOGIN_NAME#’) and add this lines:

UNION SELECT ‘SYS’ NAME_SPACE, ‘DUAL’ TAB_NAME FROM DUAL

So that the whole resulting statement is:

SELECT DISTINCT OWNER NAME_SPACE, TABLE_NAME TAB_NAME FROM ALL_TABLES WHERE OWNER = ‘CDM’ AND DROPPED = ‘NO’ UNION SELECT DISTINCT OWNER NAME_SPACE, VIEW_NAME TAB_NAME FROM ALL_VIEWS WHERE OWNER = ‘CDM’ UNION SELECT ‘SYS’ NAME_SPACE, ‘DUAL’ TAB_NAME FROM DUAL

4. Click Ok two times to get back to the Warehouse schema window and click on the lightning icon to refresh the table list. Select the DUAL table and move it to the right

5. Click on <Save and Close>. Now you can use it in FROM clauses.

To test how it works you may create a new “Today” attribute with the function CurrentDate(), like this

be sure to set it to Manual and select Date as datatype. Now drop the “Today” attribute onto a new report, click on SQL view and look at the SELECT.