Home»Support»Frequently Asked Questions»AI-Generated SQL for Attribute Generator
  • Printer Friendly Version
  • Decrease Text SizeIncrease Text Size

AI-Generated SQL for Attribute Generator

AI-Generated SQL for Attribute Generator

SQL Query Requirements

Important: Your SQL query must follow these rules:

  • The query must return a single scalar value (one column, one row)
  • The query will automatically receive @DataId as a parameter containing the DataId of the record being saved
  • Do NOT define the @DataId parameter in your query - it will be provided dynamically
  • The query should be efficient and not cause performance issues

How to Use AI to Generate Your SQL

Step-by-Step Process:

  1. Open your preferred AI tool (ChatGPT, Claude, etc.)
  2. Copy one of the prompt examples below that matches your needs
  3. Paste the prompt into the AI tool
  4. Review the generated SQL
  5. Copy the SQL into the Attribute Generator's SQL Query field
  6. Test the query to ensure it works as expected

Understanding the Attributes XML Column

The Attributes column in cpsys_DataCurrent is an XML column that contains all custom attribute values for a record. It follows the CpCollection.xsd schema structure with groups, properties, and control values.

Key XML Structure:

  • /cpCollection/group - Contains attribute groups
  • /cpCollection/group/property - Individual attributes
  • /cpCollection/group/property/value - The attribute value
  • /cpCollection/group/property/@id - The attribute's system name

When prompting AI to query the Attributes column, mention that it's an XML column following this structure.

Tips for Creating Effective AI Prompts

  • Be specific about which tables and columns you want to query
  • Always mention that the query receives @DataId as a parameter and should not define it
  • Mention "scalar value" to ensure the AI returns a single value, not a result set
  • For XML queries, mention that Attributes follows the CpCollection XML rules
  • Request specific formatting if you need dates, numbers, or concatenated values in a particular format
  • Ask for error handling if you want the query to handle NULL values gracefully

Advanced Scenarios: Complex Business Rules

For complex business logic, rule-based evaluations, and comprehensive data analysis, use the SQL Boilerplate template:

📄 Download SQL Boilerplate.txt

The boilerplate provides a complete framework for advanced scenarios including taxonomy resolution, role evaluation, keyword matching, and custom business rules with AI-assisted development.

Sample Prompt Template

General Template You Can Customize:

Write a SQL query for CentralPoint that [describe what you want to accomplish]. Query the cpsys_DataCurrent table and return [describe the output you want] as a scalar string. The query will receive @DataId as a parameter containing the DataId of the current record (don't define this parameter in the query). [Add any special formatting or logic requirements].

Testing Your Generated SQL

Before using in production:

  1. Test the query with a sample @DataId value in the Report Builder
  2. Verify it returns exactly one value (not multiple rows or columns)
  3. Check that it handles NULL values appropriately
  4. Ensure it performs well (doesn't take too long to execute)
  5. Test with different record types to ensure consistency

Need Help?

If the AI-generated SQL doesn't work as expected:

  • Try rephrasing your prompt with more specific details
  • Ask the AI to explain what the query does
  • Request modifications to the generated SQL
  • Ask the AI to add comments explaining each part of the query
  • For complex scenarios, consider using the SQL Boilerplate template linked above