sql - 我的 Group BY 语句 00904.00000 - "%s: invalid identifier" 中出现以下错误，我该如何解决？

Question

我收到下一个错误，我猜是因为group by声明：

ORA-00904: "PLANNING_ACTION"."OBJECT_ID": identificador no válido 00904. 00000 - "%s: invalid identifier" *Cause:
*Action: Error en la linea: 78, columna: 5

我已经检查过，如果我在没有 group by 语句的情况下运行查询，则查询有效，但我得到了很多重复值。

这是我的代码

select 
    TO_CHAR(PA.OBJECT_ID) as PA_OBJECTID,
    TIPO.NAME AS TIPO_PA,
    PA.NAME as PA_NAME,
    PA.PLANNING_STATUS as PLANNING_STATUS,
    PA.CREATE_WHEN as PA_CREATE_WHEN,
    PA.CREATED_BY AS PA_CREATED_BY,
    PA.MODIFIED_WHEN AS PA_MODIFIED_WHEN,
    PA.MODIFIED_BY AS PA_MODIFIED_BY,
    TO_CHAR(PA.PROJECT) AS PROJECT_OBJECTID,
    TO_CHAR(PA.SUB_PROJECT) AS SUB_PROJECT_OBJECTID,
    PA.EXP_END_DATE AS PA_EXP_END_DATE,
    TO_CHAR(PAO.VALUE) AS OBJETOPA_OBJECT_ID,
    PAO.SHOW_ORDER AS N_OBJETO_PA,

CASE WHEN  DEV.OBJECT_ID IS NOT NULL 
    THEN 'Device' 
    WHEN NE.OBJECT_ID IS NOT NULL
    THEN 'Network Element'
    WHEN CIR.OBJECT_ID IS NOT NULL
    THEN 'Circuit'
    WHEN CARD.OBJECT_ID IS NOT NULL
    THEN 'No encontrado'
    ELSE NULL 
    END  AS TIPO_OBJETO,

CASE WHEN DEV.OBJECT_ID IS NOT NULL 
    THEN DEV.NAME 
    WHEN NE.OBJECT_ID IS NOT NULL
    THEN NE.NAME
    WHEN CIR.OBJECT_ID IS NOT NULL
    THEN CIR.NAME
    WHEN CARD.OBJECT_ID IS NOT NULL
    THEN CARD.NAME
    ELSE 'No encontrado' 
    END AS NOMBRE_OBJETO,

CASE WHEN  DEV.OBJECT_ID IS NOT NULL OR NE.OBJECT_ID IS NOT NULL OR CIR.OBJECT_ID IS NOT NULL OR CARD.OBJECT_ID IS NOT NULL
    THEN STATUS.VALOR
    ELSE 'No encontrado'
    END  AS ESTADO_OBJETO,  
TO_CHAR(DEVPA.OBJECT_ID) AS OBJ_AF_DEVICE_OBJECT_ID,
DEVPA.SHOW_ORDER AS N_OBJ_AFECTADOS_DEVICE,
TO_CHAR(CIRPA.OBJECT_ID) AS OBJ_AF_CIRCUITO_OBJECT_ID,
CIRPA.SHOW_ORDER AS N_OBJ_AFECTADOS_CIRCUITO,
TO_CHAR(CARMPA.OBJECT_ID) AS OBJ_AF_CARDMOVE_OBJECT_ID,
CARMPA.SHOW_ORDER AS N_OBJ_AFECTADOS_CARDMOVE,
TO_CHAR(CARSPA.OBJECT_ID) AS OBJ_AF_CARDSWAP_OBJECT_ID,
CARSPA.SHOW_ORDER AS N_OBJ_AFECTADOS_CARDSWAP
from PLANNING_ACTION PA
    LEFT JOIN PLANNING_ACTION_OBJECT PAO ON (PA.OBJECT_ID = PAO.OBJECT_ID)
    LEFT JOIN DEVICE DEV ON (PAO.VALUE = DEV.OBJECT_ID)
    LEFT JOIN DEVICE_PLANNING_ACTIONS DEVPA ON ( PA.OBJECT_ID = DEVPA.VALUE )
    LEFT JOIN NETWORK_ELEMENT NE ON (NE.OBJECT_ID = PAO.VALUE)
    LEFT JOIN CIRCUIT CIR ON (CIR.OBJECT_ID = PAO.VALUE)
    LEFT JOIN CARD CARD ON (CARD.OBJECT_ID = PAO.VALUE)
    LEFT JOIN CARD_MOVE_PLNACT_OBJECT CARMPA ON (CARMPA.OBJECT_ID = PA.OBJECT_ID)
    LEFT JOIN CARD_SWP_PLNACT_OBJECT CARSPA ON (CARSPA.OBJECT_ID = PA.OBJECT_ID)
    LEFT JOIN CIRCUIT_PLANNING_ACTIONS CIRPA ON (CIRPA.OBJECT_ID = PA.OBJECT_ID)
    LEFT JOIN NC_OBJECT_TYPES TIPO ON (PA.TYPE_ID=TIPO.OBJECT_TYPE_ID)
    LEFT JOIN VW_LISTA_VALORES STATUS ON (STATUS.CLAVE=DEV.PH_STATUS OR STATUS.CLAVE=NE.STATUS OR STATUS.CLAVE=CIR.LOGICAL_STATUS  )
group by
    PLANNING_ACTION.OBJECT_ID,
    NC_OBJECT_TYPES.NAME,
    PLANNING_ACTION.NAME,
    PLANNING_ACTION.PLANNING_STATUS,
    PLANNING_ACTION.CREATE_WHEN,
    PLANNING_ACTION.CREATED,
    PLANNING_ACTION.MODIFIED_WHEN,
    PLANNING_ACTION.MODIFIED_BY,
    PLANNING_ACTION.PROJECT,
    PLANNING_ACTION.SUB_PROJECT,
    PLANNING_ACTION.EXP_END_DATE,
    PLANNING_ACTION_OBJECT.VALUE,
    PLANNING_ACTION_OBJECT.SHOW_ORDER

order by 
    PLANNING_ACTION.OBJECT_ID;

标签： sqloracle-sqldeveloper

Answer 1

按以下方式替换您的分组依据和排序依据：

group by
    PA.OBJECT_ID,
    NC_OBJECT_TYPES.NAME,
    PA.NAME,
    PA.PLANNING_STATUS,
    PA.CREATE_WHEN,
    PA.CREATED,
    PA.MODIFIED_WHEN,
    PA.MODIFIED_BY,
    PA.PROJECT,
    PA.SUB_PROJECT,
    PA.EXP_END_DATE,
    PLANNING_ACTION_OBJECT.VALUE,
    PLANNING_ACTION_OBJECT.SHOW_ORDER

order by 
    PA.OBJECT_ID;

问题是：您有一个 PLANNING_ACTION 的别名为 PA。