SQL variables with spaces in them?

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!

Best Answer

  • AS
    AS 🔵
    Accepted Answer

    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

Answers

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

  • Thank you but it still gives a syntax error - 

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

  • 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 

  • 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

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

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

  • Haha! Deal.

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

  • 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?

     

     

  • 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