January 6, 2014 at 9:11 pm
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 ProductionIDColumn
FROM Production.Products
Thank you in advance!
P.S. how do you guys make your posts look so pretty (colors, scrolling, etc)?
January 6, 2014 at 9:28 pm
SELECT CreditCardID,
right('00000000000' + cast (CreditCardID as varchar(10)), 10)
AS ProductionIDColumn
FROM 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 🙂
January 7, 2014 at 12:24 am
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.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
January 7, 2014 at 11:41 am
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.
January 7, 2014 at 1:11 pm
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/
January 8, 2014 at 9:12 am
I see now, thanks for help!
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply