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

Can you parameterize the SQL Select statement for ORACLE tables?

Wed, 19 Mar 2014 16:38:34 GMT

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?

Anonymous — Wed, 19 Mar 2014 16:57:36 GMT

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?

Wed, 19 Mar 2014 18:36:00 GMT

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.

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

Anonymous — Wed, 19 Mar 2014 18:46:41 GMT

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;

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

Wed, 19 Mar 2014 18:52:39 GMT

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.