How to use ORCHESTRATE in where clause OracleEntrise stage

rajkumar_ar82 · Post by **rajkumar_ar82** » Wed Apr 22, 2009 9:39 am

Hi All,
I want to use ORCHESTRATE in normal lookup type where clause at OracleEntrise stage , how can i use?

I wrote a code like below

SELECT COL1,COL2,COL3 FROM TAB1 WHERE
COL2=ORCHESTRATE.INPUTCOL

Am getting oracle error " invalid identifier"

Please help to solve this issue.

Note :
When i use Sparse lookup same thing is working

Thanks & Regards
Rajkumar

dsuser_cai · Post by **dsuser_cai** » Wed Apr 22, 2009 9:42 am

Your question is not clear, but to my understanding you are trying to do a look up and in the loop up oracle stage you are writing a query... if i understand you...

In this case you can write a normal select query without where clause. if im worng please give more detail.

rajkumar_ar82 · Post by **rajkumar_ar82** » Wed Apr 22, 2009 9:51 am

Yes, Your are correct , I want to do the look_up using oracle entriprise stage.
In that i need a where clause to filter the data based on some condition

The filtering i want to do in the query itself, In order to do that i have to pass the primary input value to the where clause

Thanks &Regards
Rajkumar

dsuser_cai · Post by **dsuser_cai** » Wed Apr 22, 2009 9:58 am

Just write a normal select query with where clause (do not include orchestrate. in this) you should be fine. let me know if this dosent work.

rajkumar_ar82 · Post by **rajkumar_ar82** » Wed Apr 22, 2009 10:12 am

dsuser_cai wrote:Just write a normal select query with where clause (do not include orchestrate. in this) you should be fine. let me know if this dosent work.

Thanks for your response,
As i mention early , i want to pass the primary input value to the where clause

Actually my query will be like below
SELECT 1 AS DUMMY,COM.COL1,COM.COL2 FROM
(SELECT 1 AS ROW_KEY ,TYPE.COL1,TYPE.COL2,TYPE.COL3,TYPE.COL4,TYPE.COL5
FROM TAB1 TYPE
WHERE SUBSTR(TRIM(TYPE.COL3),0,1)=ORCHESTRATE.COL3 AND NVL(TYPE.COL5,'#')=ORCHESTRATE.COL5
UNION ALL
SELECT 1 AS ROW_KEY ,TYPE.COL1,TYPE.COL2,TYPE.COL3,TYPE.COL4,TYPE.COL5
FROM TAB1 TYPE
WHERE SUBSTR(TRIM(TYPE.COL3),0,1)=ORCHESTRATE.COL3 AND NVL(TYPE.COL4,'#')=ORCHESTRATE.COL5
ORDER BY ROW_KEY,COL5 DESC,COL4)COM WHERE ROWNUM <2

I will pass DUMMY as key through specifiying key field in the oracle stage and also i need col3,col5 values to the query,
please advice me how can i pass this value

Thanks &Regards
Raj