Skip to content

SELECT CASE in RODBC

2 messages · boni, Jeff Newmiller

Original
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.
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: