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.