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.