Welcome to the Treehouse Community

Want to collaborate on code errors? Have bugs you need feedback on? Looking for an extra set of eyes on your latest project? Get support with fellow developers, designers, and programmers of all backgrounds and skill levels here with the Treehouse Community! While you're at it, check out some resources Treehouse students have shared here.

Looking to learn something new?

Treehouse offers a seven day free trial for new students. Get access to thousands of hours of content and join thousands of Treehouse students and alumni in the community today.

Start your free trial

Databases

Posted by hamilton katsvairo
hamilton katsvairo
6,306 Points

Reporting with SQL,Challenge task 2 of 2

In an ecommerce database there's a addresses table. There is an id, nickname, street, city, state, zip, country and user_id columns. Concatenate the street, city, state, zip and country in the following format. Street, City, State Zip. Country e.g. 34 NE 12 st, Portland, OR 97129. USA. Alias the concatenated string as address

Its giving me a bummer ,anyone to assist?

Steven Parker
Steven Parker
231,271 Points
Steven Parker
Steven Parker
231,271 Points

Please show the code that you are submitting when you get the "bummer" message, and provide a link to the challenge page.

2 Answers

michalm
michalm
7,343 Points
michalm
michalm
7,343 Points 
SELECT (street || ", " || city || ", " || state || " " || zip || ". " || country) 
    AS address
FROM addresses;

That's quite tricky remembering all the spaces etc.

Ben Glazier
Ben Glazier
7,741 Points
Ben Glazier
Ben Glazier
7,741 Points

Perhaps I'm being a little too picky here :) , but this answer isn't correct either, you're still missing the " ", after the ",", and the quotation marks around your AS value, to correctly format the address in the challenge.

The challenge asks for:

Street, City, State Zip. Country.

You're looking for something more like this:

SELECT street || "," || " " || city || "," || " " || state || " " || zip || "." || country AS "address" FROM addresses;

Steven Parker
Steven Parker
231,271 Points
Steven Parker
Steven Parker
231,271 Points

It's perfectly correct (as well as more compact and efficient) to include the space after the comma in the same literal string.

And an alias that has no spaces or other unusual characters does not need to be enclosed in quotes.

michalm
michalm
7,343 Points

Actually I have included that extra space (", ") and "" for alias is not needed. Above compiles just fine.

Ben Glazier
Ben Glazier
7,741 Points
Ben Glazier
Ben Glazier
7,741 Points

Ahh I see. I'm just an excitable noob who's flexing what they know. :D

I'm at the early stages so no shortcuts as of yet. Glad to hear that both are valid though.

Thanks Steven.

Steven Parker
Steven Parker
231,271 Points
Steven Parker
Steven Parker
231,271 Points

That looks pretty good offhand.

But I'm not used to putting parentheses around a concatenated string like that. Have you tried it without the parentheses?

If that's not it, and you provide a link to the challenge page, I'll try it myself and give another answer.

michalm
michalm
7,343 Points

I was actually answering his question :D I keep putting () to make it more readable. This compiles no prob.

Steven Parker
Steven Parker
231,271 Points
Steven Parker
Steven Parker
231,271 Points

OH, I didn't notice the name. I was expecting hamilton to post his code that was causing the error. I was intending to help him discover what went wrong rather than just providing a final answer.