I thought I was going to do something simple but it's giving me errors. I am trying to combine our order# with the order suffix which I can do without a problem use a CAST statement. I need the result to be order#-## (123456-01). The issue arises when there are more than nine releases and I get 123456-010 as I have added a '-0' in my combine. I've tried this but it gives me parsing errors:
CASE WHEN [Order Suffix] > 9
THEN CAST (([Order #],varchar(7)) + '-' + CAST ([Order Suffix],varchar(2))
ELSE CAST (([Order #],varchar(7)) + '-0' + CAST ([Order Suffix],varchar(2))
END
What am I doing wrong and is there a better way to accomplish this? Thanks in advance.
CAST() uses the syntax as <type> is basically all databases (this is a standard function.
Perhaps this will do what you want:
(CAST(([Order #] as varchar(7)) +
(CASE WHEN [Order Suffix] <= 9 THEN '-0' ELSE '-' END) +
CAST([Order Suffix] as varchar(2))
)
Notice that this factors out the common code in the two expressions. I think that helps with maintainability.