topic Re: SQL Cursor Question in QlikView

SQL Cursor Question

jcampbell474 — Mon, 13 Feb 2017 18:37:05 GMT

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!

Re: SQL Cursor Question

aarkay29 — Mon, 13 Feb 2017 19:11:04 GMT

Hope this helps!

https://help.qlik.com/en-US/connectors/Subsystems/SAP_SQL_Connector_help/Content/6.2/QV-connection/SAP-SQL-connection-Su…

Re: SQL Cursor Question

Anonymous — Mon, 13 Feb 2017 19:27:37 GMT

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;

Re: SQL Cursor Question

jcampbell474 — Mon, 13 Feb 2017 21:00:07 GMT

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

Re: SQL Cursor Question

jcampbell474 — Wed, 15 Feb 2017 14:25:41 GMT

Please disregard this post.

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