rho_pooka (1/7/2014)
Thanks for such prompt replies all!Koen, isn't the query that I had written a test just like your second example?
Struggling with this one, thanks again for help.
No you have yours mixed up.
CASE productid
WHEN LEN(@pid) = 1 THEN CONCAT(productid,'000000000')
WHEN LEN(@pid) = 2 THEN CONCAT(productid,'00000000')
WHEN LEN(@pid) = 3 THEN CONCAT(productid,'0000000')
ELSE @pid
END AS ProductionIDColumn
You either have expression to evaluate at the begging OR inside of each condition. There are 2 ways to fix your code.
CASE productid
WHEN LEN(@pid) = 1 THEN CONCAT(productid,'000000000')
WHEN LEN(@pid) = 2 THEN CONCAT(productid,'00000000')
WHEN LEN(@pid) = 3 THEN CONCAT(productid,'0000000')
ELSE @pid
END AS ProductionIDColumn
Or this way.
CASE LEN(@pid)
WHEN 1 THEN CONCAT(productid,'000000000')
WHEN 2 THEN CONCAT(productid,'00000000')
WHEN 3 THEN CONCAT(productid,'0000000')
ELSE @pid
END AS ProductionIDColumn
In your case I would prefer to use something like Christian posted above. There really is no need for a case expression here from what I can tell.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/