[R] SELECT CASE in RODBC

[boni]

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.