如何使目标SQL查询生效？ORA-01747错误排查求助

Hey there! Let's work through this problem together—first fixing that annoying ORA-01747 error, then building the query you need to find those perfect course combinations.

First: Fixing the ORA-01747 Error #

This error almost always boils down to syntax issues with column names, table names, or aliases. Here are the most common culprits:

• You're using an Oracle reserved word (like USER, DATE, TABLE) as a table/column/alias name. For example, if your table is named USER, you need to wrap it in double quotes: "USER".
• Your column or table name has spaces or special characters, but you didn't enclose it in double quotes (e.g., pos code needs to be "pos code").
• You messed up column references—like forgetting a table alias prefix, or adding extra dots where they don't belong (e.g., employee.pos_code.id instead of employee.pos_code).

Double-check your query for any of these issues first—it's likely the root of the error.

Building the Query to Find Course Combinations #

Let's assume you have these core tables (adjust names/fields to match your actual schema):

• employee_skills: Tracks skills an employee already has (fields like emp_id, skill_id)
• pos_requirements: Lists skills needed for a job role (fields like pos_code, skill_id)
• course_skills: Maps courses to the skills they teach (fields like course_id, skill_id)
• courses: Stores course details (fields like course_id, cost)

Here's a sample query that meets your requirements: it finds 1-3 course combinations that cover all missing skills for a target employee and role, sorted by total cost:

WITH emp_missing_skills AS (
    -- Step 1: Get all skills the employee is missing for the target role
    SELECT pr.skill_id
    FROM pos_requirements pr
    LEFT JOIN employee_skills es 
        ON pr.skill_id = es.skill_id 
        AND es.emp_id = :target_emp_id -- Replace with your employee ID or bind variable
    WHERE pr.pos_code = :target_pos_code -- Replace with your target role code
        AND es.skill_id IS NULL
),
course_combinations AS (
    -- Step 2: Generate all valid 1-3 course combinations (avoid duplicates like A+B vs B+A)
    -- 1-course combinations
    SELECT c1.course_id AS course1, NULL AS course2, NULL AS course3,
           c1.cost AS total_cost
    FROM courses c1
    UNION ALL
    -- 2-course combinations (ensure course1 < course2 to avoid duplicates)
    SELECT c1.course_id AS course1, c2.course_id AS course2, NULL AS course3,
           c1.cost + c2.cost AS total_cost
    FROM courses c1
    JOIN courses c2 ON c1.course_id < c2.course_id
    UNION ALL
    -- 3-course combinations (ensure course1 < course2 < course3)
    SELECT c1.course_id AS course1, c2.course_id AS course2, c3.course_id AS course3,
           c1.cost + c2.cost + c3.cost AS total_cost
    FROM courses c1
    JOIN courses c2 ON c1.course_id < c2.course_id
    JOIN courses c3 ON c2.course_id < c3.course_id
),
combination_coverage AS (
    -- Step 3: Calculate how many missing skills each combination covers
    SELECT cc.course1, cc.course2, cc.course3, cc.total_cost,
           COUNT(DISTINCT cs.skill_id) AS covered_skills
    FROM course_combinations cc
    LEFT JOIN course_skills cs 
        ON cs.course_id = cc.course1 
        OR cs.course_id = cc.course2 
        OR cs.course_id = cc.course3
    GROUP BY cc.course1, cc.course2, cc.course3, cc.total_cost
),
total_missing_skills AS (
    -- Step 4: Get the total number of missing skills we need to cover
    SELECT COUNT(*) AS total_missing
    FROM emp_missing_skills
)
-- Final step: Filter combinations that cover all missing skills, sort by cost
SELECT 
    -- Format course IDs into a readable list
    CASE 
        WHEN course2 IS NULL THEN TO_CHAR(course1)
        WHEN course3 IS NULL THEN TO_CHAR(course1) || ', ' || TO_CHAR(course2)
        ELSE TO_CHAR(course1) || ', ' || TO_CHAR(course2) || ', ' || TO_CHAR(course3)
    END AS course_ids,
    total_cost
FROM combination_coverage cc
CROSS JOIN total_missing_skills tms
WHERE cc.covered_skills = tms.total_missing
ORDER BY total_cost ASC;

Key Notes for Adjusting This Query: #

• Replace :target_emp_id and :target_pos_code with actual values or use bind variables in SQL Developer.
• If any of your tables/columns use reserved words or have spaces, wrap them in double quotes (e.g., "POS Requirements" instead of POS Requirements).
• Make sure course_skills has accurate mappings between courses and the skills they teach—bad data here will throw off your results.
• The COUNT(DISTINCT cs.skill_id) ensures we don't double-count skills covered by multiple courses in the same combination.

If You Still Can't Fix the ORA-01747 Error #

If you go through the common issues and still hit the error, share your original query code! I can spot exactly where the syntax problem is and help you fix it quickly.

内容的提问来源于stack exchange，提问作者RyanP