Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase «««123

convert crystal to SQL Expand / Collapse
Author
Message
Posted Tuesday, August 13, 2013 3:00 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 3:27 PM
Points: 12,185, Visits: 9,134
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 LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1483622
Posted Tuesday, August 13, 2013 3:12 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Wednesday, April 09, 2014 1:26 AM
Points: 81, Visits: 173
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.

Post #1483623
Posted Tuesday, August 13, 2013 3:25 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 3:27 PM
Points: 12,185, Visits: 9,134
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 LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1483624
Posted Wednesday, August 14, 2013 1:26 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Wednesday, April 09, 2014 1:26 AM
Points: 81, Visits: 173
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 :)
Post #1484100
Posted Wednesday, August 14, 2013 1:47 AM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Tuesday, April 08, 2014 7:01 AM
Points: 581, Visits: 764
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
Post #1484111
Posted Wednesday, August 14, 2013 2:06 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Wednesday, April 09, 2014 1:26 AM
Points: 81, Visits: 173
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'.


Post #1484121
Posted Wednesday, August 14, 2013 2:44 AM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Tuesday, April 08, 2014 7:01 AM
Points: 581, Visits: 764
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
Post #1484134
Posted Thursday, August 22, 2013 10:57 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Sunday, September 15, 2013 10:32 AM
Points: 21, Visits: 309
Formulas like this can be fully automatically converted.

Take a look at Crystal Migration Services.

www.crystalmigration.com
Post #1487413
Posted Thursday, August 22, 2013 2:23 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Wednesday, April 09, 2014 1:26 AM
Points: 81, Visits: 173
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!!!
Post #1487514
Posted Friday, August 23, 2013 6:51 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Today @ 12:55 PM
Points: 507, Visits: 416
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.
Post #1487804
« Prev Topic | Next Topic »

Add to briefcase «««123

Permissions Expand / Collapse