6 Replies Latest reply on May 2, 2017 11:31 AM by jakebutt14

    ExecuteSQL Conditional Where Clause?

    jakebutt14

      Hi everyone -

       

      I'm using ExecuteSQL to create dynamic value lists and have run into a bit of a snag. Here's my scenario:

       

      We have an application that is for assigning documents for review and signature. I have an EMPLOYEES table where each employee is classified by a "role", and an SIGNATURES table where each individual reading assignment/signature is given a responsibility. For simplicity sake, let's say the only three roles for employees are Employee, Scientific, and Management. Right now, I am creating a value list where only certain employees are displayed based on their role and the responsibility designated to the signature assignment, so if a signature assignment is designated as "Scientific" responsibility, only those employees classified with a role of "Scientific" will appear in the value list.

       

      What I want to do, however, is make it so that if the responsibility is labeled "Employee", ALL employees (regardless of their role classification) are displayed in the value list. This way, we can have a limited value list if a signature is needed for a certain level of employee (such as Scientific), but it would still allow us the ability to have certain signatures that can be assigned to any employee, regardless of their assigned role.

       

      Here is my current ExecuteSQL statement... right now, the part highlighted in red provides matching the signature responsibility to the employee's role, but I'm not sure how to build in allowing for a responsibility of "Employee" to generate a list of all employees. Any suggestions? I have played around a bit with using a Case statement in place of the red text, but haven't been able to get it working yet.

       

      ExecuteSQL (

       

      "SELECT nameFirstLast

      FROM Employees

      WHERE role = ?

      ORDER BY nameFirstLast" ;

       

      "" ; "" ; SIGNATURES::responsibility )

       

      As always, thanks in advance for all the help!

        • 1. Re: ExecuteSQL Conditional Where Clause?
          siplus

          In a nice Let() statement, Break the SQL QUERY in parts which you build, with CASE and IF, then feed the calculated query to ExecuteSQL.

           

          Something like this:

           

           

          Let ( [

           

          whereClause = IF (SIGNATURES::responsibility = “Employee”; “ ”; “ WHERE Role = ? ”);

           

          part1 = “SELECT nameFirstLast FROM Employees” ;

          part2 = “ ORDER by nameFirstLast” ];

           

          ExecuteSQL( part1 & whereClause & part2; “”; “”; SIGNATURES::responsibility)

           

          )

          • 2. Re: ExecuteSQL Conditional Where Clause?
            coherentkris

            perhaps...

             

            ExecuteSQL (

             

            "SELECT nameFirstLast

            FROM Employees

            WHERE role = ?

            ORDER BY nameFirstLast" ;

             

            "" ; "" ; "Employee" )

             

            I would also caution you because ROLE is a reserved word in SQL and queries that use reserved words and are NOT that are not properly esacpe quoted may fail.

            List of SQL reserved words | Drupal.org

            so....

             

            ExecuteSQL (

             

            "SELECT nameFirstLast

            FROM Employees

            WHERE \"role\" = ?

            ORDER BY nameFirstLast" ;

             

            "" ; "" ; "Employee" )

            • 3. Re: ExecuteSQL Conditional Where Clause?
              jakebutt14

              Thanks for the info. I'm actually using some of the custom functions others smarter than I have developed which get around the reserved words using field names and all that. I just tried to simplify it as much as possible for the sake of this discussion, but I hadn't realized that role is reserved so I appreciate the heads up.

               

              The scenario you give will work specifically for those employees assigned a role of "Employee", but I need to be able to have my value list show all employees when the responsibility is set to "Employee".

              • 4. Re: ExecuteSQL Conditional Where Clause?
                philmodjunk

                Do you really need ExecuteSQL for this at all?

                 

                I can think of ways to get your conditional value list that don't require ExecuteSQL in the first place--and that might make for a simpler solution here.

                • 5. Re: ExecuteSQL Conditional Where Clause?
                  jakebutt14

                  Love it! This worked great. I had been toying with trying to do basically the same thing when defining what my WHERE clause equaled (at the end of the ExecuteSQL function), but hadn't considered actually creating a dynamic WHERE clause itself.

                   

                  Thanks so much for the help! Can see this technique potentially being very useful in the future.

                  • 6. Re: ExecuteSQL Conditional Where Clause?
                    jakebutt14

                    Certainly interested to hear your ideas. I'm sure many other people have the same issue I do sometimes, where you get used to a certain technique and try to apply it to situations where there is a much easier/simpler solution. For generating these types of dynamic value lists, ExecuteSQL is just the way I have gotten used to thinking, particularly where I need to define conditions like in this situation.

                     

                    But I am always open to other techniques.