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.
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.