I have data that can be populated between 2 fields. One may be null and the other may be populated. I have this expression that looks at one of the fields, I need to incorporate another case when expression or possibly an if then else to accomplish this.

max(case when key_id = 'program.partner' then split(identifier,':')[6] end) as partner_id

I need to incorporate this with the above expression

 max(case when key_id = 'partner' then identifier end)

I was thinking something like this, but it is not working

case when (max(case when key_id = 'program.partner' then split(identifier,':')[6]) is null then (max(case when key_id = 'partner' then identifier)) else '' end as partner_id,

I have data that can be populated between 2 fields. One may be null and the other may be populated. I have this expression that looks at one of the fields, I need to incorporate another case when expression or possibly an if then else to accomplish this.

max(case when key_id = 'program.partner' then split(identifier,':')[6] end) as partner_id

I need to incorporate this with the above expression

 max(case when key_id = 'partner' then identifier end)

I was thinking something like this, but it is not working

case when (max(case when key_id = 'program.partner' then split(identifier,':')[6]) is null then (max(case when key_id = 'partner' then identifier)) else '' end as partner_id,

• sql

• 3 Match your round brackets. You are missing a close bracket for your first open bracket. – Bart McEndree Commented Mar 19 at 18:23
• 1 Please provide both sample input data and expected result. It's unclear what exactly you want to do and therefore, we don't know whether you are on the right track. – Jonas Metzler Commented Mar 19 at 18:28
• 3 What SQL dialect uses split() ? – Bart McEndree Commented Mar 19 at 18:29

1 Answer 1

Considering No Input data or expected output is given, In general, I suggest try COALESCE and CASE expressions. The COALESCE function returns the first non-null value in the list of arguments. Here's an example of how you can incorporate both conditions into a single statement:

COALESCE(
    max(case when key_id = 'program.partner' then split(identifier, ':')[6] end),
    max(case when key_id = 'partner' then identifier end)
) as partner_id

Explanation:

1. CASE Expressions: Each CASE expression checks for a specific key_id and extracts the desired value.

2. MAX Function: The MAX function is used to ensure that we get the maximum value from the results of the CASE expressions.

3. COALESCE Function: The COALESCE function returns the first non-null value from the list of arguments. If the result of the first CASE expression is NULL, it will return the result of the second CASE statement.

This way, if the value for key_id = 'program.partner' is NULL, it will fall back to the value for key_id = 'partner'.