Customer Portal

Dynamic Table Name

Comments 1

  • Avatar
    Ladislav Szabo
    0
    Comment actions Permalink

    Hi Yijin,

    Good question!

    Once you query the table names from your database, you can build the SELECT statement in CTL. In this case, the Denormalizer component (which produces a single output record from multiple input records) is the most suitable component.

    Without further ado, let’s jump to the solution:

    First, we query the table names from the master table. Then, we can proceed to build the SELECT statement. Since all the input records are of a single group (table name), we do not need to specify the key in Denormalizer’s properties. The only thing we need to implement is the Denormalize code. Within the Denormalize, we find two functions that need implementing: append and transform.

    //#CTL2
    // This transformation defines the way in which multiple input records
    // (with the same key) are denormalized into one output record.
    string final_query = ""; // global (in a component scope) variable
    
    // This function is called for each input record from a group of records
    // with the same key.
    function integer append() {
         if (isEmpty(final_query)){ // if empty, we are processing the first record
                final_query = "SELECT * FROM " + $in.0.table_name;
         }      
         else { 
                final_query += " UNION SELECT * FROM " + $in.0.table_name;
         }
         return OK;        
    }
    
    // This function is called once after the append() function was called for all records
    // of a group of input records defined by the key.
    // It creates a single output record for the whole group.
    function integer transform() {
         $out.0.final_query = final_query;
         
         return OK;
    }

    This will provide us with the final SELECT statement. Now, if you wish to use this statement, set the DatabaseReader component to read the query from the edge:

    Best,

    Ladislav.

Please sign in to leave a comment.