Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
Not applicable

Can you parameterize the SQL Select statement for ORACLE tables?

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?

1 Solution

Accepted Solutions
mov
Esteemed Contributor III

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

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;

4 Replies
mov
Esteemed Contributor III

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

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

Not applicable

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

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.

mov
Esteemed Contributor III

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

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;

Not applicable

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

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.


Community Browser