Crystal Formula to T-SQL

Question

Crystal Formula to T-SQL

Jysafe Lerroy

SSChasing Mays

Points: 618
More actions
November 16, 2013 at 7:55 am

#395838

Hi,
I have been trying to convert the Crystal formula below into T-SQL, but I'm not able to do that. Anyone has any idea on how to convert this?
replace(space(3-len({Cono})), ' ', '1') + {Cono} +
(if len(cstr({empno}, 0, '')) < 4 then
replace(space(4-len(cstr({empno}, 0, ''))), ' ', '0')
) +
cstr({empno}, 0, '')
Thanks!

Viewing 6 posts - 1 through 5 (of 5 total)

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

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 1

You have to use CASE instead of IF and you have to use CAST or CONVERT instead of CSTR (if it means "convert to string"). You also have to remove all of the curly braces on the column names. I don't know what the second and third operand of CSTR do but here's my best guess. It also assumes that the IF had no ELSE because I can't even spell "Crystal Formula". 😀

replace(space(3-len(Cono)), ' ', '1')

+ Cono

+ CASE

WHEN len(cstr(empno, 0, '')) < 4

THEN replace(space(4-len(CAST(empno AS VARCHAR(10)))), ' ', '0')

ELSE ''

END

+ CAST(empno AS VARCHAR(10))

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jysafe Lerroy SSChasing Mays Points: 618 More actions · Answer 2

Jysafe Lerroy

SSChasing Mays

Points: 618

November 18, 2013 at 8:22 am

#1667158

Thank you so much. That is exactly what I wanted.

David Burrows SSC Guru Points: 65142 More actions · Answer 3

Jeff Moden (11/16/2013)
You have to use CASE instead of IF and you have to use CAST or CONVERT instead of CSTR (if it means "convert to string"). You also have to remove all of the curly braces on the column names. I don't know what the second and third operand of CSTR do but here's my best guess. It also assumes that the IF had no ELSE because I can't even spell "Crystal Formula". 😀
replace(space(3-len(Cono)), ' ', '1')
+ Cono
+ CASE
WHEN len(cstr(empno, 0, '')) < 4
THEN replace(space(4-len(CAST(empno AS VARCHAR(10)))), ' ', '0')
ELSE ''
END
+ CAST(empno AS VARCHAR(10))

Great improv Jeff, you really needed the 'crystal' ball for that one 😉

Far away is close at hand in the images of elsewhere.
Anon.

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 4

David Burrows (11/18/2013)
Jeff Moden (11/16/2013)
You have to use CASE instead of IF and you have to use CAST or CONVERT instead of CSTR (if it means "convert to string"). You also have to remove all of the curly braces on the column names. I don't know what the second and third operand of CSTR do but here's my best guess. It also assumes that the IF had no ELSE because I can't even spell "Crystal Formula". 😀
replace(space(3-len(Cono)), ' ', '1')
+ Cono
+ CASE
WHEN len(cstr(empno, 0, '')) < 4
THEN replace(space(4-len(CAST(empno AS VARCHAR(10)))), ' ', '0')
ELSE ''
END
+ CAST(empno AS VARCHAR(10))
Great improv Jeff, you really needed the 'crystal' ball for that one 😉

I'm just glad it didn't turn out out to be too much of an as*-umption on my part.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 5

Jysafe Lerroy (11/18/2013)
Thank you so much. That is exactly what I wanted.

You bet. Thanks for the feedback. I really don't know how to program in Crystal so I had to make some guesses. I figured if I told you what I was doing, you might be able to figure it out if I got it wrong.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)