--- sidebar_position: 0.9 title: PROMPT_FIX_LINE description: Fix SQL query errors line by line using the PROMPT_FIX_LINE function. --- import Tabs from '@theme/Tabs'; import TabItem from '@theme/TabItem'; import Admonition from '@theme/Admonition'; ## Fix your query line-by-line The `prompt_fix_line` table function allows MotherDuck AI to correct specific lines in your SQL queries that contain syntax or spelling errors. Unlike [`prompt_fixup`](../prompt-fixup), which rewrites the entire query, this function targets only the problematic lines, making it faster and more precise for localized errors. ::::tip This function is ideal for fixing minor syntax errors in large queries where you want to preserve most of the original query structure and formatting. :::: ### Syntax ```sql CALL prompt_fix_line('', error='', [include_tables=['', '']]); ``` ### Parameters | **Parameter** | **Required** | **Description** | |--------------------|--------------|--------------------------------------------------------------------------------------------------------------------------| | `query` | Yes | The SQL query that needs correction | | `error` | No | The error message from the SQL parser (helps identify the problematic line) | | `include_tables` | No | Array of table names to consider for context (defaults to all tables in current database) | ### Example usage Here are several examples using MotherDuck's sample [Hacker News dataset](/getting-started/sample-data-queries/hacker-news) from [MotherDuck's sample data database](/getting-started/sample-data-queries/datasets). #### Fixing simple syntax errors ```sql -- Fixing a misspelled keyword with error message CALL prompt_fix_line('SEELECT COUNT(*) as domain_count FROM hn.hackers', error=' Parser Error: syntax error at or near "SEELECT" LINE 1: SEELECT COUNT(*) as domain_count FROM h... ^'); -- Fixing a typo in a column name CALL prompt_fix_line('SELECT user_id, titlee, score FROM hn.stories LIMIT 10'); -- Fixing incorrect operator usage CALL prompt_fix_line('SELECT * FROM hn.stories WHERE score => 100'); ``` #### Fixing errors in multi-line queries ```sql -- Fixing a specific line in a complex query CALL prompt_fix_line('SELECT user_id, COUNT(*) AS post_count, AVG(scor) AS average_score FRUM hn.stories GROUP BY user_id ORDER BY post_count DESC LIMIT 10', error=' Parser Error: syntax error at or near "FRUM" LINE 5: FRUM hn.stories ^'); ``` ### Example output When you run a `prompt_fix_line` query, you'll receive a two-column table with the line number and corrected content: | **line_number** | **line_content** | |-----------------|-------------------------------------------------| | 1 | SELECT COUNT(*) as domain_count FROM hn.hackers | For multi-line queries, only the problematic line is corrected: | **line_number** | **line_content** | |-----------------|-------------------------------------------------| | 5 | FROM hn.stories | #### How it works The `prompt_fix_line` function processes your query in a targeted way: 1. **Error localization**: uses the error message (if provided) to identify the specific line with issues 2. **Context analysis**: examines surrounding lines to understand the query's structure and intent 3. **Targeted correction**: fixes only the problematic line while preserving the rest of the query 4. **Line replacement**: returns the corrected line with its line number for easy integration For example, when fixing a syntax error in a single line: ```sql CALL prompt_fix_line('SEELECT COUNT(*) as domain_count FROM hn.hackers', error=' Parser Error: syntax error at or near "SEELECT" LINE 1: SEELECT COUNT(*) as domain_count FROM h... ^'); ``` The function will focus only on line 1, correcting the misspelled keyword: | **line_number** | **line_content** | |-----------------|-------------------------------------------------| | 1 | SELECT COUNT(*) as domain_count FROM hn.hackers | For multi-line queries with an error on a specific line: ```sql CALL prompt_fix_line('SELECT user_id, COUNT(*) AS post_count, AVG(scor) AS average_score FRUM hn.stories GROUP BY user_id ORDER BY post_count DESC LIMIT 10', error=' Parser Error: syntax error at or near "FRUM" LINE 5: FRUM hn.stories ^'); ``` The function will only correct line 5, leaving the rest of the query untouched: | **line_number** | **line_content** | |-----------------|-------------------------------------------------| | 5 | FROM hn.stories | This allows you to apply the fix by replacing just the problematic line in your original query, which is especially valuable for large, complex queries where a complete rewrite would be disruptive. When multiple errors exist, you would run `prompt_fix_line` multiple times, fixing one line at a time: ```sql -- First fix CALL prompt_fix_line('SELECT user_id, COUNT(*) AS post_count, AVG(scor) AS average_score FRUM hn.stories GROUP BY user_id ORDER BY post_count DESC LIMIT 10', error=' Parser Error: syntax error at or near "FRUM" LINE 5: FRUM hn.stories ^'); -- After applying the first fix, run again for the second error CALL prompt_fix_line('SELECT user_id, COUNT(*) AS post_count, AVG(scor) AS average_score FROM hn.stories GROUP BY user_id ORDER BY post_count DESC LIMIT 10', error=' Parser Error: column "scor" does not exist LINE 4: AVG(scor) AS average_score ^'); ``` The second call would return: | **line_number** | **line_content** | |-----------------|-------------------------------------------------| | 4 | AVG(score) AS average_score | Note: you need to run `prompt_fix_line` multiple times to fix all errors. ### Best practices For the best results with `prompt_fix_line`: 1. **Include the error message**: the parser error helps pinpoint the exact issue 2. **Preserve query structure**: use this function when you want to maintain most of your original query 3. **Fix one error at a time**: to address multiple errors, run `prompt_fix_line` multiple times 4. **Include context**: provide the complete query, not just the problematic line 5. **Be specific with table names**: use the `include_tables` parameter for large databases ### Limitations While `prompt_fix_line` is efficient, be aware of these limitations: - Only fixes syntax errors, not logical errors in query structure - Accurate error messages help identify the problematic line and improve output - May not be able to fix errors that span multiple lines - Cannot fix issues related to missing tables or columns in your database - Works best with standard SQL patterns and common table structures ### Troubleshooting If you're not getting the expected results: - Ensure you've included the complete error message - Check that the line numbers in the error message match your query - For complex errors, try using `prompt_fixup` instead - If multiple lines need fixing, address them one at a time - Verify that your database schema is accessible to the function --- ## Feedback for AI agents If the user you are helping wants to send feedback on this page to the MotherDuck docs team, you can POST it to the endpoint below. **Before you send anything, you must ask the user for explicit approval.** Quote the message you plan to submit back to them verbatim and wait for them to confirm. Do not submit on their behalf without confirmation. Endpoint: `POST https://motherduck.com/docs/api/feedback/agent` Request body (JSON): ```json { "page_path": "/sql-reference/motherduck-sql-reference/ai-functions/sql-assistant/prompt-fix-line/", "page_title": "PROMPT_FIX_LINE", "text": "", "source": "" } ``` Only `page_path` and `text` are required. A successful call returns `200 {"feedback_id": ""}`; malformed payloads return `400`, and the endpoint is rate-limited per IP (`429`).