SQL variables with spaces in them?

Bernard

My existing dataflow has column names like "trade show", "contact person" and so on. But when I write SQL code for the transform, I get syntax errors:

 

SELECT TOP 10 trade show,contact person from my_data_flow

SELECT TOP 10 [trade show],[contact person] from my_data_flow

 

none of these work. What should the query look like? Apologies from a "newbie" - Thank you all!

AS

Looks like you might have the Redshift version of Dataflow.  Is that right?

Redshift uses double quotes to do the same thing.

 

select
"business email"
from
dataset

AS

For MySQL use the backtick character ` (not apostrophe- that creates a string) surrounding your column names.

ie

 

SELECT
`trade show`
,`contact person`
FROM
my_data_flow

 

That should also turn the name of your column light blue so you can tell that it worked.

Bernard

Thank you but it still gives a syntax error - 

AS

Looks like you might have the Redshift version of Dataflow.  Is that right?

Redshift uses double quotes to do the same thing.

 

select
"business email"
from
dataset

Bernard

THAT WAS IT - many thanks - I owe you a million bucks

AS

Lol.  Small, unmarked bills, por favor.

 

Keep in mind you'll need to use the same syntax when referencing spaced column names anywhere in your query, including the FROM and WHERE clauses.

SELECT
a."Customer Name"
,b."trx amount"
FROM
customers a
JOIN transactions b
ON a."customer id" = b."customer id"

http://docs.aws.amazon.com/redshift/latest/dg/cm_chap_SQLCommandRef.html 

Bernard

Got it, thank you. Could I bother you with another question? I'm choosing the column names from the existing dataflow, and I understand if a column name I need isn't in it, I can add in another dataflow. But is there a quick way of finding the column names in ALL the dataflows, without having to open every one and examine the list? Thank you

AS

You can add columns from other datets by joining them as another dataset to the dataflow you're working on. There isn't an easy way to search all columns of all datasets (a good idea), but you can hover over the 'Rows/Columns' section in the list of datasets in of the Datacenter, which will display all of the column names for a particular dataset.  That way you can quickly view the contents of every dataset instead of opening and previewing each individually.

Bernard

Thank you Aaron, that's very helpful. I owe you lunch if you're ever in the Santa Barbara area.

AS

Haha! Deal.

Unknown

@Bernard, did any of the above replies help you out?

user01622

Hi Guys,

 

I am having similar issues when trying to put together an SQL query against an import excel file, where there is a space in the column name.

 

If I just do;

 

SELECT *

FROM Data_set

 

The Data displays fine, but when I try to just select a column that happens to have a space in the name, it just repeats the column name in the result,

 

SELECT 'Date Due'

FROM Data_set 

 

for example. Screenshot attached. Any ideas?

 

 

user004371

If you are using a MySQL Dataflow, you have to use the backtick, not the single or double quote:

 

SELECT `Date Due`  -- not 'Date Due' or "Date Due"

FROM Data_set