Can you parameterize the SQL Select statement for ORACLE tables?

[#1]

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?

[Anonymous]

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

[#3]

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.

[Anonymous · Accepted solution]

Oh, how I missed it!

Variable names are case-sensitive.  You define vTablename but use vTableName.  Try

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;

[#5]

Wow.  I know full well that they are case sensitive and missed it in all of the permutations I attempted, even posting it to this forum.  Sheesh.  Thanks for the second set of eyes.  It appears to be working as expected now.