I use two cases in below query to get results of daction & ApprovalType. While running the below query in Oracle SQL creates two new temp columns as daction_1 & ApprovalType _1 for daction & ApprovalType columns respectively. Now, i want to use these keywords in my IDOC code but since temp columns are created I'm not able to use them. How to resolve this?
SELECT WH.dActionDate,
WH.xWF_SendTo,
WH.dAction,
ATY.ApprovalType,
WH.xWorkflowComments,
CASE
WHEN NVL(ApprovalType,'') IS NULL
THEN xPurposeForRejection
ELSE ApprovalType
END AS ApprovalType,
CASE
WHEN NVL(dAction,'') ='SendTo'
AND NVL(ApprovalType,'') IS NOT NULL
OR NVL(dAction,'') ='Approve'
THEN 'Approve'
ELSE 'Reject'
END AS dAction
FROM WorkflowHistory WH,
Reason Re,
ApprovalType ATY
WHERE UPPER(dDocName) = UPPER('D_1239178')
AND xPurposeForSubmission = Re.ReasonID(+)
AND xDocApproval = ATY.ApprovalTypeID(+)
AND (dAction IN('Reject','Approve')
OR (dAction ='SendTo'
AND ApprovalType IS NOT NULL))
AND ROWNUM <= 5
ORDER BY dActionDate DESC,
dActionMillis
Looking at column names I assume that
Idoc
is script language used in Oracle Webcenter Content Server (formerly Oracle UCM). I don't have a running instance on-hand to check, but I am pretty sure that you can create database view with columns you need and then see it as regular table in Configuration Manager. Following link provides some details of configuration process. The other option is to move your logic to Idoc code itself although it may be less performant.