You may directly join between a table and a procedure using a lateral join, or in this case if the procedure just has a return value you could just call it in the select:
select b_vbl_iprocess.CASE_INFORMATION.CASENUM, b_vbl_iprocess.PROC_INDEX.PROC_NAME, (EXEC b_ vbl_iprocess.SW_CLOSE(proc_name, -1, -1, casenum, 'ipadmusr')) as close
join b_vbl_iprocess.PROC_INDEX on b_vbl_iprocess.PROC_INDEX.PROC_NAME = b_vbl_iprocess.CASE_INFORMATION.PROC_ID
where ci.casedesc = CaseService.DeleteRequestComplete_n.RequestNumber
and ci.is_dead = 0
I Steven, thanks for your reply.
I tried your suggestion, but keep getting parsing errors:
BEGIN select (b_vbl_iprocess.CASE_INFORMATION.CASENUM, b_vbl_iprocess.PROC_INDEX.PROC_NAME, (EXEC b_ vbl_iprocess.SW_CLOSE(b_vbl_iprocess.PROC_INDEX.PROC_NAME, -1, -1, b_vbl_iprocess.CASE_INFORMATION.CASENUM, 'ipadmusr'))) as close from b_vbl_iprocess.CASE_INFORMATION join b_vbl_iprocess.PROC_INDEX on b_vbl_iprocess.PROC_INDEX.PROC_ID = b_vbl_iprocess.CASE_INFORMATION.PROC_ID where b_vbl_iprocess.CASE_INFORMATION.CASEDESC = CaseService.DeleteRequestComplete_n.RequestNumber and b_vbl_iprocess.CASE_INFORMATION.is_dead = 0 END
ERROR: Error in parsing of sql - Encountered " "exec" "EXEC "" at line 2, column 95.
Are you trying to select the expressions as an array value?
If not, you don't need to use the extra enclosing parens - select ( )
It doesn't really matter, i tried all looks like i can't execute from the SELECT.
I use DV6.2 with teiid 8.7.1
Using a procedure as a correlated subquery is fully support with Teiid.
Are you trying to submit this as an anonymous procedure block?
If not, you don't need the begin/end.
If you are nesting in a procedure block, then command statements need a ';' delimiter at the end - is_dead = 0;
Hi Steven, perhaps i need to explain what i want to do.
I need to run the SW_CLOSE for all records from the select below.
After that, i want to select the casenum, procname and is_dead status (from case_information) and display them as xmlResult in the resultset (this part is not in the code below yet). I can't get through the first part. It keeps returning parsing errors. I attached a screenshot.
It's likely due to the older version, but you should be getting better error messages from the parser to indicate where the problem starts. Using a later version I see immediately there is an space in the procedure name: EXEC b_[*]v...
That's it... thanks.
Can i use XMLELEMENT tags in the query to instantly create a resultset without using a result for the procedure SW_CLOSE?
So something like:
BEGIN SELECT XMLELEMENT(NAME TmpTableOut, XMLATTRIBUTES(CaseService.DeleteRequestComplete_n.RequestNumber AS RequestNumber), XMLELEMENT(NAME CaseNumber, CI.CASENUM), XMLELEMENT(NAME ProcName, PI.PROC_NAME)) AS xmlResult, (SELECT * FROM (EXEC b_vbl_iprocess.SW_CLOSE(PI.PROC_NAME, -1, -1, CI.CASENUM, 'ipadmusr')) AS x) AS CloseProc FROM b_vbl_iprocess.CASE_INFORMATION AS CI INNER JOIN b_vbl_iprocess.PROC_INDEX AS PI ON PI.PROC_ID = CI.PROC_ID WHERE (CI.CASEDESC = CaseService.DeleteRequestComplete_n.RequestNumber) AND (CI.IS_DEAD = 0); END
With this, or without the XMLELEMENT tags i get: ERROR: java.util.NoSuchElementException
> Can i use XMLELEMENT tags in the query to instantly create a resultset without using a result for the procedure SW_CLOSE?
You definitely may use XMLELEMENT. A couple of considerations. If the procedure is expected to just return xml, then you need only an xml result. You can't also have closeproc at the top level of the select. Another consideration is whether you are trying to return a single document/element containing everything or a result that has a row for each element. The sql you currently have produces the latter. You can use the aggregate function XMLAGG to produce a single document, for example with a root element - XMLELEMENT(NAME root, XMLAGG(XMLELEMENT(...)))
> With this, or without the XMLELEMENT tags i get: ERROR: java.util.NoSuchElementException
Can you provide the stacktrace for that? Direct java exceptions are not expected.
> If the procedure is expected to just return xml, then you need only an xml result. You can't also have closeproc at the top level of the select
How would i solve this, i need the procedure SW_CLOSE to run, and also need an xmlresult of the cases in the select. Can i produce a second select as xmlResult?
> Can you provide the stacktrace for that? Direct java exceptions are not expected.
Can't find anything in the designer log, any other place i should seek?
> How would i solve this, i need the procedure SW_CLOSE to run, and also need an xmlresult of the cases in the select. Can i produce a second select as xmlResult?
If you need any of the return values from the procedure are used, then you can do it all in a single select, but if you use something like an inline view to just select the xml:
select xmlelenent.... from (select ..., (EXEC ... )) as vw
The EXEC subquery will simply get optimized out of the plan.
The workaround in that case would be to use a temporary table:
-- first select everything you need into the temp table
insert into #temp select cols ...;
-- then execute a select that will call the procedure for each row. you could do this in a loop if you want as well
select (EXEC ...) from #temp WITHOUT RETURN;
-- then create the xml result you want
select XMLELEMENT(...) from #temp;
> Can't find anything in the designer log, any other place i should seek?
Is it possible to provide a screenshot?
I changed the procedure and the parser seems okay, but still receive the ERROR: java.util.NoSuchElementException. I attached a screenshot.
I use this now:
BEGIN CREATE LOCAL TEMPORARY TABLE #temp (tmp_requestnumber integer, tmp_casenum bigdecimal, tmp_proc_name string, tmp_closedate timestamp); SELECT CaseService.DeleteRequestComplete_n.RequestNumber AS tmp_requestnumber, CI.CASENUM AS tmp_casenum, PI.PROC_NAME AS tmp_proc_name, NOW() AS tmp_closedate INTO #temp 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.DeleteRequestComplete_n.RequestNumber) AND (CI.IS_DEAD = 0); SELECT (SELECT * FROM (EXEC b_vbl_iprocess.SW_CLOSE(tmp_proc_name, -1, -1, tmp_casenum, 'ipadmusr')) AS x) FROM #temp WITHOUT RETURN; SELECT XMLELEMENT(NAME ClosedCases, XMLAGG(XMLFOREST(CaseService.DeleteRequestComplete_n.RequestNumber AS RequestNumber, tmp_casenum AS IProcessCase, tmp_proc_name AS ProcedureName, tmp_closedate AS ClosedDate))) AS xmlResult; END
NoSuchElementException.JPG 273.6 K
I'll have someone from the Designer team comment on that exception. It's likely already been resolved given the age of Designer. If you are on the latest fix release of Designer for 6.2 you may want to open a support ticket for that.
The fact that there is no Eclipse log indicates that the error is being thrown, caught and displayed as part of the transformation/SQL validation framework which utilizes a fork of the Teiid runtime query source. There is only one class, Evaluator, which catches NoSuchElementException and is implemented to iterate on elements within XML, JSON and text files, which appears to be the case in your screen cap and this thread.
I agree with Steve... maybe upgrade to latest Designer and 8.12.x Teiid for starters.
Having a project set to debug with would be helpful if you can share it.