Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

jcampbell474
Contributor III

SQL Cursor Question

I'm trying to incorporate an existing SQL script into QV and not having much success.  SQL isn't my strongest area...

We need a Network/DB schema that includes Hash ID's.  I'm not sure how to implement it.

Here's what I have so far:

Connection string;

//Build base table

Execute('SELECT  s.name AS SchemaName

,       t.name AS TableName

,       c.name AS ColumnName

,       0 AS IsHashID

INTO #SchemaCrawler

FROM    sys.tables t

        INNER JOIN sys.columns c ON c.object_id = t.object_id

        INNER JOIN sys.schemas s ON s.schema_id = t.schema_id');

QV says there is a script line error.  Am I going about this the right way?

Thanks!

JC

Tags (1)
4 Replies
aarkay29
Valued Contributor

Re: SQL Cursor Question

cocosmina
Contributor II

Re: SQL Cursor Question

Hi,

Try:

Connection string;


SchemaCrawler:

Load *;

SQL SELECT  s.name AS SchemaName

,       t.name AS TableName

,       c.name AS ColumnName

,       0 AS IsHashID

FROM    sys.tables t

        INNER JOIN sys.columns c ON c.object_id = t.object_id

        INNER JOIN sys.schemas s ON s.schema_id = t.schema_id;

jcampbell474
Contributor III

Re: SQL Cursor Question

Thank you both for your assistance.  I now have all of the code that I need to incorporate into QV.  

I have to loop it through nine (9) servers.  Any thoughts as to how it can be implemented?

//Build base table

SELECT  s.name AS SchemaName

,       t.name AS TableName

,       c.name AS ColumnName

,             0 AS IsHashID

INTO #SchemaCrawler

FROM    sys.tables t

        INNER JOIN sys.columns c ON c.object_id = t.object_id

        INNER JOIN sys.schemas s ON s.schema_id = t.schema_id;

//Run a cursor

DECLARE @database

DECLARE @schema VARCHAR(15)

DECLARE @table VARCHAR(255)

DECLARE @column VARCHAR(255)

DECLARE @sql VARCHAR(MAX)

DECLARE LoopTables CURSOR FAST_FORWARD READ_ONLY FOR 

SELECT s.name, t.name, c.name

FROM sys.tables t

INNER JOIN sys.columns c ON c.object_id = t.object_id

INNER JOIN sys.schemas s ON s.schema_id = t.schema_id

WHERE c.name = 'hashid'

AND t.name = 'plsubdatahash'

OPEN LoopTables

FETCH NEXT FROM LoopTables INTO @schema, @table, @column

WHILE @@FETCH_STATUS = 0

BEGIN

   

       SELECT @sql = 'INSERT INTO #SchemaCrawler (SchemaName, TableName, ColumnName, IsHashID) SELECT DISTINCT ''' + @schema + ''' as SchemaName, ''' + @table + ''' as TableName, hashid AS ColumnName, 1 as IsHashID FROM ' + @schema + '.' + @table

       EXEC sp_executesql @sql

    FETCH NEXT FROM LoopTables INTO @schema, @table, @column

END

CLOSE LoopTables

DEALLOCATE LoopTables

SELECT  *

FROM  #SchemaCrawler 

jcampbell474
Contributor III

Re: SQL Cursor Question

Please disregard this post. 

After getting clarification on the requirements, I need to start a new post.