生产订单表与不合格物料表LEFT JOIN关联失效问题求助

Hey Elena, let's walk through why your query might be failing and fix it step by step:

Possible Root Causes & Fixes #

1. Misplaced Filter Conditions #

You added PM.CODIGO_EMPRESA = '01' and PM.FECHA_FABRICACION > TO_DATE(...) directly in the ON clause of your LEFT JOIN. While this isn’t a strict syntax error, it can distort your LEFT JOIN behavior (effectively turning it into an INNER JOIN for filtered records) and may confuse the query parser. These filters should live in the WHERE clause since they target your main production view.

2. Column Name Mismatches #

Double-check that your join columns exist exactly as written:

• Confirm PR.CODIGO_MAQUINA is the correct machine code column name in P_INFO_RECHAZOS (it might match the view’s COD_MAQUINA instead of using CODIGO_ prefix).
• Verify PR.FASE is the right column for production phase in the rejection table (it could be named FASE_REALIZADA to align with the view).

3. Syntax Edge Cases #

Your DECODE function looks syntactically valid, but ensure all quote marks are half-width ASCII quotes (full-width Unicode quotes can break query parsing).

Corrected SQL Query #

Here’s the cleaned-up version with proper filter placement and readability:

SELECT 
    PM.FECHA_FABRICACION,
    PM.ORDEN_DE_FABRICACION,
    PM.CODIGO_FAMILIA,
    PM.CODIGO_ARTICULO,
    PM.COD_MAQUINA,
    DECODE(
        PM.COD_MAQUINA,
        'AN001', 'ANODIZADO',
        'GR001', 'ANODIZADO',
        'ES001', 'ANODIZADO',
        'PU001', 'ANODIZADO',
        'ZZ141', 'ANODIZADO',
        PM.COD_MAQUINA
    ) AS MAQUINA_PARTE,
    PM.DESC_MAQUINA,
    PM.CANTIDAD_ACEPTADA,
    PM.M2_ACEPTADOS,
    PM.M2_CONPEPTO,
    PM.M2_TOTAL,
    PM.M2_EXT,
    PM.KILOS_ACEPTADOS,
    PM.BARRAS_ACEPTADAS,
    PM.FASE_REALIZADA,
    PR.CODIGO_DEFECTO,
    PR.CANTIDAD_RECHAZADA,
    PR.LONGITUD,
    PR.KILOS_RECHAZADOS,
    PR.OBSERVACIONES
FROM ST_VW_PRODUCCION_MAQUINAS PM
LEFT JOIN P_INFO_RECHAZOS PR
    ON PM.CODIGO_EMPRESA = PR.CODIGO_EMPRESA
    AND PM.ORDEN_DE_FABRICACION = PR.ORDEN_DE_FABRICACION
    AND PM.CODIGO_ARTICULO = PR.CODIGO_ARTICULO
    AND PM.COD_MAQUINA = PR.CODIGO_MAQUINA
    AND PM.FASE_REALIZADA = PR.FASE
    -- Uncomment below if you need to align rejection dates with production dates
    -- AND TRUNC(PR.FECHA_RECHAZO) = TRUNC(PM.FECHA_FABRICACION)
WHERE 
    PM.CODIGO_EMPRESA = '01'
    AND PM.FECHA_FABRICACION > TO_DATE('04/07/2022 00:00:00', 'DD/MM/YYYY HH24:MI:SS');

Additional Checks to Rule Out Other Issues #

• Permissions: Confirm you have SELECT access to both ST_VW_PRODUCCION_MAQUINAS and P_INFO_RECHAZOS.
• Date Literal Simplification: If you hit date-related errors, use Oracle’s native date literal syntax (DATE '2022-07-04') instead of TO_DATE for more reliability.
• Duplicate Column Names: Ensure no selected columns have identical unqualified names across both tables (your query uses qualified names, so this is unlikely, but worth verifying).

内容的提问来源于stack exchange，提问作者Elena Relinque Macías