-
1. Re: Teiid:Virtual Procedure Not working from Select query
shawkins Nov 14, 2012 7:37 AM (in response to mdalei76)For #2 to work the procedure must have return/out/inout parameters or return a result set. Otherwise there are now columns to select from.
Steve
-
2. Re: Teiid:Virtual Procedure Not working from Select query
mdalei76 Nov 14, 2012 11:45 AM (in response to shawkins)Yes, you are right.
But when I try to create procedure resultset in Teiid Designer it gives error :
The SELECT transformation is valid, but NOT fully reconciled:
- The number of transformation output symbols is zero.I have created Procedure ResultSet and Added an Integer column.
How should I proceed further ?
-
3. Re: Teiid:Virtual Procedure Not working from Select query
shawkins Nov 14, 2012 12:17 PM (in response to mdalei76)I've moved the conversation over to the Designer forum, hopefully someone will pick it up from there. What version of Designer are you using?
Steve
-
4. Re: Teiid:Virtual Procedure Not working from Select query
mdalei76 Nov 14, 2012 12:32 PM (in response to shawkins)Hi,
I tried using Execute String inside procedure and it worked.
Thanks.
-
5. Virtual Procedure Not working from Select query
neetacomp May 22, 2013 7:15 AM (in response to shawkins)Hello Steve,
I am also facing the same problem.
I guess I am not sure how to define Virtual Procedure OUT parameter
My virtual procedure looks like
CREATE VIRTUAL PROCEDURE
BEGIN
DECLARE string VARIABLES.ItemId;
VARIABLES.ItemId = (SELECT SQLServerConnectionProfile2.tblItems.ItemId FROM tblItems);
END
And I am trying to call my virtual procedure named VP1 in a following way from my virtual table
SELECT
A.ItemId
FROM
(EXEC VP1()) AS A
==> When I preview my virtual procedure, it gives following error
TEIID30492 Command must project at least one symbol
==> In my virtual table, it gives error :
TEIID31118 Element "A.ItemId" is not defined by any relevant group.
-
6. Re: Teiid:Virtual Procedure Not working from Select query
neetacomp May 22, 2013 6:33 AM (in response to mdalei76)Hello Manoj,
Can you please elaborate on how to use Execute String inside procedure? I am also phasing the same problem.
When I try to preview virtual procedure, I am getting following error
TEIID30492 Command must project at least one symbol
When I try to exec virtual procedure from SQL Scrapbook, I am getting following error
TEIID30357 SQLServerViewModel2.TestProcedure does not exist. (Where SQLServerViewModel2 is my view model and Test Procedure is my virtual procedure)
Thanks,
Neeta
-
7. Re: Teiid:Virtual Procedure Not working from Select query
shawkins May 22, 2013 6:51 AM (in response to neetacomp)If you have an out parameter, then you should be able to reference/assign the value by name in your procedure body:
CREATE VIRTUAL PROCEDURE
BEGIN
out = (SELECT SQLServerConnectionProfile2.tblItems.ItemId FROM tblItems);
END
In the procedure you have above declaring a new variable will simply affect that variable's values.
> TEIID30492 Command must project at least one symbol
Did you add the out parameter to the procedure parameter list? If so then EXEC VP1() when called in a relational context (assuming it will have no result set) is allowed to project out parameters.
-
8. Re: Teiid:Virtual Procedure Not working from Select query
neetacomp May 22, 2013 7:09 AM (in response to shawkins)Hello Steve,
I am sorry, but I am not much clear on what are you trying to say.
Can you please elaborate more on it
Thanks,
Neeta
-
9. Re: Teiid:Virtual Procedure Not working from Select query
shawkins May 22, 2013 7:19 AM (in response to neetacomp)In Designer you must right click on the procedure and add an out parameter. Then you should be able to assign that parameter a value in the procedure body.
-
10. Re: Teiid:Virtual Procedure Not working from Select query
neetacomp May 27, 2013 2:15 AM (in response to shawkins)I have defined the out variable out1 for my procedure
CREATE VIRTUAL PROCEDURE
BEGIN
SQLServerViewModel2.TestProcedure.out1 = (SELECT SQLServerConnectionProfile2.tblItems.ItemId FROM tblItems);
END
Now when I preview it , I get following error :
select * from ( exec "SQLServerViewModel2"."TestProcedure"() ) AS X_X
TEIID30328 Unable to evaluate (SELECT SQLServerConnectionProfile2.tblItems.ItemId FROM tblItems LIMIT 2): TEIID30345 The command of this scalar subquery returned more than one value: SELECT SQLServerConnectionProfile2.tblItems.ItemId FROM tblItems LIMIT 2
Please let me know what am I missingWhen I execute the procedure from SQL Scrapbook in following ways , I get the error "TEIID30357 SQLServerViewModel2.TestProcedure does not exist."
exec "SQLServerViewModel2"."TestProcedure"()
or
select * from ( exec "SQLServerViewModel2"."TestProcedure"() ) AS X_X
Can you please let me know how can I fix it.
When I query SYS.PROCEDURES, I can view my procedure
select * from SYS.PROCEDURES where schemaname='SQLServerViewModel2'
Thanks,
Neeta
-
11. Re: Teiid:Virtual Procedure Not working from Select query
rareddy May 27, 2013 9:04 PM (in response to neetacomp)The error is saying that the variable assignment you did returns more than single value, so assignment is not possible. You can either you aggregation statements like select sum or select distinct etc where you know it returns single column or use a FOR LOOP cursor to assign a single value.