Click here to monitor SSC
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
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16540 Visits: 13210
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?

Member of LinkedIn. My blog at SQLKover.

MCSA SQL Server 2012 - MCSE Business Intelligence
GG_BI_GG
GG_BI_GG
SSC-Enthusiastic
SSC-Enthusiastic (114 reputation)SSC-Enthusiastic (114 reputation)SSC-Enthusiastic (114 reputation)SSC-Enthusiastic (114 reputation)SSC-Enthusiastic (114 reputation)SSC-Enthusiastic (114 reputation)SSC-Enthusiastic (114 reputation)SSC-Enthusiastic (114 reputation)

Group: General Forum Members
Points: 114 Visits: 258
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
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16540 Visits: 13210
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?

Member of LinkedIn. My blog at SQLKover.

MCSA SQL Server 2012 - MCSE Business Intelligence
GG_BI_GG
GG_BI_GG
SSC-Enthusiastic
SSC-Enthusiastic (114 reputation)SSC-Enthusiastic (114 reputation)SSC-Enthusiastic (114 reputation)SSC-Enthusiastic (114 reputation)SSC-Enthusiastic (114 reputation)SSC-Enthusiastic (114 reputation)SSC-Enthusiastic (114 reputation)SSC-Enthusiastic (114 reputation)

Group: General Forum Members
Points: 114 Visits: 258
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.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)

Group: General Forum Members
Points: 1531 Visits: 3209
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
SSC-Enthusiastic
SSC-Enthusiastic (114 reputation)SSC-Enthusiastic (114 reputation)SSC-Enthusiastic (114 reputation)SSC-Enthusiastic (114 reputation)SSC-Enthusiastic (114 reputation)SSC-Enthusiastic (114 reputation)SSC-Enthusiastic (114 reputation)SSC-Enthusiastic (114 reputation)

Group: General Forum Members
Points: 114 Visits: 258
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.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)

Group: General Forum Members
Points: 1531 Visits: 3209
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
Grasshopper
Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)

Group: General Forum Members
Points: 23 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
SSC-Enthusiastic
SSC-Enthusiastic (114 reputation)SSC-Enthusiastic (114 reputation)SSC-Enthusiastic (114 reputation)SSC-Enthusiastic (114 reputation)SSC-Enthusiastic (114 reputation)SSC-Enthusiastic (114 reputation)SSC-Enthusiastic (114 reputation)SSC-Enthusiastic (114 reputation)

Group: General Forum Members
Points: 114 Visits: 258
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
Mr or Mrs. 500
Mr or Mrs. 500 (565 reputation)Mr or Mrs. 500 (565 reputation)Mr or Mrs. 500 (565 reputation)Mr or Mrs. 500 (565 reputation)Mr or Mrs. 500 (565 reputation)Mr or Mrs. 500 (565 reputation)Mr or Mrs. 500 (565 reputation)Mr or Mrs. 500 (565 reputation)

Group: General Forum Members
Points: 565 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