英文:
Force SQL to always return at least one row from view, through R
问题
在我的机构中，我们经常使用R来查询SQL数据库并返回数据框。
以下是代码示例：
df <- dbGetQuery(jdbcConnection,
"select * from companydatabase.specificview")
问题是，有一个视图经常是空的。也就是说，它存在，SQL开发工具中可见列：
但通常它是空的，没有任何行。因此，如果我尝试查询它，就会出错。
因此，在解决如何防止R在发生这种情况时出错时，我有两种可能的方法：
-
使用
trycatch()来处理错误并继续执行（我也会这样做） -
确保它始终返回至少一行，无论如何。
我如何做到这一点（#2）？我如何确保我总是得到至少一行空行而不导致SQL错误？
英文:
At my institution we often use R to query SQL databases and return data frames.
The code will look like this:
df<-dbGetQuery(jdbcConnection,
"select * from companydatabase.specificview")
The problem is, there's one view that's often empty. I.e. it exists, there are columns visible in SQL developer:
but often it's empty with zero rows. And so if I try to query it, I get an error.
So when figuring out how to prevent R from getting error when that happens, I have two possible avenues:
-
use
trycatch()to handle errors and move past it (which I'm also going to do) -
make sure it always brings back at least one row, no matter what.
How do I do that (#2)? How do I just make sure I always get at least one blank row and the SQL doesn't error?
答案1
得分: 0
你可以修改视图并添加一个 union all 子句来添加一行虚拟数据。这将确保每次都会获取至少一行数据。
你的新视图将如下所示 -
create or replace view specificview as
<<现有的选择子句>>
union all
select 'dummy' as key, null as col1, null as col2... from dual
英文:
you can alter the view and add a union all clause to add a dummy row. This will ensure you will get at least 1 row every time.
Your new view will look like this -
create or replace view specificview as
<<existing select clause>>
union all
select 'dummy' as key, null as col1, null as col2... from dual
</details>
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论