## Exercise Q from 70-461 Training Book

 Author Message rho_pooka Valued Member Group: General Forum Members Points: 73 Visits: 205 Greetings all, exercise in the book asks to write a query that "returns the existing numeric product ID, and the product ID formatted as a fixed-sized string with 10 digits with leading zeros. E.G. product 42 should be returned as '00000000042'I've been trying to work this out by first casting productID (INT) to varchar, finding out how long the variable is, then adding the appropriate number of zeros. (ProductID is a max of 3 digits) The book has a much shorter way of doing this, but I'm just a beginner.QUESTION; why doesn't the = sign evaluate? I keep getting "incorrect syntax near '='. Also, if the = did work, am I allowed to use the "THEN-->CONCAT(.....)"? DECLARE @pid VARCHAR(10)SET @pid = CAST([productid] AS varchar(10))SELECT productid, 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 ProductionIDColumnFROM Production.ProductsThank you in advance!P.S. how do you guys make your posts look so pretty (colors, scrolling, etc)? Christian Graus Valued Member Group: General Forum Members Points: 57 Visits: 97 `SELECT CreditCardID, right('00000000000' + cast (CreditCardID as varchar(10)), 10)AS ProductionIDColumnFROM Sales.CreditCard`I just grabbed a random AdventureWorks table to use.I cast the number to a string, add 10 zeros to the front, the grab the last 10 characters. This will always be a 10 char long string, with preceding zeros added to the number. Change the number of chars and the digit, to change the number of chars.Look to your right when posting, there's a ton of tags there you use. They use square brackets, just to be difficult :-) Koen Verbeeck SSCoach Group: General Forum Members Points: 16441 Visits: 13202 You get the syntax error because you wrote the CASE statement wrong.There are 2 ways for writing a CASE:`CASE test WHEN 1 THEN 'a' WHEN 2 THEN 'b' ELSE 'c'END`or`CASE WHEN test = 1 THEN 'a' WHEN test = 2 THEN 'b' ELSE 'c'END`You mixed both of them, hence the error.Regarding the pretty colors: you can use IFCode shortcuts. (to the left when you post).You can hit the quote button on someones post. That way you can see how they created their code. How to post forum questions.Need an answer? No, you need a question.What’s the deal with Excel & SSIS?Member of LinkedIn. My blog at SQLKover. MCSA SQL Server 2012 - MCSE Business Intelligence rho_pooka Valued Member Group: General Forum Members Points: 73 Visits: 205 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. Sean Lange SSCoach Group: General Forum Members Points: 16548 Visits: 17003 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 productidWHEN LEN(@pid) = 1 THEN CONCAT(productid,'000000000')WHEN LEN(@pid) = 2 THEN CONCAT(productid,'00000000')WHEN LEN(@pid) = 3 THEN CONCAT(productid,'0000000')ELSE @pidEND AS ProductionIDColumnYou either have expression to evaluate at the begging OR inside of each condition. There are 2 ways to fix your code.`CASE productidWHEN LEN(@pid) = 1 THEN CONCAT(productid,'000000000')WHEN LEN(@pid) = 2 THEN CONCAT(productid,'00000000')WHEN LEN(@pid) = 3 THEN CONCAT(productid,'0000000')ELSE @pidEND 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 @pidEND 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 Moden's splitter.Cross Tabs and Pivots, Part 1 – Converting Rows to Columns Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs Understanding and Using APPLY (Part 1)Understanding and Using APPLY (Part 2) rho_pooka Valued Member Group: General Forum Members Points: 73 Visits: 205 I see now, thanks for help!