Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Exercise Q from 70-461 Training Book Expand / Collapse
Author
Message
Posted Monday, January 6, 2014 9:11 PM


Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Monday, May 26, 2014 7:27 AM
Points: 71, Visits: 193
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)?
Post #1528315
Posted Monday, January 6, 2014 9:28 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Thursday, May 8, 2014 6:43 PM
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
Post #1528326
Posted Tuesday, January 7, 2014 12:24 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: 2 days ago @ 11:20 PM
Points: 13,252, Visits: 10,133
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 LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1528367
Posted Tuesday, January 7, 2014 11:41 AM


Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Monday, May 26, 2014 7:27 AM
Points: 71, Visits: 193
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.
Post #1528615
Posted Tuesday, January 7, 2014 1:11 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 12:19 PM
Points: 13,061, Visits: 11,889
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)
Post #1528649
Posted Wednesday, January 8, 2014 9:12 AM


Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Monday, May 26, 2014 7:27 AM
Points: 71, Visits: 193
I see now, thanks for help!
Post #1528941
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse