convert crystal to SQL

  • Hello SQLservercentral members,

    How do I translate this crystal report sql in Microsoft sql. The following is the syntax:

    if {inc_data.event_type}="c" then "R"+CStr({incident.incident_ref},"####0")

    else if {inc_data.event_type}="i" then CStr({incident.incident_ref},"####0")

    else "P"+CStr({incident.incident_ref},"####0")

  • This is not SQL, it is some sort of Visual Basic.

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • This is Crystalreport. In crystal report you can use Formula Fields. know i wanne convert it.

    When you read the code you can see that the difference is little. I hope you can help me forwards.

    (Koen ben je Nederlands?)

  • So you want to use this formula as a SSRS formula?

    In that case, it would be something like this:

    iif(Fields!event_type == "c","R" + Format(Fields!event_type,"####0"),iif(Fields!event_type == "i",Format(Fields!event_type,"####0"),"P" + Format(Fields!event_type,"####0"))

    Not 100% sure about the format function though, so you better check if it works.

    ps: ik ben van België, maar ik spreek Nederlands 🙂

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • Koen Verbeeck (8/6/2013)


    So you want to use this formula as a SSRS formula?

    In that case, it would be something like this:

    iif(Fields!event_type == "c","R" + Format(Fields!event_type,"####0"),iif(Fields!event_type == "i",Format(Fields!event_type,"####0"),"P" + Format(Fields!event_type,"####0"))

    Not 100% sure about the format function though, so you better check if it works.

    ps: ik ben van België, maar ik spreek Nederlands 🙂

    Ik voegde het in mijn expression bij waar de gegevens aangepast moeten worden alleen het werkt niet... Doe ik iets verkeerd.

    look pictures

  • I'll continue in English for people who accidentilly stumble upon this thread.

    You entered the expression in the text field itself, so it is just text, not an expression.

    Right click on the text box and select expression from the context menu. Enter your expression there.

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • It is in the expression box.

    Look picture

  • Any errors?

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • In the example you see that i must use the = 🙂 now im use it but.. I get a syntax error..

    See picture.

  • I'm not certain what formatting "####0" represents, it could be padding with spaces or zeros, but basically it would be a CASE expression like so:

    case inc_data.event_type

    when 'c' then 'R'+left('00000'+cast(incident.incident_ref as varchar),5)

    when 'i' then left('00000'+cast(incident.incident_ref as varchar),5)

    else 'P'+left('00000'+cast(incident.incident_ref as varchar),5)

    end

    "Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho

  • Try with a single =.

    Double == is SSIS expression language :blush:

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • Its say now this.

    Msg 156, Level 15, State 1, Line 1

    Incorrect syntax near the keyword 'case'.

    I have change a little bit (select and from statement) And the '0000' (in crystal) that the numbers must begin from 0 1 2 3 and if it line down its 0 #0 if it is the then 10.. Its weird i know.. 🙂

  • Koen Verbeeck (8/6/2013)


    Try with a single =.

    Double == is SSIS expression language :blush:

    if i do that i get a syntax error.

  • karim.boulahfa (8/7/2013)


    Koen Verbeeck (8/6/2013)


    Try with a single =.

    Double == is SSIS expression language :blush:

    if i do that i get a syntax error.

    I think you are missing a bracket ) at the end.

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • I just trying that but is solve only the 2 and the third telling that the syntax is incorrect.

Viewing 15 posts - 1 through 15 (of 29 total)

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