Skip site navigation (1) Skip section navigation (2)

Re: Sqldf - error message

From: Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com>
To: "Marvelde, Luc te" <L(dot)teMarvelde(at)nioo(dot)knaw(dot)nl>
Cc: pgsql-sql(at)postgresql(dot)org
Subject: Re: Sqldf - error message
Date: 2009-11-20 14:42:13
Message-ID: 162867790911200642p1ed67d74g64c4fa294a70aafb@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-sql
2009/11/20 Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com>:
> Hello
>
> 2009/11/20 Marvelde, Luc te <L(dot)teMarvelde(at)nioo(dot)knaw(dot)nl>:
>> Hi All!
>>
>> I just discovered sqldf and im very enthousiastic, as I am a big fan of R
>> and I often get frustrated using Access for datamanagement.
>>
>> I tried running some queries from Access in R, and it works very well.
>> However, I have problems with some queries.
>>
>> If I run this SQL query:
>>
>>> sqldf("SELECT
>> + dbo_tbl_Terrein.RingCentraleNaam,
>> + dbo_tbl_Broedsels.BroedselID
>> + FROM ((dbo_tbl_BroedselLocatie
>> + INNER JOIN dbo_tbl_Broedsels ON dbo_tbl_BroedselLocatie.BroedselID =
>> dbo_tbl_Broedsels.BroedselID)
>> + INNER JOIN dbo_tbl_Nestkasten ON dbo_tbl_BroedselLocatie.NestkastID =
>> dbo_tbl_Nestkasten.NestkastID)
>> + INNER JOIN dbo_tbl_Terrein ON dbo_tbl_Nestkasten.TerreinNummer =
>> dbo_tbl_Terrein.TerreinNummer
>> + WHERE (((dbo_tbl_Terrein.RingCentraleNaam)='Oosterhout a/d Waal'));")
>>

p.s. Your style is strange. Don't use useless parentheses

SELECT  dbo_tbl_Terrein.RingCentraleNaam,  dbo_tbl_Broedsels.BroedselID
   FROM dbo_tbl_BroedselLocatie
              INNER JOIN
              dbo_tbl_Broedsels
              ON dbo_tbl_BroedselLocatie.BroedselID =
dbo_tbl_Broedsels.BroedselID
              INNER JOIN dbo_tbl_Nestkasten
              ON dbo_tbl_BroedselLocatie.NestkastID =
dbo_tbl_Nestkasten.NestkastID
              INNER JOIN
              dbo_tbl_Terrein
              ON dbo_tbl_Nestkasten.TerreinNummer =dbo_tbl_Terrein.TerreinNummer
  WHERE dbo_tbl_Terrein.RingCentraleNaam ='Oosterhout a/d Waal'

Query is same and little bit more readable.



>> I get the following message:
>>
>> Error in sqliteExecStatement(con, statement, bind.data) :
>>   RS-DBI driver: (error in statement: no such column:
>> dbo_tbl_Broedsels.BroedselID)
>>
>> ( I also tried removing the 'dbo_tbl_'  part of the dataframe names, but
>> still the same problem occurred)
>
> I thing, you have a problem with data. Probably some creating script
> was broken or some similar. The message is clean. Your database has
> not column BroedselID. Recheck your query, please. Im not sure - maybe
> SQLite identifiers are case sensitive. Maybe are defined as case
> sensitive.
>
> Regards
> Pavel Stehule
>
>>
>> Now ofcoarse I looked on google, because I was sure someone would have had
>> the same problem.. And yes, I found this website...
>>
>> http://code.google.com/p/sqldf/
>>
>> They say:
>> This happens when you try to use variable with a dot in it (as dots have
>> special meaning to SQL) or if you try to use SQL92 reserved keywords.
>> SQLite/RSQLite replaces dots with underscores and changes keywords words so
>> that they can be used. The keywords in question can be found by entering the
>> following at the R command line:
>>
>> .SQL92Keywords
>>
>> In this list of keywords, i cannot find any word that is close to the error…
>> So, i was wondering if someone knows how to solve it. preferably i do not
>> want to change all variable names, as these names come from the 'big main
>> database' of the institute.
>>
>> The site also says that SQLite/RSQLite can fix the problem… but then when i
>> look in their help, i cannot see how i can easily use those commands…
>>
>> Anyone?
>>
>> Kind regards,
>>
>> Luc te Marvelde
>> New member :)
>>
>>
>>
>>
>

In response to

pgsql-sql by date

Next:From: Pavel StehuleDate: 2009-11-20 14:43:53
Subject: Re: Sqldf - error message
Previous:From: Tom LaneDate: 2009-11-20 14:39:21
Subject: Re: Sqldf - error message

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group