SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


convert crystal to SQL


convert crystal to SQL

Author
Message
Koen Verbeeck
Koen Verbeeck
SSC Guru
SSC Guru (59K reputation)SSC Guru (59K reputation)SSC Guru (59K reputation)SSC Guru (59K reputation)SSC Guru (59K reputation)SSC Guru (59K reputation)SSC Guru (59K reputation)SSC Guru (59K reputation)

Group: General Forum Members
Points: 59451 Visits: 13297
What is the result you expect?

--> Format(Fields!incident_ref.Value,"00000")

Are you sure the format function is OK? Is "00000" the formatting you need?


How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?
My blog at SQLKover.

MCSE Business Intelligence - Microsoft Data Platform MVP
GG_BI_GG
GG_BI_GG
Old Hand
Old Hand (362 reputation)Old Hand (362 reputation)Old Hand (362 reputation)Old Hand (362 reputation)Old Hand (362 reputation)Old Hand (362 reputation)Old Hand (362 reputation)Old Hand (362 reputation)

Group: General Forum Members
Points: 362 Visits: 275
What I want. In the picture you see the 2 column 1 with a letter and other with number. I want to combine those to so if it is i then i + the number like 64149 what you see in pictures..

So the result will be like;

i64149
And so on.
Koen Verbeeck
Koen Verbeeck
SSC Guru
SSC Guru (59K reputation)SSC Guru (59K reputation)SSC Guru (59K reputation)SSC Guru (59K reputation)SSC Guru (59K reputation)SSC Guru (59K reputation)SSC Guru (59K reputation)SSC Guru (59K reputation)

Group: General Forum Members
Points: 59451 Visits: 13297
karim.boulahfa (8/13/2013)
What I want. In the picture you see the 2 column 1 with a letter and other with number. I want to combine those to so if it is i then i + the number like 64149 what you see in pictures..

So the result will be like;

i64149
And so on.



In the original question you didn't say i + <number>, you just said <number>.
You just need to do a very simple modification in your expression.


How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?
My blog at SQLKover.

MCSE Business Intelligence - Microsoft Data Platform MVP
GG_BI_GG
GG_BI_GG
Old Hand
Old Hand (362 reputation)Old Hand (362 reputation)Old Hand (362 reputation)Old Hand (362 reputation)Old Hand (362 reputation)Old Hand (362 reputation)Old Hand (362 reputation)Old Hand (362 reputation)

Group: General Forum Members
Points: 362 Visits: 275
The qeustion was how can i convert the code in SQL (Expression). When you read the expression its says when event_type is i then + number zo i + 00000..

Im still searching Smile
Louis Hillebrand
Louis Hillebrand
SSCommitted
SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)

Group: General Forum Members
Points: 1924 Visits: 3462
For the formatting you can try this,
Use the case to get the correct character in front (or an empty string), and then add the incident_ref in 5 digits. (include leading zero's if needed)

CASE Event_Type
WHEN 'c' THEN 'R'
WHEN 'i' THEN ''
ELSE 'P'
END + RIGHT('00000' + CAST(incident_ref As nvarchar(5) ), 5)



Louis
GG_BI_GG
GG_BI_GG
Old Hand
Old Hand (362 reputation)Old Hand (362 reputation)Old Hand (362 reputation)Old Hand (362 reputation)Old Hand (362 reputation)Old Hand (362 reputation)Old Hand (362 reputation)Old Hand (362 reputation)

Group: General Forum Members
Points: 362 Visits: 275
Louis Hillebrand (8/14/2013)
For the formatting you can try this,
Use the case to get the correct character in front (or an empty string), and then add the incident_ref in 5 digits. (include leading zero's if needed)

CASE Event_Type
WHEN 'c' THEN 'R'
WHEN 'i' THEN ''
ELSE 'P'
END + RIGHT('00000' + CAST(incident_ref As nvarchar(5) ), 5)



Louis


I have tweak the qeury and it comes to this;

select event_type,
'Event_Type' = CASE
WHEN 'c' THEN 'R'
WHEN 'i' THEN ''
ELSE 'P'
END + RIGHT('00000' + CAST(incident_ref As nvarchar(5) ), 5)
from incident, inc_data

but get the following error;

Msg 4145, Level 15, State 1, Line 2
An expression of non-boolean type specified in a context where a condition is expected, near 'THEN'.
Louis Hillebrand
Louis Hillebrand
SSCommitted
SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)

Group: General Forum Members
Points: 1924 Visits: 3462
karim.boulahfa (8/14/2013)
Louis Hillebrand (8/14/2013)
For the formatting you can try this,
Use the case to get the correct character in front (or an empty string), and then add the incident_ref in 5 digits. (include leading zero's if needed)

CASE Event_Type
WHEN 'c' THEN 'R'
WHEN 'i' THEN ''
ELSE 'P'
END + RIGHT('00000' + CAST(incident_ref As nvarchar(5) ), 5)



Louis


I have tweak the qeury and it comes to this;

select event_type,
'Event_Type' = CASE
WHEN 'c' THEN 'R'
WHEN 'i' THEN ''
ELSE 'P'
END + RIGHT('00000' + CAST(incident_ref As nvarchar(5) ), 5)
from incident, inc_data

but get the following error;

Msg 4145, Level 15, State 1, Line 2
An expression of non-boolean type specified in a context where a condition is expected, near 'THEN'.



You need an input_expression for the CASE expression, see BOL.

Simple CASE expression:
CASE input_expression
WHEN when_expression THEN result_expression [ ...n ]
[ ELSE else_result_expression ]
END


So, for your code it will look like this:

select event_type,
'Event_Type' = CASE event_type
WHEN 'c' THEN 'R'
WHEN 'i' THEN ''
ELSE 'P'
END + RIGHT('00000' + CAST(incident_ref As nvarchar(5) ), 5)
from incident, inc_data
rpt2ssrs
rpt2ssrs
SSC-Enthusiastic
SSC-Enthusiastic (171 reputation)SSC-Enthusiastic (171 reputation)SSC-Enthusiastic (171 reputation)SSC-Enthusiastic (171 reputation)SSC-Enthusiastic (171 reputation)SSC-Enthusiastic (171 reputation)SSC-Enthusiastic (171 reputation)SSC-Enthusiastic (171 reputation)

Group: General Forum Members
Points: 171 Visits: 309
Formulas like this can be fully automatically converted.

Take a look at Crystal Migration Services.

www.crystalmigration.com
GG_BI_GG
GG_BI_GG
Old Hand
Old Hand (362 reputation)Old Hand (362 reputation)Old Hand (362 reputation)Old Hand (362 reputation)Old Hand (362 reputation)Old Hand (362 reputation)Old Hand (362 reputation)Old Hand (362 reputation)

Group: General Forum Members
Points: 362 Visits: 275
rpt2ssrs (8/22/2013)
Formulas like this can be fully automatically converted.

Take a look at Crystal Migration Services.

www.crystalmigration.com


Why must I pay for a service if I can do it on my own and the whole world can learn it and sharing!!! That is the best solution!!!
Tee Time
Tee Time
Ten Centuries
Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)

Group: General Forum Members
Points: 1025 Visits: 465
karim.boulahfa (8/22/2013)
rpt2ssrs (8/22/2013)
Formulas like this can be fully automatically converted.

Take a look at Crystal Migration Services.

www.crystalmigration.com


Why must I pay for a service if I can do it on my own and the whole world can learn it and sharing!!! That is the best solution!!!


YOU don't have to pay for anything, but it is an option for others who may want to go that route.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search