Using SQL CASE statement in Crystal Reports

• Ramona Roman

  Old Hand

  Points: 399

  More actions

  December 15, 2005 at 3:13 pm

  #188678

  I have a CASE statement that I am using in my SQL Query in Crystal Reports:

   

  SELECT  ldcaccountnumber,

   transcode,

   ReadBeginDate = substring(TransData, 260, 10),

   ReadEndDate= substring(TransData, 270, 10),

   Therms= substring(TransData, 314, 5),

   ProcessLog,

   #daysinread = datediff(dd, cast(substring(TransData, 260, 10) as datetime), cast(substring(TransData, 270, 10) as datetime)),

   CreationDttm,

   RecvDttm=UserloginTime,

   PostedtoAcct = (

     CASE

     WHEN art.processlog = 'ADEPT' Then (Select top 1 ar.Userlogintime from phase1.dbo.aglc_recvtrans ar where art.filesystemid=ar.filesystemid)

     WHEN art.processlog = 'Lane' Then (Select top 1 ar.Userlogintime from lane.dbo.aglc_recvtrans ar where art.filesystemid=ar.filesystemid)

     Else NULL

     End

    &nbsp

  FROM aglc_recvtrans  art

  I get results in that column in SQL but in Crystal Reports I get Nothing. Anyone have any ideas?

• jratwork

  SSC-Addicted

  Points: 439

  More actions

  December 20, 2005 at 9:42 am

  #610636

  I wonder if Crystal is having a problem determing the field type for the case field because the ELSE clause returns NULL.  Can you have the ELSE return a valid datetime instead (say 1/1/1900 00:00), do a refresh in the Crystal data, and see if the field starts showing up.

  JR