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@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.