1

I am running SQL activity which grabs rows from last 2 hours.

However, there will be instances when there is no rows found in last 2 hours and I'd like to set Default values in that case.

Attempt 1:

SELECT SubscriberID, JobID, ListID, BatchID, (CASE 
            WHEN EXISTS(
                        SELECT 1 
                        FROM _Sent
                        WHERE EventDate >= DateAdd(hh,-2,CURRENT_TIMESTAMP)
                    ) 
            THEN EventDate
            ELSE CURRENT_TIMESTAMP
        END) AS EventDate
FROM _Sent
WHERE EventDate >= DateAdd(hh,-2,CURRENT_TIMESTAMP)

This seems to not work as master WHERE fails to pull 0 records which won't run the CASE in SELECT statement.

Attempt 2:

SELECT CASE 
    WHEN EXISTS(
        SELECT 1 
        FROM _Sent
        WHERE EventDate >= DateAdd(hh,-2,CURRENT_TIMESTAMP)
    )
    THEN (
        SELECT SubscriberID, JobID, ListID, BatchID, EventDate
        FROM _Sent
        WHERE EventDate >= DateAdd(hh,-2,CURRENT_TIMESTAMP)
    ) 
    ELSE (
        SELECT 0 AS SubscriberID, 0 AS JobID, 0 AS ListID, 0 AS BatchID, CURRENT_TIMESTAMP AS EventDate
        FROM _Sent
    )
END

Error: An error occurred while checking the query syntax. Errors: Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.

Has anyone managed to set default value using SQL when it returns an empty row?

Note: The SQL activity is set to Overwrite action.

3

So, with some testing and investigation this is how I have resolved:

SELECT SubscriberID, JobID, ListID, BatchID, EventDate
FROM _Sent
WHERE EventDate >= DateAdd(hh,-2,CURRENT_TIMESTAMP)

UNION

SELECT 0 AS SubscriberID, 0 AS JobID, 0 AS ListID, 0 AS BatchID, CURRENT_TIMESTAMP AS EventDate
FROM _Sent
WHERE NOT EXISTS (
                SELECT 1
                FROM _Sent
                WHERE EventDate >= DateAdd(hh,-2,CURRENT_TIMESTAMP))

Explanation:

  • UNION brings the results from both tables however, if top table fails to bring in any rows, it will still add the default values to the resulting DE. Also note, I have added NOT EXISTS condition to the second table to make sure I don't add default value if row is found in first table.
| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.