R help - Feb 2003 - non-SQL sqlQuery error

Dear all, 

I've encountered a curious problem. I am trying to run an SQL query
using sqlQuery() function in RODBC. 
The query works fine when run in a stand-alone SQL browser (Microsoft
Query Analyzer, in particular). 
However, when I use the exact same thing from sqlQuery() function, I get
the following error:

Error in "[.data.frame"(data, , ) : not all specified columns exist

Traceback() gives this:

5: stop("not all specified columns exist")
4: "[.data.frame"(data, , )
3: data[, ]
2: sqlGetResults(channel, errors = errors, ...)
1: sqlQuery(con.object, " ... long query here ... ")

The puzzling thing is that the error does not seem to be like the usual
mispecified-SQL type that I normally 
get when using sqlQuery(). I am not sure how to debug this problem,
since I can't really see what's going on 
in the sqlQuery() internally. 

My query includes a "SUM(ColumnName1) GROUP BY ColumnName2" construct,
and from trial and error, it seems 
that this is the cause of the failure (at least removing it makes the
error go away).

I am using R1.6.2 and the RODBC package from
http://cran.r-project.org/bin/windows/contrib/PACKAGES.

Can anyone offer any clues?

Thanks, 

Pijus

On Mon, 10 Feb 2003, Pijus Virketis wrote:
> I've encountered a curious problem. I am trying to run an SQL query
> using sqlQuery() function in RODBC. 
> The query works fine when run in a stand-alone SQL browser (Microsoft
> Query Analyzer, in particular). 
> However, when I use the exact same thing from sqlQuery() function, I get
> the following error:
> 
> Error in "[.data.frame"(data, , ) : not all specified columns
exist
> 
> Traceback() gives this:
> 
> 5: stop("not all specified columns exist")
> 4: "[.data.frame"(data, , )
> 3: data[, ]
> 2: sqlGetResults(channel, errors = errors, ...)
> 1: sqlQuery(con.object, " ... long query here ... ")
There is no statement 3 in the released version of sqlGetResults ...
> The puzzling thing is that the error does not seem to be like the usual
> mispecified-SQL type that I normally 
> get when using sqlQuery(). I am not sure how to debug this problem,
> since I can't really see what's going on 
> in the sqlQuery() internally. 
You can.  R are RODBC and Open Source, so why not open it and read it?

-- 
Brian D. Ripley,                  ripley at stats.ox.ac.uk
Professor of Applied Statistics,  http://www.stats.ox.ac.uk/~ripley/
University of Oxford,             Tel:  +44 1865 272861 (self)
1 South Parks Road,                     +44 1865 272866 (PA)
Oxford OX1 3TG, UK                Fax:  +44 1865 272595

Professor Ripley,
> There is no statement 3 in the released version of sqlGetResults ...
In other words, I have an outdated/wrong version of RODBC?
> You can.  R are RODBC and Open Source, so why not open it and read it?
Because I don't know C/C++. I realise I should learn it, but I hope that
there is a more immediate
solution to my problem that I can apply before looking for "C++ for
Beginners" on Amazon.

Thank you, 

Pijus

Dear All, 

Reverting to RODBC 0.9.1 that I had archived resolved the problem.
Thanks to Professor Ripley for noting
that I had a non-release version of RODBC (how I might have obtained it
is still beyond me ;)).

Pijus