search

OHDSI / SqlRender

This is an R package and Java library for rendering parameterized SQL, and translating it to different SQL dialects.
https://ohdsi.github.io/SqlRender
Other
82 stars 77 forks source link

test for existence of a table #36

Closed vojtechhuser closed 8 years ago

vojtechhuser commented 8 years ago

What is the best appraoch to test across databases from within R code for existance of a given table.

For my dq study - if this table exist (achilles_results_derived) then do something if not, do something else.

schuemie commented 8 years ago

We currently only support SQL Server's equivalent of DROP TABLE IF EXISTS:

IF OBJECT_ID('ACHILLES_analysis', 'U') IS NOT NULL
DROP TABLE ACHILLES_analysis;

But you can't use that same syntax to do other things when the table does or does not exist.

What is the specific action you have in mind that needs to depend on whether the table exists?

I could add a specific function to DatabaseConnector to list all the tables in a schema that would work on all supported platforms?

vojtechhuser commented 8 years ago

My DataQuality package is crashing at a site that did not run the lasted Achilles. So if table does not exist, do not try to extract data out of it.

There are two possible problems - A query that crashes (table missing, problem) vs. query with 0 results. (no problem here)

That list tables function would be actually very good and would solve that functionality problem .