sqloracle-databasesubqueryora-01722

ORA-01722: invalid number throws in subquery


I have a query as per following:

SELECT 
  WAH.ALERT_KEY AS ALERT_KEY, 
  TRUNC(WAH.EVENT_DATE) AS ALERT_DATE, 
  WAH.AT_DATASOURCE_ID AS DATA_SOURCE_ID, 
  WAH.CHECK_DEFINITION_ID AS CHECK_ID, 
  OU.ID AS ORGUNIT_ID, 
  USR.ID AS USER_ID, 
  GRP.ID AS GROUP_ID, 
  CUS.SOURCE_SYSTEM AS SOURCE_SYTEM 
FROM 
  WLM_ALERT_HEADER WAH, 
  CHECK_DEFINITION CD, 
  ORGANIZATION_UNITS OU, 
  USERS USR, 
  GROUPS GRP, 
  CUSTOMERS CUS, 
  WORKFLOW_WORKITEM WW 
WHERE 
  WAH.ALERT_KEY in (
    select 
      ENTITY_KEY 
    from 
      WORKFLOW_WORKITEM ww 
    where 
      ww.STATUS_ID in (
        select 
          ID 
        from 
          WORKFLOW_STATUSES ws 
        where 
          ws.CODE IN (
            'S_GE_WLM_WL_001', 'S_GE_WLM_WL_002', 
            'S_GE_WLM_WL_003', 'S_GE_WLM_WL_004', 
            'S_GE_WLM_WL_007', 'S_GE_WLM_WL_010'
          ) 
          and ww.ORGUNIT_ID in (
            select 
              ou.ID 
            from 
              ORGANIZATION_UNITS ou 
            where 
              ou.ID = (
                select 
                  cus.ORGUNIT_ID 
                from 
                  CUSTOMERS cus 
                where 
                  cus.CUSTOMER_ID = wah.CUSTOMER_ID
              )
          )
      ) 
  ) 
  AND WAH.CHECK_DEFINITION_ID = CD.ID 
  AND USR.ID = WW.ASSIGNED_TO 

  AND GRP.ID IN (
    select 
      GROUP_ID 
    from 
      USER_GROUP_RELATIONS 
    where 
      USER_ID = USR.ID
  ) 
  AND WW.ENTITY_NAME = 'WLM Alert';

ORA-01722 is thrown for above query, unless I put these 2 lines:

AND WAH.CHECK_DEFINITION_ID = CD.ID 
AND USR.ID = WW.ASSIGNED_TO

inside the right parenthesis of subquery WAH.ALERT_KEY in

However, this shouldn't be the way as these 2 lines are not part of the subquery. I wonder which field in the subquery complain about conversion failure.


Solution

  • Well, the error message relates to one of your "subqueries" all of which have been used to "join" the tables

    WHERE wah.ALERT_KEY IN ( SELECT ENTITY_KEY FROM WORKFLOW_WORKITEM ww
    WHERE ww.STATUS_ID IN ( SELECT ID FROM WORKFLOW_STATUSES ws
    AND ww.ORGUNIT_ID IN ( SELECT ou.ID FROM ORGANIZATION_UNITS ou
    AND grp.ID IN ( SELECT GROUP_ID FROM USER_GROUP_RELATIONS
    

    One of those comparisons involves converting a string to a number

    Prior to the where clause there are no subqueries:

    SELECT
          wah.ALERT_KEY           AS alert_key
        , TRUNC(wah.EVENT_DATE)   AS alert_date
        , wah.AT_DATASOURCE_ID    AS data_source_id
        , wah.CHECK_DEFINITION_ID AS check_id
        , ou.ID                   AS orgunit_id
        , usr.ID                  AS user_id
        , grp.ID                  AS group_id
        , cus.SOURCE_SYSTEM       AS source_sytem
    FROM WLM_ALERT_HEADER wah
       , CHECK_DEFINITION cd
       , ORGANIZATION_UNITS ou
       , USERS usr
       , GROUPS grp
       , CUSTOMERS cus
       , WORKFLOW_WORKITEM ww
    

    However there is a desperate need to use "explicit joins" instead of those commas.