1 Reply Latest reply on Mar 28, 2011 3:31 PM by Russell Steel

    Virtual Procedure erroring with ERROR: Procedure PartsVirtual.partsbyColour1 does not have a return value.

    Russell Steel Newbie

      I'm trying to port a VDB from MetaMatrix 5.5.3 to Teiid 7.3.

       

      I have successfully followed the Migrating Metmatrix projects to Teiid Designer, the problem is with VP code from MMx 5.5.3 where a VP was calling another VP with a 1 column, 1 row output;

       

       

      CREATE VIRTUAL PROCEDURE

      BEGIN

              DECLARE STRING o_PRODUCT_NAME;

       

               o_PRODUCT_NAME = EXEC PartsVirtual.partsbyColour1(colorIn => 'BLUE');

       

      END

       

      Results with ERROR: Procedure PartsVirtual.partsbyColour1 does not have a return value.

       

      Where as;

       

      CREATE VIRTUAL PROCEDURE

      BEGIN

            EXEC PartsVirtual.partsbyColour1(colorIn => 'BLUE');

      END

       

      Results with my VP inheriting the results set being passed backed from PartsVirtual.partsbyColour1, the problem is, I'd like to be able to utilise the value being returned by the called VP within the calling VP, and not just pass it back.

       

       

      CREATE VIRTUAL PROCEDURE

      BEGIN

          SELECT PartsSourceB.PARTS.PART_ID FROM PartsSourceB.PARTS WHERE PartsSourceB.PARTS.PART_COLOR = PARTSVIRTUAL.PARTSBYCOLOUR1.colorIn;

      END

       

      Also reading the current Teiid documentation, http://docs.jboss.org/teiid/7.3.0.Final/reference/en-US/pdf/teiid_reference.pdf, section 6.2.5 states the following Limitations for VPs;

       

      Teiid virtual procedures can only be defined in Teiid Designer. They also cannot use IN/OUT, OUT or RETURN parameters and may only return 1 result set.

       

      Which would then mean to me, that the error being returned by the Designer is incorrect, also I cannot find any properties in the designer to specify a parameters as OUT parameter, only an IN can be defined.

      Teiid VP Parameter Direction Property.png