Re: Sqldf - error message

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 :)
>>
>>
>>
>>
>