1 Reply Latest reply: Sep 2, 2015 3:08 PM by Massimo Grossi RSS

    Dynamically load fields from DB2 tables

    P Q

      Hi guys,

       

      I wonder if you can load dynamic fields from DB2 tables.

       

      I'm connected to DB2 via ODBC connection, and I have loaded a table as:

      NameTable:

      TableName, FieldName

      TableNameA, FieldName1

      TableNameA, FieldName2

      TableNameB, FieldName3

      ....

       

      I need to load the value of all these fields from the corresponding tables, and left join to the NameTable, so I tried below code:

       

      let vNOR=NoOfRows('NameTable');
      for i=1 to $(vNOR)
      let vTAB=FieldValue(TableName,$(i));
      let vPK=FieldValue(FieldName,$(i));

      trace $(vTAB);
      trace $(vPK);

      left join(NameTable)
      load Distinct
      TableName
      ,
      FieldName
      ,
      $(vPK) as PK_VALUE
      from schema.$(vTAB);

       

      But the code doesn't work. Can anyone help?

       

      BR, PQ

        • Re: Dynamically load fields from DB2 tables
          Massimo Grossi

          try this

           

          NameTable:

          load * inline [

          TableName, FieldName

          TableNameA, FieldName1

          TableNameA, FieldName2

          TableNameA, FieldName3

          TableNameB, FieldName4

          TableNameB, FieldName5

          ];

           

          s:

          load

          TableName,

          concat(FieldName, ', ') as Fields

          Resident NameTable

          Group By TableName;

           

          for i=0 to NoOfRows('s')-1

                 t=Peek('TableName', i, 's');

                 s=Peek('Fields', i, 's');

                 set st='

                 left join (NameTable)

                 load *; '

                 & chr(10) &

                 'SQL select

                 $(s)

                 from  $(t);'

                 ;

                 //trace t=$(t);

                 //trace s=$(s);

                 trace st=$(st);

           

                 /* uncomment to execute

                 $(st);

                 */


          NEXT i;