Received: from localhost (unknown [200.46.208.211]) by mail.postgresql.org (Postfix) with ESMTP id BDCD0633C4D for ; Fri, 20 Nov 2009 10:50:57 -0400 (AST) Received: from mail.postgresql.org ([200.46.204.86]) by localhost (mx1.hub.org [200.46.208.211]) (amavisd-maia, port 10024) with ESMTP id 11336-09-2 for ; Fri, 20 Nov 2009 14:50:38 +0000 (UTC) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6 Received: from mail-yw0-f198.google.com (mail-yw0-f198.google.com [209.85.211.198]) by mail.postgresql.org (Postfix) with ESMTP id 9864A633211 for ; Fri, 20 Nov 2009 10:50:29 -0400 (AST) Received: by ywh36 with SMTP id 36so2903282ywh.15 for ; Fri, 20 Nov 2009 06:50:27 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=gamma; h=domainkey-signature:mime-version:received:in-reply-to:references :date:message-id:subject:from:to:cc:content-type :content-transfer-encoding; bh=kuWal0I0uV5Uh3mbYFv30SNHf5HLw5RB83Ohv2b0anM=; b=lYeC8eSTK62TH6lBudrtWR7ahIi5KVH+CIgE6SaHh5/ck7x0PDylIV/oDS04UFreGa MDs3UwZqXYGt3puogfwtILj+Zik15UheumN1lKqsYzKjPCc9/0HN29vYM37HHOjHEifQ NBEfikbMpzrEqdFEUfLsBMZvTDyi9fISFxd2Q= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma; h=mime-version:in-reply-to:references:date:message-id:subject:from:to :cc:content-type:content-transfer-encoding; b=pWI5OQ4ZqjVUosy0UdAYRLIXfA4uzTTQH5l3sbqvN87nPnfkxlH7p3TkRhHHOz0clv A3P7ckl1HMxZMcNixp9n2KhrL/Yt2z7hZcTCBHCzuI63CjMSm4mJQ26OVp1nQD1XCwI/ JCGYDo8SuVLPSvRFnNaA7E/ePTqlxh63w15GE= MIME-Version: 1.0 Received: by 10.150.19.5 with SMTP id 5mr2802373ybs.103.1258728133653; Fri, 20 Nov 2009 06:42:13 -0800 (PST) In-Reply-To: <162867790911200637l1c16438apc4bfa3ebef0210d2@mail.gmail.com> References: <1276C0564833F043AB4C85ED8DA9CFE502AF054F@ctemail1.nioo.int> <162867790911200637l1c16438apc4bfa3ebef0210d2@mail.gmail.com> Date: Fri, 20 Nov 2009 15:42:13 +0100 Message-ID: <162867790911200642p1ed67d74g64c4fa294a70aafb@mail.gmail.com> Subject: Re: Sqldf - error message From: Pavel Stehule To: "Marvelde, Luc te" Cc: pgsql-sql@postgresql.org Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: quoted-printable X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-2.576 tagged_above=-10 required=5 tests=AWL=0.023, BAYES_00=-2.599 X-Spam-Level: X-Archive-Number: 200911/66 X-Sequence-Number: 33417 2009/11/20 Pavel Stehule : > Hello > > 2009/11/20 Marvelde, Luc te : >> 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 =3D >> dbo_tbl_Broedsels.BroedselID) >> + INNER JOIN dbo_tbl_Nestkasten ON dbo_tbl_BroedselLocatie.NestkastID = =3D >> dbo_tbl_Nestkasten.NestkastID) >> + INNER JOIN dbo_tbl_Terrein ON dbo_tbl_Nestkasten.TerreinNummer =3D >> dbo_tbl_Terrein.TerreinNummer >> + WHERE (((dbo_tbl_Terrein.RingCentraleNaam)=3D'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 =3D dbo_tbl_Broedsels.BroedselID INNER JOIN dbo_tbl_Nestkasten ON dbo_tbl_BroedselLocatie.NestkastID =3D dbo_tbl_Nestkasten.NestkastID INNER JOIN dbo_tbl_Terrein ON dbo_tbl_Nestkasten.TerreinNummer =3Ddbo_tbl_Terrein.Terrei= nNummer WHERE dbo_tbl_Terrein.RingCentraleNaam =3D'Oosterhout a/d Waal' Query is same and little bit more readable. >> I get the following message: >> >> Error in sqliteExecStatement(con, statement, bind.data) : >> =C2=A0 RS-DBI driver: (error in statement: no such column: >> dbo_tbl_Broedsels.BroedselID) >> >> ( I also tried removing the 'dbo_tbl_'=C2=A0 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 h= ad >> 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 er= ror=E2=80=A6 >> So, i was wondering if someone knows how to solve it. preferably i do no= t >> want to change all variable names, as these names come from the 'big mai= n >> database' of the institute. >> >> The site also says that SQLite/RSQLite can fix the problem=E2=80=A6 but = then when i >> look in their help, i cannot see how i can easily use those commands=E2= =80=A6 >> >> Anyone? >> >> Kind regards, >> >> Luc te Marvelde >> New member :) >> >> >> >> >