Hitachi Vantara Pentaho Community Forums
Results 1 to 4 of 4

Thread: Varible arguments - Loop in Select Query

  1. #1

    Default Variable arguments - Loop in Select Query

    Hi all,

    I have a serious challenge here in Pentaho Kettle and I humbly need your help. Thanks in advance.

    Goal to achieve: To do a select query that has multiple and dynamic fields.

    Multiple and Dynamic Fields: These referes to info regarding a customer based on his ID (This ID is a defined Variable).

    Method: Ok. I have a table on which I have information (attributes) about a certain customer. This table is not THE costumer table. I can know the number of attributes for each costumer by querying the information_schema database from mysql. I use the following query:
    Code:
    Select count(*) as AttributeQuantity from information_schema.columns where column_name like 'attribute_%' and table_name = 'users_${userID}'
    • Results from this query: For userID 1234 we have AttributeQuantity = 2 attributes and for userID 4321 we have AttributeQuantity = 4 attributes.

    Back to the goal: For this output of info, the goal is to do something automated similar to this:

    select attribute_1, attribute_2 from table info_${userID} - for userID1234 and select attribute_1, attribute_2, attribute_3, attribute_4 from table info_${userID}.

    If there were 2 more userID's in the database, with 3 and 1 attributes respectivly, the query should be able to do the same.

    How can I make this dynamic Select query?

    In few words:

    1. I have the number of attributes for each userID
    2. I have a variable set for querying the correct userID
    3. How can i dynamically query the attributes table, with variable select fields size.
    Last edited by joaoromao; 01-25-2011 at 07:42 AM.

  2. #2

    Default

    I was able to code (java) the sql string like this:

    Code:
    public class Teste {
    
    public static void main(String args[]){
    
    String atributes = "atribute_";
    
    int number_of_fields = ${INPUT FIELD};
    
    int j = 0;
    
    for (int i = 1; i <= number_of_fields; i++) {
    
    
    j = i; if(j == 1){
    atributes = atributes + Integer.toString(j);
    } else{
    atributes = atributes +"," +"atribute_" + Integer.toString(j);
    }
    } System.out.println(atributes); } }
    This way i get the attribute_x, attribute_y, attribute_z, according to the input number of attributes.

    But how can i implement this on kettle?

  3. #3
    Join Date
    Nov 2008
    Posts
    143

    Default

    E aí João, tudo certo?

    Is this related to your previous post?

    You could use your sample test mentioned above in a User Defined Java Class to create your dynamic SQL and then use a Execute row SQL script.

  4. #4

    Default

    Quote Originally Posted by renatopb View Post
    E aí João, tudo certo?

    Is this related to your previous post?

    You could use your sample test mentioned above in a User Defined Java Class to create your dynamic SQL and then use a Execute row SQL script.
    To whom it might concern (olá renato )

    I've managed to solve this with this javascript :

    Code:
    var atributos = "atribute_";
    
    var c = Contador;
    
    var j = 0;
    
    for (var i = 1; i <= c; i++)
    
    {
    
    	j = i;
    
    	if(j == 1){ atributos = atributos + num2str(j); }
    
    		else
    
    			{
    
    				atributos = atributos +"," +"atribute_" + num2str(j);
    
    			}
    
    }
    
    sql_query = "SELECT "+ atributos;

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Privacy Policy | Legal Notices | Safe Harbor Privacy Policy

Copyright © 2005 - 2019 Hitachi Vantara Corporation. All Rights Reserved.