[R] SELECT CASE in RODBC

[Jeff Newmiller]

RODBC is a library that sends SQL statements through an ODBC DSN. I don't see anything to suggest that the problem is in R or RODBC, but it might be in ODBC or your (unidentified) database. If you don't agree, please make your example reproducible (sample data, complete R code, ODBC DSN creation information including which type of database engine and driver you are using). Then if someone has your OS and DB, they can reproduce your problem and help track it down.

A more likely issue is that your database/driver doesn't like your SQL syntax, and this is not a SQL or ODBC support forum.
---------------------------------------------------------------------------
Jeff Newmiller                        The     .....       .....  Go Live...
DCN:<jdnewmil at dcn.davis.ca.us>        Basics: ##.#.       ##.#.  Live Go...
                                      Live:   OO#.. Dead: OO#..  Playing
Research Engineer (Solar/Batteries            O.O#.       #.O#.  with
/Software/Embedded Controllers)               .OO#.       .OO#.  rocks...1k
--------------------------------------------------------------------------- 
Sent from my phone. Please excuse my brevity.

boni <bodilh.nielsen at agrsci.dk> wrote:

>Hi all,
>
>I have a problem with a variable created by a SELECT CASE statement. 
>See
>the code below.
>
>I would like the variable "event" to be a character variabel as I want
>to
>UNION my two SELECTs - and the "event"-variable is a character variable
>in
>the first SELECT result.
>
>I have tried different ways:
>* used ' ' in stead of " " around the string I want as the text in the
>variable
>* used ELSE "NA" in stead of ELSE NULL
>
>The CASE works fine if I use numbers in stead of the text string, but
>then
>the UNION 'crashes' because it cannot convert the "event" variable from
>the
>first SELECT to an integer variable...
>
>Any suggestions?
>
>Thanks - Bodil
>
>I use the following code:
>
>raw<-sqlQuery(.zz,
>  'SELECT TOP(100)PERCENT
>    Animal.Herdnumber AS herd, Animal.ChrNumber AS chrNr,
>    Animal.AnimalNumber AS animalid, Animal.DateOfBirth AS datebirth,
>    AnimalStatus.Code AS codestatus, AnimalStatus.Description AS
>animalstatus,
>    AnimalTransfer.Date AS date,
>AnimalTransferType.Code AS codeevent, AnimalTransferType.Description AS
>event
>  FROM
>    Animal
>  FULL JOIN
>    AnimalTransfer ON Animal.AnimalNumber = AnimalTransfer.AnimalNumber
>  FULL JOIN
>    AnimalTransferType ON AnimalTransfer.AnimalTransferTypeId =
>AnimaltransferType.RowId
>  FULL JOIN
>    AnimalStatus ON Animal.AnimalStatusId = AnimalStatus.RowId
>UNION ALL 
>  SELECT TOP(100)PERCENT
>    Animal.Herdnumber AS herd, Animal.ChrNumber AS chrNr,
>    Animal.AnimalNumber AS animalid, Animal.DateOfBirth AS datebirth,
>    AnimalStatus.Code AS codestatus, AnimalStatus.Description AS
>animalstatus,
>    calving.calvingdate AS date, 
>      "codeevent" = CASE 
>        WHEN calving.lactationnumber>0 THEN 1
>          ELSE NULL
>        END,
>     "event" = CASE 
>          WHEN calving.lactationnumber>0 THEN "calving"
>          ELSE NULL
>        END
>  FROM
>    Animal
>  LEFT JOIN
>    calving ON Animal.animalnumber = calving.motheranimalnumber
>  LEFT JOIN
>    AnimalStatus ON Animal.AnimalStatusId = AnimalStatus.RowId 
>  WHERE Animal.HerdNumber = 41954  
>    AND animal.animalstatusid <> 73     
>  ')
>
>--
>View this message in context:
>http://r.789695.n4.nabble.com/SELECT-CASE-in-RODBC-tp4453354p4453354.html
>Sent from the R help mailing list archive at Nabble.com.
>
>______________________________________________
>R-help at r-project.org mailing list
>https://stat.ethz.ch/mailman/listinfo/r-help
>PLEASE do read the posting guide
>http://www.R-project.org/posting-guide.html
>and provide commented, minimal, self-contained, reproducible code.