Adding a field with mySQL

user07803
user07803 Member
in Magic ETL

Hello,

 

I'm trying to add a new field to an existing data set via mySQL (my first time doing this). The new field will take data from my existing data set (Product Name) and re-assign a new name leveraging a case statement.

 

Below is my code. I'm currently receiving an syntax error, which I'm looking for assistance on. Additionally, I want to validate this is the correct way to add a new field with mySQL integration.

 

CASE
WHEN `Product Name` like '%YOUTUBE%' then 'YouTube'
WHEN `Product Name` like '%AUDIO%' then 'Audio'
WHEN `Product Name` like '%FENCING%' then 'Geo-Fencing'

 

ELSE unassigned
END AS new product name

 

Thanks!

Best Answers

  • AS
    AS Coach
    Answer ✓

    What does your entire transform look like?

    If this is a single column in your transform, then it looks fine to me.  In total, it should look something like 

     

    SELECT

    ...

    , CASE WHEN [your case statement]

    ...

    FROM

    ...

     

    Based on the error, it seems the problem starts where your case statement begins, which is either preceded by a comma, separating it from a previous column, or the initial SELECT word.

    Aaron
    MajorDomo @ Merit Medical

    **Say "Thanks" by clicking the heart in the post that helped you.
    **Please mark the post that solves your problem by clicking on "Accept as Solution"
  • lucywo
    lucywo Member
    Answer ✓

    Hey there,

     

    If you are working in the SQL environment, then check the codes below,

     

    select a,

    (CASE
    WHEN `Product Name` like '%YOUTUBE%' then 'YouTube'
    WHEN `Product Name` like '%AUDIO%' then 'Audio'
    WHEN `Product Name` like '%FENCING%' then 'Geo-Fencing'

    ELSE 'unassigned'
    END) as b

    from c

     

    Note: a - field you want to query; b - new field name; c - table name

     

    If it still doesnt work, try change `Product Name` to [Product Name] or if even its possible to remove the blank space inside your field name.(`Product Name` --> Product_Name)

     

    Thanks!

     

     

     

  • AS
    AS Coach
    Answer ✓

    @lucywo

    I think you'd be right on 1, but the OP said mySQL, not beast mode.

    I added the same type consistency comment in my initial response also, with the option of using NULL instead, though my inquiry about wich was preferred was not answered.

    Aaron
    MajorDomo @ Merit Medical

    **Say "Thanks" by clicking the heart in the post that helped you.
    **Please mark the post that solves your problem by clicking on "Accept as Solution"

Answers

  • AS
    AS Coach

    So close.

     

    Each possible output of your case statement needs to be the same data type. The word 'unassigned' is the issue here.  Do you want it to say 'unassigned', or just not have a value? If you want it to say 'unassigned', that word needs to be in quotes like the other products are:

     

    Pick one of the two in brackets:

     

    CASE
    WHEN `Product Name` like '%YOUTUBE%' then 'YouTube'
    WHEN `Product Name` like '%AUDIO%' then 'Audio'
    WHEN `Product Name` like '%FENCING%' then 'Geo-Fencing'

    ELSE [NULL or 'Unassigned']
    END AS new product name

    Aaron
    MajorDomo @ Merit Medical

    **Say "Thanks" by clicking the heart in the post that helped you.
    **Please mark the post that solves your problem by clicking on "Accept as Solution"
  • user07803
    user07803 Member

    I'm now receiving the below error:

     

    The database reported a syntax error. You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'CASE WHEN `Product Name` like '%YOUTUBE%' then 'YouTube' WHEN `Product Name` lik' at line 1

     

    Code leveraged is below (copied from your original reply)

     

    CASE
    WHEN `Product Name` like '%YOUTUBE%' then 'YouTube'
    WHEN `Product Name` like '%AUDIO%' then 'Audio'
    WHEN `Product Name` like '%FENCING%' then 'Geo-Fencing'
    ELSE 'Unassigned'
    END AS new product name

  • AS
    AS Coach

    I hadn't seen it but your column alias "new product name" has to be one string, not three separate words.

     

    END AS 'new product name' 

    or

    END AS new_product_name

    or something like it

     

    Try that.

    Aaron
    MajorDomo @ Merit Medical

    **Say "Thanks" by clicking the heart in the post that helped you.
    **Please mark the post that solves your problem by clicking on "Accept as Solution"
  • user07803
    user07803 Member

    I gave that a shot and am still receiving the error from my previous reply.

  • AS
    AS Coach
    Answer ✓

    What does your entire transform look like?

    If this is a single column in your transform, then it looks fine to me.  In total, it should look something like 

     

    SELECT

    ...

    , CASE WHEN [your case statement]

    ...

    FROM

    ...

     

    Based on the error, it seems the problem starts where your case statement begins, which is either preceded by a comma, separating it from a previous column, or the initial SELECT word.

    Aaron
    MajorDomo @ Merit Medical

    **Say "Thanks" by clicking the heart in the post that helped you.
    **Please mark the post that solves your problem by clicking on "Accept as Solution"
  • lucywo
    lucywo Member
    Answer ✓

    Hey there,

     

    If you are working in the SQL environment, then check the codes below,

     

    select a,

    (CASE
    WHEN `Product Name` like '%YOUTUBE%' then 'YouTube'
    WHEN `Product Name` like '%AUDIO%' then 'Audio'
    WHEN `Product Name` like '%FENCING%' then 'Geo-Fencing'

    ELSE 'unassigned'
    END) as b

    from c

     

    Note: a - field you want to query; b - new field name; c - table name

     

    If it still doesnt work, try change `Product Name` to [Product Name] or if even its possible to remove the blank space inside your field name.(`Product Name` --> Product_Name)

     

    Thanks!

     

     

     

  • AS
    AS Coach
    Answer ✓

    @lucywo

    I think you'd be right on 1, but the OP said mySQL, not beast mode.

    I added the same type consistency comment in my initial response also, with the option of using NULL instead, though my inquiry about wich was preferred was not answered.

    Aaron
    MajorDomo @ Merit Medical

    **Say "Thanks" by clicking the heart in the post that helped you.
    **Please mark the post that solves your problem by clicking on "Accept as Solution"
  • lucywo
    lucywo Member

    @AS

     

    Thanks for correcting me.

     

    Yes your right. I have revised my answer.

     

    Thanks again!

Categories