0 Replies Latest reply on May 1, 2013 11:58 PM by chipbetterley0

    Using date parameters in custom sql (Oracle)

    chipbetterley0

      Hi,

       

      I am having trouble using date parameters in my customer sql connection to an Oracle database.  I think the issue is how Oracle interprets the date format used by Tableau.  Can you help fix my custom sql?  My objective is to assign 4 segmentation flags to each Customer based on This Year's Revenue, Last Year's Revenue, This Year's # of Orders, and Last Year's # of Orders.  This requires using a Case Between statement.

       

      My query using hard-coded dates returns accurate values:

       

      case

        when sum(case when p.order_date between to_date('01-FEB-2009','dd-mm-yyyy') and to_date('31-JAN-2010','dd-mm-yyyy') then price_amt-price_amt_return else 0 end) > <Parameters.Rev Seg 3 Max> then <Parameters.Rev Seg 4 Name>

        when sum(case when p.order_date between to_date('01-FEB-2009','dd-mm-yyyy') and to_date('31-JAN-2010','dd-mm-yyyy') then price_amt-price_amt_return else 0 end) > <Parameters.Rev Seg 2 Max> then <Parameters.Rev Seg 3 Name>

        when sum(case when p.order_date between to_date('01-FEB-2009','dd-mm-yyyy') and to_date('31-JAN-2010','dd-mm-yyyy') then price_amt-price_amt_return else 0 end) > <Parameters.Rev Seg 1 Max> then <Parameters.Rev Seg 2 Name>

        else <Parameters.Rev Seg 1 Name> end SEG_REV_LY

       

       

      My query using a parameter for the date is valid but does not return accurate values:

       

      case

        when sum(case when p.order_date between to_date(<Parameters.Last Year Start (LY)>,'dd-mm-yyyy') and to_date('31-JAN-2010','dd-mm-yyyy') then price_amt-price_amt_return else 0 end) > <Parameters.Rev Seg 3 Max> then <Parameters.Rev Seg 4 Name>

        when sum(case when p.order_date between to_date(<Parameters.Last Year Start (LY)>,'dd-mm-yyyy') and to_date('31-JAN-2010','dd-mm-yyyy') then price_amt-price_amt_return else 0 end) > <Parameters.Rev Seg 2 Max> then <Parameters.Rev Seg 3 Name>

        when sum(case when p.order_date between to_date(<Parameters.Last Year Start (LY)>,'dd-mm-yyyy') and to_date('31-JAN-2010','dd-mm-yyyy') then price_amt-price_amt_return else 0 end) > <Parameters.Rev Seg 1 Max> then <Parameters.Rev Seg 2 Name>

        else <Parameters.Rev Seg 1 Name> end SEG_REV_LY

       

      Can you help me rewrite this query so that Oracle properly interprets the date parameters?

       

      Thank you in advance.