-
1. Re: virtual procedure's default value in Procedural Relational Command
shawkins Jan 4, 2011 11:00 AM (in response to meltedmetal)S.Q.
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?
Steve
-
2. Re: virtual procedure's default value in Procedural Relational Command
meltedmetal Jan 5, 2011 12:34 AM (in response to shawkins)Hi, Steven
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.
Best Regards,
S.Q.
-
3. Re: virtual procedure's default value in Procedural Relational Command
shawkins Jan 5, 2011 10:45 AM (in response to meltedmetal)S.Q.
Are you able to use the native call support in Hibernate? http://docs.jboss.org/hibernate/core/3.3/reference/en/html/querysql.html#sp_query
Steve
-
4. Re: virtual procedure's default value in Procedural Relational Command
meltedmetal Jan 7, 2011 2:54 AM (in response to shawkins)Hi, Steven
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.
Best Regards,
S.Q.
-
5. Re: virtual procedure's default value in Procedural Relational Command
shawkins Jan 7, 2011 11:04 AM (in response to meltedmetal)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
UNION ALL
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.
Steve
-
6. virtual procedure's default value in Procedural Relational Command
meltedmetal Jan 24, 2011 3:26 AM (in response to shawkins)The answer is very clear.
Thanks.