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

Crystal Formula to T-SQL Expand / Collapse
Author
Message
Posted Saturday, November 16, 2013 7:55 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

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-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 1:53 PM
Points: 35,366, Visits: 31,905
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1514927
Posted Monday, November 18, 2013 8:22 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

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

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 7:04 AM
Points: 6,953, Visits: 7,071
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-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 1:53 PM
Points: 35,366, Visits: 31,905
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1515853
Posted Tuesday, November 19, 2013 5:58 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 1:53 PM
Points: 35,366, Visits: 31,905
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1515855
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse