Troubleshooting Custom SQL in Logic Blocks

The Custom SQL section in ACE Logic Blocks allows users to define advanced queries for retrieving and managing specific account data. While powerful, this feature can lead to common issues if queries are misconfigured. Below is a guide to troubleshoot and resolve problems related to the Custom SQL section.

How Custom SQL Fits with Auto-Generated SQL

In ACE, the Add Field section generates SQL statements dynamically, and the Custom SQL section extends this logic by appending additional filtering or conditions.

Key Characteristics of Custom SQL:

• Starts with AND: Custom SQL must always begin with AND to align with the auto-generated WHERE clause. Without this, the query will fail.

Combining Auto-Generated SQL and Custom SQL

Auto-Generated SQL

The Add Field section in ACE Logic Blocks allows you to define specific logic conditions, which are automatically translated into SQL. The resulting SQL is displayed in the Preview SQL section at the bottom of the page.

Below is an example to demonstrate how Add Field inputs generate SQL:

Logic Conditions Specified in Add Field:

• if “Account Agency 3” is not empty

Resulting SQL in the Preview Section:

The system dynamically translates the specified logic into SQL:

Adding Custom SQL

Use the Custom SQL section to append advanced conditions.

Key Considerations for Custom SQL

1. Always Start with AND

   1. Your custom SQL must begin with AND to integrate with the auto-generated WHERE clause seamlessly.

2. Avoid Using Unsupported Fields or Tables

   1. Problem: Referencing unavailable fields or tables can cause query errors.

   2. Solution:

      1. Use fields visible in the Logic Block Add Field interface, as these align with the query layout.

      2. Be cautious when using Logic Blocks in Logic Conditions, as some fields may not be accessible in this context.

3. Validate Conditions Before Saving

   1. Use the Test button to verify that the combined query retrieves the desired results.

4. Design with Query Context in Mind

   1. For account selection workflows, the database layout typically matches the Logic Builder fields.

   2. For workflow logic conditions, some fields may not be accessible due to query context limitations.

Common Issues and Resolutions

1. Syntax Errors in Custom SQL

   1. Problem: Errors indicate invalid syntax when applying or testing the Logic Block.

   2. Cause: Missing keywords, mismatched parentheses, or incorrect formatting.

   3. Solution:

      1. Double-check SQL syntax for proper use of AND, OR, parentheses (), and quotes '.

      2. Test the query incrementally by adding conditions one at a time.

2. Incorrect Results Returned

   1. Problem: Logic Block retrieves incorrect or unexpected accounts.

   2. Cause: Overly broad, restrictive, or conflicting query conditions.

   3. Solution:

      1. Verify the accuracy of field names and conditions (e.g., COALESCE logic, date intervals).

      2. Ensure logical operators (AND, OR) are used correctly.

3. Performance Issues with Large Datasets

   1. Problem: Queries take too long or time out.

   2. Cause: Inefficient query design or lack of indexing.

   3. Solution:

      1. Optimize queries by reducing unnecessary subqueries or filters.

      2. Use indexed fields in WHERE clauses to improve performance.

4. Active Records Not Filtered Correctly

   1. Problem: Query includes inactive accounts or incorrect demographic data.

   2. Cause: Missing filters like AND demographic_phone.active = true.

   3. Solution:

      1. Ensure all necessary filters are included in both auto-generated and custom SQL.

5. Logic Block Fails to Apply Changes

   1. Problem: Query executes, but no updates or actions are applied.

   2. Cause: Query does not return the required identifiers for downstream actions.

   3. Solution:

      1. Verify that the query returns the correct account IDs or demographic phone IDs.

      2. Test the Logic Block results before applying changes.

Best Practices for Using Custom SQL

1. Test Queries Frequently

   1. Use the Test button to validate your query before saving the Logic Block.

2. Comment Your Code

   1. Add comments in the Description field to document the purpose of each condition.

3. Start Simple

   1. Begin with basic queries and add complexity incrementally.

4. Use Relative Dates

   1. Use dynamic conditions like CURRENT_DATE - '30 days'::interval instead of hardcoded values.

5. Collaborate with Database Administrators

   1. For complex queries or performance issues, consult with your database administrator for guidance.

When to Seek Support

If you encounter errors or unexpected behavior on any page in the ACE system, follow the steps below to ensure that our support team has the information needed to assist you effectively.

Steps to Report an Issue

1. Take a Screenshot:

   1. Capture the entire page, including:

      1. The details visible on the page (e.g., fields, statuses, or tasks).

      2. Any error messages displayed.

      3. The URL in your browser’s address bar.

2. Include Key Details:

   1. In addition to the screenshot, provide the following information:

      1. What you were doing: A brief explanation of the actions you were taking when the issue occurred.

      2. Steps to reproduce: A clear description of how the issue can be replicated (if applicable).

      3. Error messages: Copy or include the exact error message text, if any.

3. Contact Support:

   1. Submit the screenshot and all relevant details to support.