Since a procedural relational command is emulating table semantics it does not assume a default criteria value. Default values are currently only applicable for EXEC/CALL commands, which can also be used as nested tables if you need to use correlated variables.
Is there a reason that you cannot use the EXEC syntax?
Yes, there is a reason. The client of the VDB uses Hibernate, so it can't produce the EXEC/CALL commands.
Thank you. I got it.
Thanks for your help.
I think it helps the case that only procedure included.
Actually, my case is that the virtual table is the result of Union operation of a procedure and a table.
Another question of parameter of procedure in procedural relational command.
As the simple virtual procedure example mentioned before.
The two parameters are joined by 'and'. It is ok.
select * from virtualPro1 WHERE PARA1 = 4 AND PARA2 =5;
The two parameters are joined by 'or'. It is not ok.
select * from virtualPro1 WHERE PARA1 = 4 OR PARA2 =5;
The error is :
Remote org.teiid.api.exception.query.QueryPlannerException: No valid criteria specified for procedure parameter test.virtualPro1.para1
What is the reason?
Detail of my actual use case is following:
A view table --- 'viewTable'
A source table --- 'srcTable'
A virtual procedure -- 'virtualPro1'
Select transformation of 'viewTable' is :
select * from srcTable Union select * from virtualPro1
The SQL against the viewTable is (User SQL):
select * from viewTable where para1=3 OR para2=4
So the join the parameters by 'OR' is important for us.
Any help are really appreciated.
Thank you again.
Disjuncts are difficult with procedural relational mapping because they are logically multiple executions of the underlying procedure. The general approach would be to convert your criteria to disjunctive normal form then have each disjunct execute the root query - ensuring that each of the disjuncts fully specifies the procedure parameters.
So with an OR you are actually asking for:
select * from virtualPro1 WHERE PARA1 = 4
select * from virtualPro1 WHERE
PARA2 = 5
Teiid doesn't automatically perform this conversion yet, but even if it did (see below) you would run into the problem of needing to fully specify the parameters for each branch.
What you are looking for may be a targeted enhancement to take something of the form:
select * from virtualPro1 WHERE (PARA1 = 4 AND PARAM2 = 6) OR (PARAM1 = 3 AND PARA2 = 5);
and automatically convert it to the UNION ALL form where all of the parameters are specified.
The answer is very clear.