IIB: Passing local variable to ESQL select statement

Question

I need to retrieve datas from two tables based on the Job code which is given in the input xml in IIB/WMB.

SQL Query

SELECT D.DEPTNO,D.DNAME,D.LOC,E.EMPNO,E.ENAME,E.JOB,E.SAL FROM DEPT D JOIN EMP E on E.JOB ='MANAGER' OR E.JOB = 'CLERK'

ESQL Query

Getting the JOB details from input xml and assigning it to local variable.

SET type1 = InputRoot.XMLNSC.Employee.Type[1];
SET type2 = InputRoot.XMLNSC.Employee.Type[2];

Now I need to pass the above variables to my ESQL query.

SET Outputroot.XMLNSC.List[] = PASSTHRU('SELECT D.DEPTNO,D.DNAME,D.LOC,E.EMPNO,E.ENAME,E.JOB,E.SAL FROM Database.EMP AS E,Database.DEPT AS D where E.JOB =?' VALUES('type1','type2')) ;

But the above query is not working.

Answer 1

There are two problems in this query:

First, you are passing two parameters but only one question mark (?) exists in the query.

Second, you are passing a string value like 'type1' and the correct is to pass the variable:

SET Outputroot.XMLNSC.List[] = PASSTHRU('SELECT D.DEPTNO,D.DNAME,D.LOC,E.EMPNO,E.ENAME,E.JOB,E.SAL FROM Database.EMP AS E,Database.DEPT AS D where E.JOB =?' VALUES(type1)) ;