JBossDeveloper

Hi Barry and Steven,

I updated JDBS and Teiid Designer to the latest version for DV 6.2, but it didn't help.

I attached the project (removed sources), perhaps have a look? Thanks in advance.

edit: the procedure is located onder e_data_consumption_layer / CaseService_archief.xmi

Regards,

Ruud

Hi Steven,

I tried a different method with the code below, but i get ERROR: ERROR: TEIID31119 Symbol #temp.tmp_casenumber is specified with an unknown group context

Any idea what is wrong with this?

BEGIN
    CREATE LOCAL TEMPORARY TABLE #temp (tmp_requestnumber integer, tmp_casenumber bigdecimal, tmp_procname string, tmp_closedate timestamp);
   INSERT INTO #temp (tmp_requestnumber, tmp_casenumber, tmp_procname, tmp_closedate) SELECT CaseService_archief.DeleteRequest_n.RequestNumber AS tmp_requestnumber, CI.CASENUM AS tmp_casenumber, PI.PROC_NAME AS tmp_procname, NOW() AS tmp_closedate FROM b_vbl_iprocess.CASE_INFORMATION AS CI INNER JOIN b_vbl_iprocess.PROC_INDEX AS PI ON CI.PROC_ID = PI.PROC_ID WHERE (CI.CASEDESC = CaseService_archief.DeleteRequest_n.RequestNumber) AND (CI.IS_DEAD = 0);
    
LOOP ON (SELECT CI.CASENUM AS tmp_casenumber, PI.PROC_NAME AS tmp_procname FROM b_vbl_iprocess.CASE_INFORMATION AS CI INNER JOIN b_vbl_iprocess.PROC_INDEX AS PI ON CI.PROC_ID = PI.PROC_ID WHERE (CI.CASEDESC = CaseService_archief.DeleteRequest_n.RequestNumber) AND (CI.IS_DEAD = 0)) AS txncursor
    BEGIN
        EXEC b_vbl_iprocess.SW_CLOSE(txncursor.tmp_procname, -1, -1, txncursor.tmp_casenumber, 'ipadmusr');
    END
    SELECT XMLELEMENT(NAME ClosedCases, XMLAGG(XMLFOREST(CaseService_archief.DeleteRequest_n.RequestNumber AS RequestNumber, #temp.tmp_casenumber AS IProcessCase, #temp.tmp_procname AS ProcedureName, NOW() AS ClosedDate))) AS xmlResult;
END