Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Exercise Q from 70-461 Training Book


Exercise Q from 70-461 Training Book

Author
Message
rho_pooka
rho_pooka
Valued Member
Valued Member (73 reputation)Valued Member (73 reputation)Valued Member (73 reputation)Valued Member (73 reputation)Valued Member (73 reputation)Valued Member (73 reputation)Valued Member (73 reputation)Valued Member (73 reputation)

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 ProductionIDColumn
FROM Production.Products

Thank you in advance!

P.S. how do you guys make your posts look so pretty (colors, scrolling, etc)?
Christian Graus
Christian Graus
Valued Member
Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)

Group: General Forum Members
Points: 57 Visits: 97
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 :-)
Koen Verbeeck
Koen Verbeeck
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

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
rho_pooka
Valued Member
Valued Member (73 reputation)Valued Member (73 reputation)Valued Member (73 reputation)Valued Member (73 reputation)Valued Member (73 reputation)Valued Member (73 reputation)Valued Member (73 reputation)Valued Member (73 reputation)

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
Sean Lange
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

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 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 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
rho_pooka
Valued Member
Valued Member (73 reputation)Valued Member (73 reputation)Valued Member (73 reputation)Valued Member (73 reputation)Valued Member (73 reputation)Valued Member (73 reputation)Valued Member (73 reputation)Valued Member (73 reputation)

Group: General Forum Members
Points: 73 Visits: 205
I see now, thanks for help!
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search