Skip to Content
avatar image
Former Member

Crystal Reports - case varchar changed to a 'string' datatype and truncated to 32

Hi, I am using Crystal Reports version 11.0.0.1282

I am using the 'Command' option in the database expert section. I have a case statement with multiple when clauses: when fielda 'xxxx' then 'xxxxxxxxxxx' , when fielda 'yyyyy' then 'yyyyyyyyyyy', etc. I end the case statement with End as 'Description'. 'fielda' is a varchar 255 field (usuall never gets anywhere near 255), but the command is defaulting the 'Description' to a String [32] datatype. So my display description of 50 characters gets truncated to 32 characters. I have done many searches and I see that there are truncation issues with CR but I couldn't find my exact issue. I did find one that said to use 'Cast' so I tried that but it didn't work. This is what I tried:

When fielda like '%xxxx%' then CAST('xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx' as VARCHAR(50)).

Any help with this issue would be deeply appreciated.

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

4 Answers

  • Best Answer
    Oct 25, 2016 at 06:09 PM

    What type of database are you connecting to? What type of a connection are you using (ODBC, OleDB, Native, etc.)? What version of Crystal are you using (please go to Help>>About and get the exact version number!)

    Also, you might want to try putting the "Cast()" around the case statement as a whole instead of around the individual parts - so it would look something like this:

    CAST(
    case
    ...
    end as VARCHAR(50)) as Description

    -Dell

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    Oct 25, 2016 at 08:03 PM

    Thanks for responding Dell, I will try that. I am using ODBC to a cache db.

    Add comment
    10|10000 characters needed characters exceeded

  • Oct 25, 2016 at 08:37 PM

    Please mark my response as the "Best Answer" so that others will know what the solution is and so that the question is closed.

    Thanks!

    -Dell

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    Oct 25, 2016 at 08:14 PM

    That worked! you are a life saver :-)

    Add comment
    10|10000 characters needed characters exceeded