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,

      Justin

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

          Justin,

           

          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).

           

          Regards,

          Joshua

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

            Joshua,

             

            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 -

             

            SELECT

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

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

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

              END

                as "YEAR",

            BUNCH OF OTHER STUFF,

              "COURSE"."WHOLECREDIT" AS "WHOLECREDIT"

            FROM "dbo"."COURSE" "COURSE"

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

             

             

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

             

            SELECT

              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!

             

            Justin

            • 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...

               

              Justin