11 Replies Latest reply on May 27, 2013 9:04 PM by rareddy

    Teiid:Virtual Procedure Not working from Select query

    mdalei76

      Hi,

       

      Rnning virtual proc using exec works, but does not work from select query.

       

      1. exec  "CompositeViewModel"."TestProc1"()   --Works

      2. select * from ( exec "CompositeViewModel"."TestProc1"() ) AS X     --- Does not work.

      17:49:13,201 WARN  [org.teiid.PROCESSOR] (Worker8_QueryProcessorQueue87) TEIID30020 Processing exception 'TEIID30492 Command must project at least one symbol' for request FmJW7EwI3MX5.0.  Exception type org.teiid.api.exception.query.QueryValidatorException thrown from org.teiid.dqp.internal.process.Request.validateWithVisitor(Request.java:330)

       

      I have created procedure using Teiid Designer.

       

      CREATE VIRTUAL PROCEDURE

      BEGIN

      SELECT id FROM audit WHERE aid = 1;

      END

       

       

      I want to see all records as output.

        • 1. Re: Teiid:Virtual Procedure Not working from Select query
          shawkins

          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

            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

              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

                Hi,

                 

                I tried using Execute String inside procedure and it worked.

                 

                 

                Thanks.

                • 5. Virtual Procedure Not working from Select query
                  neetacomp

                  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

                    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

                      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

                        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

                          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

                            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 missing

                             

                            When 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

                              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.