3 Replies Latest reply on Nov 12, 2013 9:36 AM by Justin Smith

    Case or IIF in Custom SQL

    Justin Smith

      Is it possible to use a Case or IIF in a custom SQL connection? I can't seem to get either to work, and am wondering if it has something to do with my ODBC connection or simply just a syntax error.


      Thanks in advance,


        • 1. Re: Case or IIF in Custom SQL
          Joshua Milligan



          A CASE statement should work.  What is your source?  Are you able to share the SQL statement (or at least the CASE)?  Would it be possible to use a calculated field instead (Tableau would translate that into SQL for you).




          • 2. Re: Case or IIF in Custom SQL
            Justin Smith



            Thanks for the reply and sorry for the delayed response. I tried to do the Calculated field after pulling it into Tableau, but taking a string to an int then doing the math and wrapping it back to a string gave me weird results. I ran into this before with the ODBC > CONNX middleware > my database connection. Once I take it into Tableau as an extract it works fine, but I need/want a live connection. Plus it gave me a reason to try to learn Case in SQL


            Heres the Case I had -



              CASE when mid(<Parameters.Term>,3,1) = '3'

                then concat('20',left(<Parameters.Term>,2)+1)

                    else concat('20',left(<Parameters.Term>,2))


                as "YEAR",



            FROM "dbo"."COURSE" "COURSE"

            WHERE "COURSE"."TERMCODE" = <Parameters.Term>



            I converted to IIF also, but neither seem to work:



              iif(mid(<Parameters.Term>,3,1) = '3',concat('20',left(<Parameters.Term>,2)+1),concat('20',left(<Parameters.Term>,2))) as "YEAR",



            Later down the line, I'm Unioning some stuff for a comparison based on <Parameters.Term>, but it fails on the first line of the select.


            Any help is appreciated!



            • 3. Re: Case or IIF in Custom SQL
              Justin Smith

              Figured it out : "mid" is not an acceptable function. Instead it is "substring". Always something easy that I can't seem to get past...