Passing an array in the WHERE clause of a DV virtual procedure

Solution Verified - Updated -

Environment

  • Red Hat JBoss Data Virtualization (DV) 6.2

Issue

  • Want to pass a list/array into a Virtual Procedure and then use the array in the WHERE clause:
CREATE VIRTUAL PROCEDURE peerrsTraining(uniqname STRING)
RETURNS TABLE (
    uniqname    STRING,
    provider    STRING,
    module_code STRING,
    module_name STRING,
    expire_dt   TIMESTAMP)
AS BEGIN
   SELECT
       pt.uniqname,
       pt.provider,
       pt.module_code,
       pt.module_name,
       pt.expire_dt
   FROM peerrs_training pt
   WHERE pt.uniqname = peerrsTraining.uniqname;
   END;

Resolution

This cannot be done in version 6.2 unless a User-defined Function is created to iterate thru the array that VARIADIC creates in the where clause1 without that you will receive an error on the comparison2. In the community release a new system function has been added, ARRAYITERATE3 and will be included in a future release of Data Virtualization.

  1. CREATE VIRTUAL PROCEDURE peerrsTraining(VARIADIC uniqname STRING)
RETURNS TABLE (
    uniqname    STRING,
    provider    STRING,
    module_code STRING,
    module_name STRING,
    expire_dt   TIMESTAMP)
AS BEGIN
   SELECT
       pt.uniqname,
       pt.provider,
       pt.module_code,
       pt.module_name,
       pt.expire_dt
   FROM peerrs_training pt
 here ->  WHERE pt.uniqname = peerrsTraining.uniqname;
   END;

    ↩︎

  2. Error: TEIID30072 Remote org.teiid.api.exception.query.QueryResolverException: TEIID30072 The expressions in this criteria are being compared but are of differing types (string[] and string) and no implicit conversion is available: Attestation.peerrsTraining.uniqname = 'one'

    ↩︎

  3. TEIID-3362 ↩︎

This solution is part of Red Hat’s fast-track publication program, providing a huge library of solutions that Red Hat engineers have created while supporting our customers. To give you the knowledge you need the instant it becomes available, these articles may be presented in a raw and unedited form.

Comments