CASE WHEN... AND in SQL ERROR

PI_Science
PI_Science Member
in Magic ETL

I'm working in MYSQL and trying to write a CASE WHEN Statement with multiple AND X<Y conditions. When it is finished, the statement below will be much longer with 15 more AND conditions, but right now this shorter version won't verify. The purpose of this statement is simply to find the lowest value across 15 columns for each row. Thoughts on what is going wrong (or perhaps a simplier way to find the minimum?? (The "min" function only finds the lowest value out of all the rows in the column (and not out of all the columns in a row, whic is what I'm trying to find). Thanks!

 

SELECT
CASE WHEN `Venturer_Dist`< `Strategist_Dist` AND `Venturer_Dist`< `Operator_Dist`
THEN 'Venturer'
END
FROM transform_data_3

Best Answer

  • RGranada
    RGranada Contributor
    Answer ✓

    Ok,

     

    Try:

     

    SELECT
    CASE WHEN `Venturer_Dist`< `Strategist_Dist` AND `Venturer_Dist`< `Operator_Dist`
    THEN 'Venturer' END  AS ColumnName
    FROM transform_data_3

    changes in Bold italic

     

    If you are using this is a dataflow try also to loose the "`" :

     

    SELECT
    CASE WHEN Venturer_Dist < Strategist_Dist  AND  Venturer_Dist <  Operator_Dist
    THEN 'Venturer'  END  AS ColumnName
    FROM transform_data_3

     

    Let me know how it goes.

    Ricardo Granada 

    MajorDomo@Lusiaves

    **If the post solves your problem, mark it by clicking on "Accept as Solution"
    **You can say "Thank you" by clicking the thumbs up in the post that helped you.

Answers

  • RGranada
    RGranada Contributor

    Hi,

     

    What's the error?

     

    Can/did you run transform_data_3 correctly?

     

    Regards,

    Ricardo Granada 

    MajorDomo@Lusiaves

    **If the post solves your problem, mark it by clicking on "Accept as Solution"
    **You can say "Thank you" by clicking the thumbs up in the post that helped you.
  • PI_Science
    PI_Science Member

    Hi Ricardo,

     

    This is the error message:

     

     The database reported a syntax error. Incorrect column name 'CASE WHEN `Venturer_Dist`< `Strategist_Dist` AND `Venturer_Dist`< `Operator_Dist` THEN 'Venturer' E'

     

    transform_data_3 is running smoothly!

     

    Thanks,

     

    Sarah

     

  • RGranada
    RGranada Contributor
    Answer ✓

    Ok,

     

    Try:

     

    SELECT
    CASE WHEN `Venturer_Dist`< `Strategist_Dist` AND `Venturer_Dist`< `Operator_Dist`
    THEN 'Venturer' END  AS ColumnName
    FROM transform_data_3

    changes in Bold italic

     

    If you are using this is a dataflow try also to loose the "`" :

     

    SELECT
    CASE WHEN Venturer_Dist < Strategist_Dist  AND  Venturer_Dist <  Operator_Dist
    THEN 'Venturer'  END  AS ColumnName
    FROM transform_data_3

     

    Let me know how it goes.

    Ricardo Granada 

    MajorDomo@Lusiaves

    **If the post solves your problem, mark it by clicking on "Accept as Solution"
    **You can say "Thank you" by clicking the thumbs up in the post that helped you.
  • PI_Science
    PI_Science Member

    The first solution worked! Thanks for your help!

Categories