Blog

At CloudSQL Online, our users have run millions of SQL queries. And while most of them fly through just fine, we’ve noticed that around 1 in 10 ends up throwing a syntax or runtime error. Surprising? Not really.

Our users are experts, but the SQLs they write are often big, bold, and complex—the kind of stuff that makes basic SQL editors sweat. But even for pros, one missing comma or a small logic slip can send things sideways.

Let’s see how you do.

Here’s a SQL that failed with the below error.

ORA-00918: column ambiguously defined

Can you fix this?

SELECT
     paf.person_id,
     paf.assignment_id,
     contract_id,
     pcf.type,
     CASE
          WHEN pcf.type = 'TEMP_CONT' THEN to_char (contract_end_date, 'YYYY-MM-DD')
     END contract_end_date
FROM
     per_contracts_f pcf,
     per_all_assignments_f paf
WHERE paf.primary_assignment_flag = 'Y'
     AND pcf.contract_id = paf.contract_id
     AND TRUNC (SYSDATE) BETWEEN trunc (nvl (paf.effective_start_date, sysdate)) AND trunc  (nvl (paf.effective_end_date, sysdate))

Take a moment. This one’s not too bad—we think you’ll crack it quickly.
👇 Check your fix against the solution below. You guessed it right ! contract_id column needs an alias.

SELECT
     paf.person_id,
     paf.assignment_id,
     pcf.contract_id,
     pcf.type,
     CASE
          WHEN pcf.type = 'TEMP_CONT' THEN to_char (contract_end_date, 'YYYY-MM-DD')
     END contract_end_date
FROM
     per_contracts_f pcf,
     per_all_assignments_f paf
WHERE
     paf.primary_assignment_flag = 'Y'
     AND pcf.contract_id = paf.contract_id
     AND TRUNC (SYSDATE) BETWEEN trunc (nvl (paf.effective_start_date, sysdate)) AND trunc  (nvl (paf.effective_end_date, sysdate))

Feeling confident? Great. Now here’s a more complex one.

This next SQL also threw the same error. Can you debug it?

SELECT
     NVL (papf.attribute1, papf.person_number) internal_employee_id,
     papf.person_number,
     (
          SELECT
               name
          FROM
               hr_all_organization_units
          WHERE
               organization_id = papf.business_group_id
     ) business_unit_name,
     pv.segment1 supplier_num,
     pv.creation_Date supplier_Creation_date,
     pvs.vendor_site_code,
     ai.invoice_date,
     ai.invoice_num,
     decode (
          ai.invoice_currency_code,
          'USD',
          'Domestic',
          'International'
     ) dom_or_international,
     ai.invoice_amount,
     ai.description
FROM
     ap_invoices ai,
     poz_suppliers pv,
     poz_supplier_sites_all_m pvs,
     per_all_people_f papf,
     hz_parties hp
WHERE
     1 = 1
     AND ai.vendor_id = pv.vendor_id
     AND pv.vendor_id = pvs.vendor_id
     AND hp.party_id = pv.party_id
     AND ai.vendor_site_id = pvs.vendor_site_id
     AND ai.invoice_type_lookup_code = 'EXPENSE REPORT'
     AND ai.payment_status_flag = 'N'
     AND ap_invoices_pkg.get_approval_status (
          ai.invoice_id,
          ai.invoice_amount,
          ai.payment_status_flag,
          ai.invoice_type_lookup_code
     ) = 'APPROVED'
     AND pv.employee_id = papf.person_id
     AND trunc (ai.invoice_date) BETWEEN trunc (effective_start_date) AND trunc  (effective_end_date)

This one’s a bit trickier, but we know you’ve got the skills. With a little patience (and a strong coffee maybe), you’ll get there.

But here’s the thing…

Even with all the talent in the world, debugging long SQLs eats up time—time that could be better spent building, analyzing, and solving real business problems. That got us thinking…

What if you didn’t have to fix it alone?

That’s why we built FixWithAI—an AI-powered feature built right into CloudSQL.

Now, whenever you hit an error, just click the FixWithAI button that pops up next to it. AI will analyze the query, understand the error, and hand it off to a smart AI agent tailored to help with that kind of issue.

Let’s see what it came up with:

Explanation Added table alias ‘papf’ to effective_start_date and effective_end_date columns to resolve ORA-00918 (column ambiguously defined) error

Optimized SQL
SELECT
     NVL (papf.attribute1, papf.person_number) internal_employee_id,
     papf.person_number,
     (
          SELECT
               name
          FROM
               hr_all_organization_units
          WHERE
               organization_id = papf.business_group_id
     ) business_unit_name,
     pv.segment1 supplier_num,
     pv.creation_Date supplier_Creation_date,
     pvs.vendor_site_code,
     ai.invoice_date,
     ai.invoice_num,
     decode (
          ai.invoice_currency_code,
          'USD',
          'Domestic',
          'International'
     ) dom_or_international,
     ai.invoice_amount,
     ai.description
FROM
     ap_invoices ai,
     poz_suppliers pv,
     poz_supplier_sites_all_m pvs,
     per_all_people_f papf,
     hz_parties hp
WHERE
     1 = 1
     AND ai.vendor_id = pv.vendor_id
     AND pv.vendor_id = pvs.vendor_id
     AND hp.party_id = pv.party_id
     AND ai.vendor_site_id = pvs.vendor_site_id
     AND ai.invoice_type_lookup_code = 'EXPENSE REPORT'
     AND ai.payment_status_flag = 'N'
     AND ap_invoices_pkg.get_approval_status (
          ai.invoice_id,
          ai.invoice_amount,
          ai.payment_status_flag,
          ai.invoice_type_lookup_code
     ) = 'APPROVED'
     AND pv.employee_id = papf.person_id
     AND trunc (ai.invoice_date) BETWEEN trunc (papf.effective_start_date) AND trunc (papf.effective_end_date) -- Added table alias to resolve ambiguous column

Pretty cool, right? Lightning-fast results—and they make sense.

At CloudSQL, we believe the best outcomes happen when your expertise meets the power of AI—backed by the insights we’ve built into our platform over years of working with Oracle Cloud developers.

Ready to try it yourself?

You can start using FixWithAI today in the CloudSQL Desktop app—at no extra cost.

👉 Click here to sign up and get started.

Go on—give it a shot. You might just fall in love with debugging all over again.