1

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:

enter image description here

but often it's empty with zero rows. And so if I try to query it, I get an error.

enter image description here

So when figuring out how to prevent R from getting error when that happens, I have two possible avenues:

  1. use trycatch() to handle errors and move past it (which I'm also going to do)

  2. 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?

shA.t
  • 16,580
  • 5
  • 54
  • 111
Joe Crozier
  • 944
  • 8
  • 20
  • 1
    I'd suggest removing the R tag and asking this as a straight SQL question. Probably tagged with your flavor of SQL (looks like Oracle) as there might be specific solutions based on that. – Gregor Thomas May 31 '23 at 15:16
  • 1
    What will the row contain? Are you looking for advice on how to change your SQL statement? Or just the code you use to execute the SQL statement. That error is coming from the database itself, not R. If your database doesn't return any values, it's hard for R to guess what a row would have looked like for that query. See https://stackoverflow.com/questions/6233086/ora-00918-column-ambiguously-defined-in-select – MrFlick May 31 '23 at 15:19
  • As Gregor suggested, I think I'm looking for a sql specific answer. My ideal would be the exact column names available in the view, but with a blank row of null values. Hopefully there is a way for sql to do that. – Joe Crozier May 31 '23 at 15:21
  • 2
    The error explicitly mentions that there are ambiguous columns. I think your view returns multiple columns with the same name. There is no problem with zero rows. – Maarten May 31 '23 at 15:22
  • @Maarten I had thought the same thing, and being a newbie to sql I googled the error and it suggested that. However, when I scroll through the columns listed in sql developer that I screenshotted, unless I'm blind I dont see any duplicates. And when I click over to 'Data', its blank. Am i misunderstanding something? – Joe Crozier May 31 '23 at 15:26
  • 2
    https://stackoverflow.com/questions/41034484/how-to-solve-ora-00918-column-ambiguously-defined Most likely view is badly defined. Make sure that view select clause has table aliases before the column names For example view is made with 2 tables, where both tables have column `name` and you're referring it as `name` not `t1.name` – user3053452 May 31 '23 at 15:26

1 Answers1

0

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
Koushik Roy
  • 6,868
  • 2
  • 12
  • 33