RE: Crystal Formula to T-SQL – SQLServerCentral

SSC Guru

Points: 1003851

November 16, 2013 at 9:08 am

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)