-
1. Re: passing where clause in virtual view as parameter in virtual procedure
shawkins Nov 18, 2014 8:33 AM (in response to gadeyne.bram)1 of 1 people found this helpfulTeiid supports a procedural relational syntax - DML Commands - Teiid 8.10 (draft) - Project Documentation Editor
Rather than "call foo(1,2)", you can use "select col1, col2, col3 ... from foo where param1=1 and param2=2" - where the names are the respective result set and parameter names. This syntax presumes that the procedure is acting as a table and you can issue more complicated predicates - "select col1, col2, col3 ... from foo where param1 in (1, 2, 3) and param2=2" and we'll invoke the procedure 3 times. However we do not use the concept of default parameters with procedural relational syntax. There is [TEIID-220] There should also be a value set property to proc inputs - JBoss Issue Tracker and there should be an issue about allowing default parameters - but that would require a hint or other additional mechanism as there are consistency/validation issues.
-
2. Re: passing where clause in virtual view as parameter in virtual procedure
gadeyne.bram Nov 18, 2014 9:47 AM (in response to shawkins)That sounds really great! I'll have a look.
Thank you!
-
3. Re: passing where clause in virtual view as parameter in virtual procedure
gadeyne.bram Nov 18, 2014 10:29 AM (in response to shawkins)It is actually what I need but since the parameter names and the result set names should be unique I can not use it. This software that I'm using generates where clauses based on the column names.
-
4. Re: passing where clause in virtual view as parameter in virtual procedure
shawkins Nov 18, 2014 10:58 AM (in response to gadeyne.bram)> It is actually what I need but since the parameter names and the result set names should be unique I can not use it
If they are not unique, you should just modify the procedure. Otherwise if there are already result set columns that effectively duplicate the parameters, then you would just introduce a view to selective project / rename the set of result set columns and parameters that you want:
create view v as select rs_col1 as name1, rs_col2 as name2, ... param1 as nameX ... from proc
-
5. Re: passing where clause in virtual view as parameter in virtual procedure
gadeyne.bram Nov 20, 2014 8:11 AM (in response to shawkins)This is really great!
Thank you Steven!