convert crystal to SQL

Question

convert crystal to SQL

GG_BI_GG

SSCrazy

Points: 2627
More actions
August 6, 2013 at 2:32 am

#296470

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")

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

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

Koen Verbeeck SSC Guru Points: 259115 More actions · Answer 1

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

GG_BI_GG SSCrazy Points: 2627 More actions · Answer 2

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

Koen Verbeeck SSC Guru Points: 259115 More actions · Answer 3

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

GG_BI_GG SSCrazy Points: 2627 More actions · Answer 4

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

Koen Verbeeck SSC Guru Points: 259115 More actions · Answer 5

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

GG_BI_GG SSCrazy Points: 2627 More actions · Answer 6

GG_BI_GG

SSCrazy

Points: 2627

August 6, 2013 at 7:16 am

#1638908

It is in the expression box.

Look picture

Koen Verbeeck SSC Guru Points: 259115 More actions · Answer 7

Any errors?

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

GG_BI_GG SSCrazy Points: 2627 More actions · Answer 8

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

See picture.

Eric M Russell SSC Guru Points: 125562 More actions · Answer 9

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

Koen Verbeeck SSC Guru Points: 259115 More actions · Answer 10

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

GG_BI_GG SSCrazy Points: 2627 More actions · Answer 11

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.. 🙂

GG_BI_GG SSCrazy Points: 2627 More actions · Answer 12

Koen Verbeeck (8/6/2013)
Try with a single =.
Double == is SSIS expression language :blush:

if i do that i get a syntax error.

Koen Verbeeck SSC Guru Points: 259115 More actions · Answer 13

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

GG_BI_GG SSCrazy Points: 2627 More actions · Answer 14

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