Reference Query

[Aravind]

In a job I look-up a hashfile to get the values from the oracle table. The database link is a reference link. The reference query is like

select col1,col2,col3,col4,sum(col6) from tab1,
(select col1,col2,col4,max(col3) tempcol from tab1 where
substr(trim(string(col3)),1,7) = ? and col4 = ?
group by col1,col2,col4) temp1
where
tab1.col1= temp1.col1 and
tab1.col2= temp1.col2 and
tab1.col4= temp1.col4 and
tab1.col5= temp1.col5 and
tab1.col3= temp1.tempcol and
tab1.col4=? group by col1,col2,col3,col4;


In the transformer stage if i mapped col3 & col4 to the corresponding fields in the oracle stage but on running the job I am getting the error.
SQLSTATE=S1009, DBMS.CODE=0
[DataStage][SQL Client][ODBC][DataDirect][ODBC lib] Invalid argument value
My doubt is In datastage if I need to constraint on the same field twice in a reference query whether I need to map it twice in the transformer.
Thanks in advance for your help.

[Sainath.Srinivasan]

In DataStage reference, each '?' stands for one key field value passed. So if you declare 3 cols to be key, you can use 3 '?' with each taking value in order of their occurance in the SQL.