Have you read the news today?

You may already know that Oracle has the capability to get data directly from the Web. Like in:

SELECT HTTPURITYPE.createuri (‘http://www.economist.com/rss/leaders_rss.xml’).getclob () RSS FROM DUAL;

This offers a lot of possibilities when you need to query external data and, combined with some XML magic, can make a cute RSS view out of a well-formed xml feed:

 CREATE VIEW RSS_READER
AS
   SELECT EXTRACTVALUE (VALUE (p), ‘/item/title’) title,
          EXTRACTVALUE (VALUE (p), ‘/item/link’) link_url,
          EXTRACTVALUE (VALUE (p), ‘/item/description’) description
     FROM TABLE (
             XMLSEQUENCE (EXTRACT (xmltype (HTTPURITYPE.createuri (‘http://www.economist.com/rss/leaders_rss.xml’).getclob (),
                                            NULL,
                                            1,
                                            1),
                                   ‘/rss/channel/item’,
                                   ‘xmlns:media=”http://search.yahoo.com/mrss/”‘))) p;

Nice and cheap, no need to store data in the RDBMS. Every time you access the view the data is read from the Web. Just change the rss url according to your feed (you will probably need to rework the EXTRACT functions also).

Note: if you’re on Oracle 11g you may have to grant connecttion to the rss feed domain thru’ an ACL (ask your DBA for details…)

Playing with DATE ranges

If you search on the net for a trick to generate date rangees in Oracle, you most probably will end up here:

http://stackoverflow.com/questions/418318/generate-a-range-of-dates-using-sql

variuos interesting and mind-challenging ways to achieve the same result.

I found this way the most cost effective and readable, yet flexible enough for my datawarehouse ETL processes.

WITH LIMITS AS (SELECT TO_DATE (’01-JAN-2010′) BEGINDATE, SYSDATE + 10 ENDDATE FROM DUAL)

SELECT (BEGINDATE + LEVEL – 1) AS DATES

FROM LIMITS

CONNECT BY LEVEL <= (ENDDATE – BEGINDATE)

/

You can modify start and end date and have a calendar in one SQL pass. Let’s go a little further: I always have reports wich need to show the current week (WTD) for example:

WITH LIMITS AS (SELECT TRUNC ( SYSDATE, ‘DY’) BEGINDATE, TRUNC (SYSDATE) ENDDATE FROM DUAL),

     CALENDAR

     AS (    SELECT (BEGINDATE + LEVEL – 1) AS DATES

               FROM LIMITS

         CONNECT BY LEVEL <= (ENDDATE – BEGINDATE))

SELECT DATES, TO_CHAR ( DATES, ‘Dy’) DAYS_OF_WEEK

  FROM CALENDAR

/

Easy way to retrieve a list of days in the current week until today. Fast, cost effective and easy to modify. And so on:

–Last full Week

WITH LIMITS AS (SELECT TRUNC ( SYSDATE – 7, ‘DY’) BEGINDATE, TRUNC ( SYSDATE, ‘DY’) ENDDATE FROM DUAL),

     CALENDAR

     AS (    SELECT (BEGINDATE + LEVEL – 1) AS DATES

               FROM LIMITS

         CONNECT BY LEVEL <= (ENDDATE – BEGINDATE))

SELECT DATES, TO_CHAR ( DATES, ‘Dy’) DAYS_OF_WEEK

  FROM CALENDAR

/

–Same day last Week to Date

WITH LIMITS AS (SELECT TRUNC (SYSDATE – 6) BEGINDATE, TRUNC (SYSDATE) ENDDATE FROM DUAL),

     CALENDAR

     AS (    SELECT (BEGINDATE + LEVEL – 1) AS DATES

               FROM LIMITS

         CONNECT BY LEVEL <= (ENDDATE – BEGINDATE + 1))

SELECT DATES, TO_CHAR ( DATES, ‘Dy’) DAYS_OF_WEEK

  FROM CALENDAR

/

–Last Month

WITH LIMITS AS (SELECT TRUNC ( ADD_MONTHS ( SYSDATE, -1), ‘MONTH’) BEGINDATE, TRUNC (SYSDATE,’MONTH’) ENDDATE FROM DUAL),

     CALENDAR

     AS (    SELECT (BEGINDATE + LEVEL – 1) AS DATES

               FROM LIMITS

         CONNECT BY LEVEL <= (ENDDATE – BEGINDATE))

SELECT DATES, TO_CHAR ( DATES, ‘Dy’) DAYS_OF_WEEK

  FROM CALENDAR

/

–Year to Date

WITH LIMITS AS (SELECT TRUNC ( SYSDATE, ‘YEAR’) BEGINDATE, TRUNC (SYSDATE) ENDDATE FROM DUAL),

     CALENDAR

     AS (    SELECT (BEGINDATE + LEVEL – 1) AS DATES

               FROM LIMITS

         CONNECT BY LEVEL <= (ENDDATE – BEGINDATE + 1))

SELECT DATES, TO_CHAR ( DATES, ‘Dy’) DAYS_OF_WEEK

  FROM CALENDAR

/

If you need to build a time dimension for your datawarehouse, you may probably need more that that.

WITH LIMITS AS (SELECT TRUNC ( SYSDATE, ‘YEAR’) BEGINDATE, TRUNC (SYSDATE) ENDDATE FROM DUAL),

     CALENDAR

     AS (    SELECT (BEGINDATE + LEVEL – 1) AS DATES

               FROM LIMITS

         CONNECT BY LEVEL <= (ENDDATE – BEGINDATE + 1))

SELECT DATES

      ,TO_CHAR ( DATES, ‘Dy’) DAYS_OF_WEEK

      ,DATES – 7 ONE_WEEK_AGO

      ,ADD_MONTHS ( DATES, -1) ONE_MONTH_AGO

      ,ADD_MONTHS ( DATES, -12) ONE_YEAR_AGO

      ,TRUNC ( DATES, ‘Q’) BEGIN_OF_QUARTER

      ,TRUNC ( ADD_MONTHS ( DATES, 3), ‘Q’) – 1 END_OF_QUARTER

  FROM CALENDAR

/

Check out http://programmerslounge.blogspot.com/2011/07/how-to-get-first-day-and-last-day-of.html for a list of more first/last attributes.

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.