Recent PostsRecent Posts Popular TopicsPopular Topics
 Home Search Members Calendar Who's On

 Crystal Formula to T-SQL Rate Topic Display Mode Topic Options
Author
 Message
 Posted Saturday, November 16, 2013 7:55 AM
 Grasshopper Group: General Forum Members Last Login: Wednesday, December 18, 2013 9:44 PM Points: 18, Visits: 78
 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!
Post #1514921
 Posted Saturday, November 16, 2013 9:08 AM
 SSC-Forever Group: General Forum Members Last Login: Today @ 8:06 PM Points: 42,046, Visits: 39,428
 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." Helpful Links:How to post code problemsHow to post performance problems
Post #1514927
 Posted Monday, November 18, 2013 8:22 AM
 Grasshopper Group: General Forum Members Last Login: Wednesday, December 18, 2013 9:44 PM Points: 18, Visits: 78
 Thank you so much. That is exactly what I wanted.
Post #1515255
 Posted Monday, November 18, 2013 10:29 AM
 SSCertifiable Group: General Forum Members Last Login: Yesterday @ 9:48 AM Points: 7,842, Visits: 9,322
 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.
Post #1515305
 Posted Tuesday, November 19, 2013 5:53 PM
 SSC-Forever Group: General Forum Members Last Login: Today @ 8:06 PM Points: 42,046, Visits: 39,428
 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." Helpful Links:How to post code problemsHow to post performance problems
Post #1515853
 Posted Tuesday, November 19, 2013 5:58 PM
 SSC-Forever Group: General Forum Members Last Login: Today @ 8:06 PM Points: 42,046, Visits: 39,428
 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." Helpful Links:How to post code problemsHow to post performance problems
Post #1515855

 Permissions