Post reply

Using SQL CASE statement in Crystal Reports

  • 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?

  • December 19, 2005 at 8:00 am

    This was removed by the editor as SPAM

  • 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

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply