Re: Sqldf - error message - Mailing list pgsql-sql

2009/11/20 Pavel Stehule <pavel.stehule@gmail.com>:
> Hello
>
> 2009/11/20 Marvelde, Luc te <L.teMarvelde@nioo.knaw.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 :)
>>
>>
>>
>>
>