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

Jan Lundeen
Jan Lundeen
5,887 Points
Posted by Jan Lundeen
Jan Lundeen
5,887 Points

Reporting with SQL concatenation query - SQL Error: near ".": syntax error

Hi,

Can you help me out with this? I'm trying to answer the following question, but I keep getting a syntax error near the period at the end of the string.

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. Countrye.g. 34 NE 12 st, Portland, OR 97129. USA. Alias the concatenated string as address Type in your command below.

Here is my query and the error:

SELECT Street||"," "||City||"," "||State||"," " ||Zip||"." "||Country"." AS "address" FROM addresses;"address" FROM addresses;

SQL Error: near ".": syntax error

From the error message, it looks like there's a syntax error after Zip with the period. However, I looked at my notes. It looks correct to me. Not sure what is happening. Any ideas?

Thanks,

Jan

5 Answers

Andrew Chalkley
STAFF
Andrew Chalkley
Treehouse Guest Teacher
Andrew Chalkley
Andrew Chalkley
Treehouse Guest Teacher

Hi Jan,

The circle number 1 shows an additional quote mark that opens up a string which encapsulates the || Zip ||. The number 2 circle is the period that outside of the quote and is being recognized in the query not the string. You're also missing || after country.

If you remove the extra quotes you should be closer to your end goal.

Jan Lundeen
Jan Lundeen
5,887 Points
Jan Lundeen
Jan Lundeen
5,887 Points

Hi Andrew,

Thanks! I took out the extra quotes. Unfortunately, that didn't work. I got the following error(see below). I copied the first part of the table it printed below the error as well. It appears that I put in a period twice. However, I can only see one period after Country in my query:

SELECT Street||","||City||","||State||" "||Zip||"."||Country||"." AS "address" FROM addresses;

Bummer! Your query didn't retireve the addresses in the correct format. Expecting 2532 2nd ST, San Diego, California 90222. USA not 2532 2nd ST,San Diego,California 90222.USA.. address 2532 2nd ST,San Diego ,California 90222.USA. 2213 5th PL,Fresno ,CA 90266.USA. 3521 24th AVE,Fresno ,California 90263.USA. 3984 2nd AVE,San Diego ,California 90246.USA.

Also, I noticed in the question that it wanted me to to do the following. I think I made a mistake here as well.:

"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."

Looking at the example(Portland address), it looks like I included a comma after state which shouldn't be there. I added a space between quotes instead.

However, from the question, if I follow the example, I need to include a period (".") after Country. I'm not sure why it's giving me this error.

I took out the period after Country in my query and got the following results:

Bummer! There's something wrong with your SQL statement. Please review your code and try again.

SELECT Street||","||City||" ,"||State||" "||Zip||"."||Country||" AS "address" FROM addresses;

SQL Error: unrecognized token: "" FROM addresses;"

I'm not sure what I'm doing wrong. Any suggestions?

Thanks,

Jan

Andrew Chalkley
Andrew Chalkley
Treehouse Guest Teacher
Andrew Chalkley
Andrew Chalkley
Treehouse Guest Teacher

Hey Jan,

You're super close. I just looked at the question. The example had a sentence straight after it and a period was at the end. I've changed the example to have no period and split the sentence up on a new line. That caused confusion since the query doesn't require the final period.

In your first query you're missing spaces after the two commas and the first period. Also the second period with quote marks around are unneeded ".". When you remove the trailing "." and add additional spaces like ", " and ". " Make sure you remove the || at the end when you remove "."

Hope that helps.

Regards
Andrew

Jan Lundeen
Jan Lundeen
5,887 Points
Jan Lundeen
Jan Lundeen
5,887 Points

Hi Andrew,

For some reason, I can't reply to your most recent post, so I'll reply here.

That worked. Thanks! However,on the example, the period has not been removed after country yet. Perhaps your change has not taken effect yet.

Jan

Elad Ohana
Elad Ohana
24,456 Points
Elad Ohana
Elad Ohana
24,456 Points

Hi Jan,

You seem to have 3 quote marks between concatenations. See if the code below works:

SELECT Street || ", " || City || ", " || State || ", " || Zip || ". " || Country || "." AS "address" FROM addresses;

In this code you are combining <<Street>> + ", " + <<City>> + <<State>> (and so on...) Because of the uneven opening and closing of quotes in your code, the program doesn't understand where each string begins and ends.
I don't have a link to the challenge, but that should be a more correct syntax. Hope this helps.

Jan Lundeen
Jan Lundeen
5,887 Points
Jan Lundeen
Jan Lundeen
5,887 Points

Hi Elad,

I tried that. Unfortunately, it didn't work. I'm not sure what's causing the problems.

Nevertheless, I certainly appreciate your help!

Jan

Elad Ohana
Elad Ohana
24,456 Points
Elad Ohana
Elad Ohana
24,456 Points

Hi Jan,

Try taking the comma after State, and just replacing it with a space and see if that fixes it... It might just be a formatting issue.

Elad

Elad Ohana
Elad Ohana
24,456 Points
Elad Ohana
Elad Ohana
24,456 Points

Hi Jan,

Make sure that you have a space after your commas or periods within your quotes. It does seem like you got through your syntax errors and now you just need to get your content corrected. I've found sometimes that a rewrite helps with fixing small errors when I can't seem to figure out where they are.

Jan Lundeen
Jan Lundeen
5,887 Points
Jan Lundeen
Jan Lundeen
5,887 Points

HI Elad,

Thanks for the tips! Andrew was able to answer my question.

Jan

Can Uludag
PLUS
Can Uludag
Courses Plus Student 8,769 Points
Can Uludag
Can Uludag
Courses Plus Student 8,769 Points

I've tried this and it passed.

SELECT Street || ", " || City || ", " || State || " " || Zip || ". " || Country AS "address" FROM addresses;

Jan Lundeen
Jan Lundeen
5,887 Points
Jan Lundeen
Jan Lundeen
5,887 Points

Hi Can,

Andrew already answered my question. However, that's what he told me as well. In the example, there is a period after USA, which was causing part of the problem. Andrew;s going to take the period out of the example.

I certainly appreciate your help!

Jan