Error in Ontology SQL Tool during AIP Logic LLM Calls

Ask the Community
1

I am trying to use the LLM in AIP Logic with the Ontology SQL Tool. My objective is to make the AI Agent do a quick analysis about an ontology type. However, the Ontology SQL Tool produces wrong SQL, and the error message is unclear:

SQL Syntax Error: Unknown SQL parsing error at line -1, column -1 near: SELECT `employeeId` AS `employeeId`,
       SUM(`duration`)/3600.0 AS `total_hours`,
       SUM(IF(`date` IN ('2026-01-25','2026-01-31','2026-02-01','2026-02-07','2026-02-08','2026-02-14','2026-02-15','2026-02-21'), `duration`, 0))/3600.0 AS `weekend_hours`
FROM `TimesheetItems`
WHERE `date` >= '2026-01-25' AND `date` <= '2026-02-21'
  AND `employeeId` IN (1,2,3)
GROUP BY `employeeId`
ORDER BY `employeeId`; (dialect: SPARK)

In one case, the same error “Unknown SQL parsing” happened 3 times in a row.

My current prompt is:

Generate a summary of the team's effort over the last 4 weeks for a weekly report to the team lead. Consider 4 weeks from the last Sunday.

**Employee IDs:**
 
{{ids}

 **Output Format:**
   - Provide a professional Markdown report for the Team Lead.
   - Do NOT include the SQL code or technical query details.
   - Include a "Summary of Effort" and an "Alerts" section (Flagging who worked on weekends, and who worked more than 170 hours or less than 150 hours).
   - Be brief, omit unnecessary messages.
   - Consider that your output will be used in a PDF document, so you should not ask for follow ups like "If you’d like, I can...".

**SQL syntax errors**:
    - Non-existent functions: "strftime", "DATE(<CHARACTER>)".
    - Do not escape characters with "\".

How can I better describe my prompt so that it avoids these SQL errors? I tried to put some errors in the prompt, but I couldn’t understand how to avoid this one that I showed.

2

Hey Igor,

Just a few ideas that spring to mind. There are some functions which are not supported in Ontology SQL. I am not sure if the IF() operation is supported for example. The key difference is that Ontology SQL is actually backed by Spark SQL.

Maybe worth trying to specify in your prompt that the AIP Logic should use Spark/Ontology SQL functions? Also, do you know which LLM you are using under the hood?

Hope that helps and happy to keep iterating on your prompt if that doesn’t improve it!

1 Like
3

After many iterations, my agent is working correctly. I am not sure what really helped, I think there were some input string variables that were coming from an object set and were not well formatted. It would be great if the error message were more informative. In addition, saying “You must be ANSI compliant” also seemed to help.

@1bb5123f1d2ddabc24bd thanks for your response! I see that Spark SQL does have the if function though. I am using GPT-5 mini.