HELP! Can not get this ETL Formula to Validate

Can not get this ETL Formula to Validate

CASE WHEN HdrParentItemCode IN ('ZROUGHIN', 'ZWATER', 'ZSEWER', 'ZROCK', 'ZVANGUARD') THEN 'Rough-IN'
ELSE
CASE WHEN HdrParentItemCode IN ('ZTOPOUT', 'ZGAS') THEN 'Topout'
ELSE
CASE WHEN HdrParentItemCode IN ('ZTRIM','ZDROPIN') THEN 'Trim'
ELSE
CASE WHEN HdrParentItemCode IN ('ZPUNCH') THEN 'Punch'
ELSE
'Others'
END

Best Answers

  • GrantSmith
    GrantSmith Coach
    Answer ✓

    Only the first CASE keyword is needed you can remove the others but leave them when and clauses

    **Was this post helpful? Click Agree or Like below**
    **Did this solve your problem? Accept it as a solution!**
  • MichelleH
    MichelleH Coach
    Answer ✓

    @jtrollinger In addition to Grant's feedback, you only need one ELSE to designate 'Others'

  • GrantSmith
    GrantSmith Coach
    Answer ✓

    Good clarification @MichelleH

    @jtrollinger it should end up with something like this

    CASE WHEN `HdrParentItemCode` IN ('ZROUGHIN', 'ZWATER', 'ZSEWER', 'ZROCK', 'ZVANGUARD') THEN 'Rough-IN'

    WHEN `HdrParentItemCode` IN ('ZTOPOUT', 'ZGAS') THEN 'Topout'

    WHEN `HdrParentItemCode` IN ('ZTRIM','ZDROPIN') THEN 'Trim'

    WHEN `HdrParentItemCode` IN ('ZPUNCH') THEN 'Punch'

    ELSE

    'Others'

    END

    **Was this post helpful? Click Agree or Like below**
    **Did this solve your problem? Accept it as a solution!**

Answers

  • GrantSmith
    GrantSmith Coach
    Answer ✓

    Only the first CASE keyword is needed you can remove the others but leave them when and clauses

    **Was this post helpful? Click Agree or Like below**
    **Did this solve your problem? Accept it as a solution!**
  • Thanks Grant. Syntax is everything.

  • MichelleH
    MichelleH Coach
    Answer ✓

    @jtrollinger In addition to Grant's feedback, you only need one ELSE to designate 'Others'

  • GrantSmith
    GrantSmith Coach
    Answer ✓

    Good clarification @MichelleH

    @jtrollinger it should end up with something like this

    CASE WHEN `HdrParentItemCode` IN ('ZROUGHIN', 'ZWATER', 'ZSEWER', 'ZROCK', 'ZVANGUARD') THEN 'Rough-IN'

    WHEN `HdrParentItemCode` IN ('ZTOPOUT', 'ZGAS') THEN 'Topout'

    WHEN `HdrParentItemCode` IN ('ZTRIM','ZDROPIN') THEN 'Trim'

    WHEN `HdrParentItemCode` IN ('ZPUNCH') THEN 'Punch'

    ELSE

    'Others'

    END

    **Was this post helpful? Click Agree or Like below**
    **Did this solve your problem? Accept it as a solution!**