Case statement customer query

Question

I am new to tableau parameters. I am trying to execute the following query but somehow it give me missing parenthesis error.

SELECT 

 CASE 
    WHEN <Parameters.Timeframe> = 'YTD'
    THEN TO_CHAR(to_date('01-JAN-' || to_char(sysdate, 'YYYY'), 'dd-mon-yyyy')) 

    WHEN <Parameters.Timeframe> = 'MTD'
    THEN TO_CHAR(TO_DATE('01-' || TO_CHAR(SYSDATE,'MON-YYYY'),'DD-MON-YYYY'))

    WHEN <Parameters.Timeframe> = 'QTD' and TO_CHAR(SYSDATE,'Q') = 1 
    THEN TO_CHAR(TO_DATE('01-01-' || TO_CHAR(SYSDATE,'YYYY'),'MM-DD-YYYY'))

    WHEN <Parameters.Timeframe> = 'QTD' and TO_CHAR(SYSDATE,'Q') = 2 
    THEN TO_CHAR(TO_DATE('04-01-' || TO_CHAR(SYSDATE,'YYYY'),'MM-DD-YYYY'))

    WHEN <Parameters.Timeframe> = 'QTD' and TO_CHAR(SYSDATE,'Q') = 3 
    THEN TO_CHAR(TO_DATE('07-01-' || TO_CHAR(SYSDATE,'YYYY'),'MM-DD-YYYY'))

    WHEN <Parameters.Timeframe> = 'QTD' and TO_CHAR(SYSDATE,'Q') = 4 
    THEN TO_CHAR(TO_DATE('10-01-' || TO_CHAR(SYSDATE,'YYYY'),'MM-DD-YYYY'))

    ELSE
    TO_CHAR(SYSDATE) 
    END as DATE_RANGE_START

    FROM table a

where 
    a.created_date  >= CASE 
    WHEN <Parameters.Timeframe> = 'YTD'
    THEN to_date('01-JAN-' || to_char(sysdate, 'YYYY'), 'dd-mon-yyyy') 

    WHEN <Parameters.Timeframe> = 'MTD'
    THEN TO_DATE('01-' || TO_CHAR(SYSDATE,'MON-YYYY'),'DD-MON-YYYY')

    WHEN <Parameters.Timeframe> = 'QTD' and TO_CHAR(SYSDATE,'Q') = 1 
    THEN TO_DATE('01-01-' || TO_CHAR(SYSDATE,'YYYY'),'MM-DD-YYYY')

    WHEN <Parameters.Timeframe> = 'QTD' and TO_CHAR(SYSDATE,'Q') = 2 
    THEN TO_DATE('04-01-' || TO_CHAR(SYSDATE,'YYYY'),'MM-DD-YYYY')

    WHEN <Parameters.Timeframe> = 'QTD' and TO_CHAR(SYSDATE,'Q') = 3 
    THEN TO_DATE('07-01-' || TO_CHAR(SYSDATE,'YYYY'),'MM-DD-YYYY')

    WHEN <Parameters.Timeframe> = 'QTD' and TO_CHAR(SYSDATE,'Q') = 4 
    THEN TO_DATE('10-01-' || TO_CHAR(SYSDATE,'YYYY'),'MM-DD-YYYY')

    ELSE
    SYSDATE 
        END 

and a.created_date  <= sysdate

How should I fix it

Answer 1

General advice for getting the most value out of Tableau -- Avoid entering SQL into Tableau. That goes for Custom SQL in a data connection or calling RAW SQL function in calculated fields.

That doesn't meant that there aren't valid and important uses for those features, but they are relatively rare. Most often it is a sign that someone is reverting to hand coded SQL because they already understand it, rather than taking the time to learn Tableau. That approach doesn't usually lead to the best result.

If instead you find the Tableau feature that corresponds to the concept you are trying to achieve, you will usually accomplish the same result with more flexibility and efficiency. In short, let Tableau take care of generating the specific SQL for your query unless you you have no alternative.

Tableau has all kinds of support for different ways of filtering, truncating, formatting and graphing date values.

Try just connecting to your table A directly, and placing create_date on the filter shelf, and showing a quick filter to make it easy to adjust the filter criteria. You will get different types of choices depending on whether you treat create_date as a continuous or discrete field on the filter shelf. You will always get different results depending on whether you choose to truncate create_date to the day, month, quarter or year.

In your case, I would try treating create_date as continuous (green) exact date (without truncation) on the filter shelf, and then using either a start date, range or period quick filter. You can adjust the quick filter type by clicking on the black triangle at the top right of the filter.

If none of the builtin filters suit your needs, you can define a boolean valued calculated field that references your parameter and determines whether the date should be included, and then put that field on the filter shelf.

What you put on the filter shelf impacts the where and having clauses of the generated SQL