0

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
Yaroslav Shabalin
  • 1,645
  • 3
  • 17
  • 29
Pulkit Bhatia
  • 127
  • 1
  • 7
  • 22
  • 3
    First, what does `IDOC` stand for? Second, you've tagged this for MySQL and Oracle. Which of the two are you really using? Third, why are you aliasing your `CASE` statements to use an alias that matches a column that you're already selecting? Why wouldn't you choose a unique alias that allows you to differentiate the two? – Justin Cave Jan 20 '14 at 20:20
  • 1
    In Oracle, an empty string is treated as Null, so Nvl(dAction,'') is the same as dAction – David Aldridge Jan 20 '14 at 21:34

1 Answers1

0

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.

Yaroslav Shabalin
  • 1,645
  • 3
  • 17
  • 29
  • Friends, thanks. I'm working on Oracle. IDOC is just another progr. language. Main issue is how can i use daction or ApprovalType variable in any other programming language – Pulkit Bhatia Jan 21 '14 at 05:56