Case statement not working

jp2631 · 2023-08-09T13:31:52+00:00

There was an error rendering this rich post.

jp2631

Please help with my ETL formular.

I'm not sure why my case only works for…

WHEN AdditionalRecruiter = '' and RecruiterInOrder = 1 THEN 'Unassigned Owner'

WHEN STR_CONTAINS(AdditionalRecruiter,',') THEN AllRecruiter

WHEN AllRecruiter IS NULL THEN 'N/C'

Below is the code I'm using to populate the 'RecruiterOutcome' column. I'm using N/A, N/B, N/C, and N/D to differentiate between cases. The goal is to fill the 'RecruiterOutcome' column with any flag in the blank rows, so that I can filter them out later.

CASE WHEN AdditionalRecruiter = '' and RecruiterInOrder = 1 THEN 'Unassigned Owner'
WHEN STR_CONTAINS(AdditionalRecruiter,',') THEN AllRecruiter
WHEN STR_CONTAINS(AdditionalRecruiter,',') and AllRecruiter IS NULL THEN 'N/A'
WHEN STR_CONTAINS(AdditionalRecruiter,',') and AllRecruiter = '' THEN 'N/B'
WHEN AllRecruiter IS NULL THEN 'N/C'
WHEN AllRecruiter = '' THEN 'N/D'
ELSE AllRecruiter
END

Find more posts tagged with

Accepted answers

GrantSmith

Case statements are evaluated from the top down and will stop when they find a condition that's true.

Because you have

WHEN STR_CONTAINS(AdditionalRecruiter,',') THEN AllRecruiter

At the top of your case statement the other two below it won't ever get evaluated. Put the N/A and N/B conditions above this to have them evaluated first.

jp2631

@GrantSmith Thank you for your quick response. Your suggestion works well! I also realized that I made things too complicated. My formula should be as simple as the below code, then add a filter after to filter out the NULL rows in the 'RecruiterOutcome' column.

Problem solved!!! 😀

CASE WHEN AdditionalRecruiter = '' and RecruiterInOrder = 1 THEN 'Unassigned Owner'
WHEN STR_CONTAINS(AdditionalRecruiter,',') THEN AllRecruiter
ELSE AllRecruiter
END

All comments

GrantSmith

Case statements are evaluated from the top down and will stop when they find a condition that's true.

Because you have

WHEN STR_CONTAINS(AdditionalRecruiter,',') THEN AllRecruiter

At the top of your case statement the other two below it won't ever get evaluated. Put the N/A and N/B conditions above this to have them evaluated first.

jp2631

Problem solved!!! 😀

CASE WHEN AdditionalRecruiter = '' and RecruiterInOrder = 1 THEN 'Unassigned Owner'
WHEN STR_CONTAINS(AdditionalRecruiter,',') THEN AllRecruiter
ELSE AllRecruiter
END