Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Dynamically assign SQL Database based on date

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

JP

//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;

1 Solution

Accepted Solutions
vishsaggi
Champion III
Champion III

Try this?

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

View solution in original post

9 Replies
vishsaggi
Champion III
Champion III

Try this?

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

Not applicable
Author

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

vishsaggi
Champion III
Champion III

Are you using variable in that next select script?

Not applicable
Author

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

vishsaggi
Champion III
Champion III

Can you tell what variable is holding?

May be try this?

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

Not applicable
Author

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.

vishsaggi
Champion III
Champion III

So which one worked?

Not applicable
Author

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

vishsaggi
Champion III
Champion III

Glad it worked.