Virtual Procedure Syntax

[snadji]

Hi,

Can I use virtual procedures to create virtual views? If yes, what is the syntax? I am trying to do something like the following:

<model visible="true" type="VIRTUAL" name="myschema">

  <metadata type="DDL"><![CDATA[

    CREATE VIRTUAL PROCEDURE myproc1

    BEGIN

    CREATE LOCAL TEMPORARY TABLE temptable1 (a string, b string); 

    ... load the temp table with data from multiple sources

    ... do some stuff

    SELECT * FROM temptable1;

    END

    CREATE myview1VIEW ppp (

      a bigdecimal,

      b string

      )

      AS SELECT * FROM myproc1;

  </metadata>

</model>       

I tried different variations of the above, but always got errors.

Thanks

[shawkins]

Samier,

What you show above should work via the procedural relational syntax and will require you access the view using equality or in predicates against a and b.  What errors are you seeing?

Steve

[snadji]

I am getting this error:

TEIID31080 View myschema.myview1 validation error: QueryResolverException-Group does not exist: myproc1

I removed everything else from my model so the only thing left in it is:

<model visible="true" type="VIRTUAL" name="myschema">

  <metadata type="DDL"><![CDATA[

    CREATE VIRTUAL PROCEDURE myproc1

    BEGIN

    CREATE LOCAL TEMPORARY TABLE temptable1 (a string, b string);

    SELECT * FROM temptable1;

    END

    CREATE VIEW myview1 (

      a bigdecimal,

      b string

      )

      AS SELECT * FROM myproc1;

    ]]>

  </metadata>

</model>      

[rareddy]

try as CREATE VIEW myview1 (a bigdecimal,b string) AS SELECT * FROM myshema.myproc1;

[shawkins]

You should not have to schema qualify if the name is unambiguous.  On further inspection it looks like your ddl for the procedure isn't valid.  The following works for me on 8.2 CR1:

CREATE VIRTUAL PROCEDURE myproc1 () returns table (a bigdecimal, b string) as 
BEGIN 
  CREATE LOCAL TEMPORARY TABLE temptable1 (a bigdecimal, b string);
  SELECT * FROM temptable1;
END;

CREATE VIEW myview1 ( a bigdecimal,  b string ) AS 
  SELECT * FROM myproc1;

Steve

[snadji]

This works.  Thank you very much!