New to the tool.
Trying to start with a complex query as I am good in SQL.
Trying to get Change Requests that are Not finalized (Approved/Rejected). Can join USMD120C and USMD1213, but not "GET_BP.USMDKBPBP_HEADER = CR_TO_BPKEY.USMD_VALUE" due to data type mismatch. Is it possible to include type conversions in joins?
SELECT "/1MD/BPBP_HEADER" AS BP_ID,
CR.USMD_CREQUEST AS CR,
CR.USMD_CREQ_TYPE AS CR_TYPE,
CR.USMD_CREQ_STATUS AS CR_STATUS,
CR.USMD_CREATED_BY AS CR_CREATED_BY,
TO_DATE(CR.USMD_CREATED_AT) AS CR_CREATION_DT,
CR.USMD_CHANGED_BY AS CR_CHANGED_BY,
TO_DATE(CR.USMD_CHANGED_AT) AS CR_CHANGED_DT,
CR.USMD_RELEASED_BY AS CR_RELEASED_BY,
TO_DATE(CR.USMD_RELEASED_AT) AS CR_RELEASED_DT,
CR.USMD_REASON AS CR_REASON,
CR.USMD_REASON_REJ AS CR_REJECT_REASON,
CR_TO_BPKEY.USMD_VALUE AS CR_TO_BP_KEY
FROM
USMD120C CR,
USMD1213 CR_TO_BPKEY,
"/1MD/MD______004" GET_BP
where
"/1MD/BPBP_HEADER" like '006%'
AND
CR.USMD_CREQUEST = CR_TO_BPKEY.USMD_CREQUEST
AND CR_TO_BPKEY.USMD_ENTITY='BP_HEADER'
AND CR_TO_BPKEY.USMD_ENTITY_OBJ='BP_HEADER'
AND GET_BP.USMDKBPBP_HEADER = CR_TO_BPKEY.USMD_VALUE
AND CR.USMD_CREQ_STATUS NOT IN ('05','06')
------------------------------
Guna Subramanian
Energy Queensland Limited
------------------------------