6 Replies Latest reply on Jan 24, 2011 3:26 AM by meltedmetal

    virtual procedure's default value in Procedural Relational Command

    meltedmetal

      Hi, all

       

      How to use the default value of virtual procedure in procedural relational command?

       

      There is a very simple virtual procedure named 'virtualPro1' with two parameters 'para1' and 'para2'.

      'para1' is NO_NULLS and has default value 100.

      'para2' is also NO_NULLS and has default value 200.

      The result is 'sum1', it is the sum of 'para1' and 'para2'.

       

      I found the default value is ok in EXEC command. The result is 201.

      select * from (exec virtualPro1 (para1 = 1)) as t1;
      

       

      But it didn't work in procedural relational command.

      select * from virtualPro1 where para1=1 ;
      

      I get the below error:

      Processing exception 'No valid criteria specified for procedure parameter test.virtualPro1.para2' 
      Exception type org.teiid.api.exception.query.QueryPlannerException thrown 
      from org.teiid.query.optimizer.relational.rules.RulePlanProcedures.execute(RulePlanProcedures.java:121). 
      Enable more detailed logging to see the entire stacktrace.
      

       

      I want use the default values of virtual procedure in procedural relational command.

      Any help are welcome.

       

      Thank you.

       

      Best Regards,

      S.Q.

        • 1. Re: virtual procedure's default value in Procedural Relational Command
          shawkins

          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

            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

              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

                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

                  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

                    The answer is very clear.

                    Thanks.