The following contains example scenarios for summarizing loan data and troubleshooting query errors. Each scenario includes a sample Cortex Code input, SQL example, and guidance for resolving common issues.
Scenario: Loan Count by Loan Type
Use this scenario to summarize loan volume by loan type, which can be useful for reporting, portfolio analysis, or validating data distribution.
Cortex Code input:
Write an SQL query that groups results by lien1 loan type and returns the total number of loans for each loan type.
SQL example:
SELECT
lien1_loan_type,
COUNT(*) AS total_loans
FROM
XXXX
WHERE
NOT lien1_loan_type IS NULL
GROUP BY
lien1_loan_type
ORDER BY
total_loans DESC;
Scenario: Common SQL Errors and Troubleshooting
Use this section to troubleshoot common SQL issues that may cause queries to return incorrect results or no results at all.
Missing Comma Errors:
A missing comma in the SELECT statement can cause columns to populate incorrectly. This often happens when manually adding fields.
Example issue:
SELECT
address,
city,
zipcode,
state
lien1_loan_type,
lien1_interest_rate_used,
lien1_amount,
lien1_contract_date
FROM
prod_bulk_property_data
WHERE
lien1_loan_type ILIKE '%VA%'
AND state ILIKE '%WA%'
AND lien1_interest_rate_used >= 7.0
AND lien1_contract_date BETWEEN '2023-10-01'
AND '2023-10-31'
ORDER BY
lien1_interest_rate_used DESC;
How to fix:
Paste the full query into Copilot and ask it to identify and fix the syntax error or edit the SQL and input a comma to where it is needed.
Incorrect or Missing FROM Statement
If a query returns an error or no results, the issue may be related to the FROM statement, which defines which database or table the query is searching.
How to fix
Paste your full query into Copilot and ask it to fix the FROM statement to ensure it is referencing the correct database and table.
This is especially helpful when:
Copying queries between environments
Modifying existing SQL
Building queries step-by-step in Copilot
Logic and Filtering Errors
Logic errors typically occur when filters unintentionally remove all results.
Example issue:
SELECT
address,
city,
zipcode,
state,
lien1_loan_type,
lien1_interest_rate_used,
lien1_amount,
lien1_contract_date
FROM
prod_bulk_property_data
WHERE
lien1_loan_type ILIKE '%VA%'
AND state ILIKE '%Washington%'
AND lien1_interest_rate_used >= 7.0
AND lien1_contract_date BETWEEN '2023-10-01'
AND '2023-10-31'
ORDER BY
lien1_interest_rate_used DESC;
If the data uses state abbreviations (e.g., WA), this filter will exclude all rows.
How to fix
Temporarily remove filters one at a time
Add filtered fields to the SELECT statement to inspect values
Confirm expected formats before reapplying conditions