4 Replies Latest reply: Mar 19, 2014 2:52 PM by Al Rago RSS

    Can you parameterize the SQL Select statement for ORACLE tables?

    Al Rago

      I'm trying to loop through a list of tables to count the records in each table.

       

      DStoEXT:

      LOAD

           SourceFileName,

           EXTKeyFieldName,   

           EXTTableName,   

           SatTargetTableName,   

           HubTargetTableName   

       

      FROM [\\...\Documents\DStoEXT.xlsx] (ooxml, embedded labels, header is 1 lines, table is [DS to EXT ]);

       

      Let vRows = NoOfRows ('DStoEXT');

      For i = 0 to vRows -1

           Let vTablename = 'DBSchema.' & peek('EXTTableName',$(i),'DStoEXT');     

           EXT_Counts:

                LOAD

                     Count(1) as vTablename;

           SQL SELECT *  

           FROM $(vTableName);

      Next i;

       

      The problem is parameterizing the SQL SELECT statement.  I've tried several different ways but no joy. Suggestions?

        • Re: Can you parameterize the SQL Select statement for ORACLE tables?
          Michael Solomovich

          Start from the end.  Take a look in the log file how the FROM $(vTableName) is translated.

            • Re: Re: Can you parameterize the SQL Select statement for ORACLE tables?
              Al Rago

              From the Log file Filename.qvw (text file):

               

              3/19/2014 1:23:06 PM: 0272  Let vRows = NoOfRows ('DStoEXT')

              3/19/2014 1:23:06 PM: 0274  For i = 0 to vRows -1

              3/19/2014 1:23:06 PM: 0275    Let vTablename = 'CKOCNRDBO.' & peek('EXTTableName',0,'DStoEXT')

              3/19/2014 1:23:06 PM: 0276    EXT_Counts:

              3/19/2014 1:23:06 PM: 0277    LOAD 3/19/2014 1:23:06 PM: 0278    Count(1) as vTablename

              3/19/2014 1:23:06 PM: 0279    SQL SELECT

              3/19/2014 1:23:06 PM: 0280    *  

              3/19/2014 1:23:06 PM: 0281    FROM

              3/19/2014 1:23:06 PM:         Error: ErrorSource: OraOLEDB, ErrorMsg: ORA-00903: invalid table name

              3/19/2014 1:23:08 PM: 0283  Next i

              3/19/2014 1:23:08 PM: 0275    Let vTablename = 'CKOCNRDBO.' & peek('EXTTableName',1,'DStoEXT')

              3/19/2014 1:23:08 PM: 0276    EXT_Counts:

              3/19/2014 1:23:08 PM: 0277    LOAD

              3/19/2014 1:23:08 PM: 0278    Count(1) as vTablename

              3/19/2014 1:23:08 PM: 0279    SQL SELECT

              3/19/2014 1:23:08 PM: 0280    *  

              3/19/2014 1:23:08 PM: 0281    FROM

              3/19/2014 1:23:09 PM:         Error: ErrorSource: OraOLEDB, ErrorMsg: ORA-00903: invalid table name

              3/19/2014 1:23:09 PM: 0283  Next i

               

              In the Debugger the variable is populated correctly but, as shown above on line 281, it translates to a NULL value.