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.
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)) ;
Here is the solution:
DECLARE designation char;
DECLARE designation1 char;
DECLARE Query char;
FOR source AS InputRoot.XMLNSC.Employees.Designation[] DO
IF(source = '') THEN
SET designation = 'Empty';
ELSE
SET designation = designation1 ||''''||UPPER(source)||'''';
SET designation1 = ''''||UPPER(source)||''''||','||designation1 ;
END IF;
SET Query = '(SELECT D.DEPTNO,D.DNAME,D.LOC,E.EMPNO,E.ENAME,E.JOB,E.SAL FROM EMP AS E JOIN DEPT AS D on D.DEPTNO = E.DEPTNO where E.JOB in ('||designation||'))';
SET Outputroot.XMLNSC.List[] = PASSTHRU(Query);
The passthru call should be
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 in (? , ?)', type1,type2) ;
OR
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=? or E.JOB=?)', type1,type2) ;
Add To Database.DBName after your PASSTHRU statement where DBName is your database name
Like this:
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)) To Database.DBName ;
