topic Re: Dynamically assign SQL Database based on date in QlikView

Dynamically assign SQL Database based on date

Tue, 04 Apr 2017 17:31:00 GMT

I have a reference table in SQL that automatically updates so that you can pick yesterday's snapshot - UNIXAKCCollection.dbo.KC_Named. However, when I run the code below and try to use a variable and the Peek command the field results are null. Any help would be greatly appreciated.

Thanks

//Dataset selects Yesterday's snapshot

YesterDatabase:

SQL Select PhysicalName as DBName

From UNIXAKCCollection.dbo.KC_Named as N

Inner Join UNIXAKCCollection.dbo.KC_Databases D on N.NameID = D.NameID

WHERE N.DatabaseID =1

and N.DatabaseTag =1;

Let vdatabase = Peek('DBName','YesterDatabase'); //However vdatabase variable is null

SQL select * from $(vdatabase).larmstr;

Re: Dynamically assign SQL Database based on date

vishsaggi — Tue, 04 Apr 2017 18:42:08 GMT

Try this?

Let vdatabase = Peek('DBName', -1,'YesterDatabase');

Re: Dynamically assign SQL Database based on date

Tue, 04 Apr 2017 19:04:13 GMT

That worked well. Thanks. However, I have a new error when I try the next select clause:

ODBC read failed

SQL select * from UNIXASnapshot20170403.dbo.larmstr

Note that I added in the dbo in the table name.

Thanks

Re: Dynamically assign SQL Database based on date

vishsaggi — Tue, 04 Apr 2017 19:29:02 GMT

Are you using variable in that next select script?

Re: Dynamically assign SQL Database based on date

Tue, 04 Apr 2017 19:45:44 GMT

Yes and when I copy the text of the error message and run it it SSMS it works fine.

Re: Dynamically assign SQL Database based on date

vishsaggi — Tue, 04 Apr 2017 20:51:15 GMT

Can you tell what variable is holding?

May be try this?

SQL select * from '$(vdatabase)'.larmstr;

Re: Dynamically assign SQL Database based on date

Tue, 04 Apr 2017 21:11:08 GMT

Thanks, Vishwarath. That worked great. Thanks for the additional input. I realized that I had omitted a change in my code which I have corrected and now all parts of the script work.

Re: Dynamically assign SQL Database based on date

vishsaggi — Tue, 04 Apr 2017 21:19:17 GMT

So which one worked?

Re: Dynamically assign SQL Database based on date

Tue, 04 Apr 2017 21:26:44 GMT

This syntax worked to assign the variable

Let vdatabase = Peek('DBName', -1,'YesterDatabase');

And this syntax worked for the query:

SQL select * from $(vdatabase).dbo.larmstr;

Thanks

Re: Dynamically assign SQL Database based on date

vishsaggi — Tue, 04 Apr 2017 21:49:35 GMT

Glad it worked.