0

I have a Data Extension with daily sendLogs and I want to get the client_id with the EmailID=XXXX and ActivityId = XXXXXXXXX and add 1 to the field N_Ciclos of "Contadores" DE (target DE).

I have this update query:

SELECT
    s.client_id,
    c.N_Ciclos + 1 AS N_Ciclos,
    CASE
        WHEN s.EmailID = '596' AND s.ActivityID = '746c6513-7ea8-4896-9b8f-ab4442ba4d90' THEN 'Jornada 7.1'
    END AS Jornada,
    
FROM
    SendLog_Diario s
LEFT JOIN
    Contadores c ON s.client_id = c.client_id
WHERE
    s.EmailID = '596' AND s.ActivityID = '746c6513-7ea8-4896-9b8f-ab4442ba4d90'
GROUP BY
    s.client_id, s.EmailID, s.ActivityID, c.N_Ciclos

When the client_id already exists in the DE "Contadores", the query results as expected but when the client_id does not exist in DE, the query adds a row on the DE but the field "N_Ciclos" is set to 0 instead of 1.

How can I fix it?

Thank you!

1 Answer 1

0

N_Ciclos would be null if client_id does not exist. Not entirely sure about your use case but you can try to use the function COALESCE in your select statement:

SELECT
    s.client_id,
    COALESCE(c.N_Ciclos, 0) + 1 AS N_Ciclos,
    CASE
        WHEN s.EmailID = '596' AND s.ActivityID = '746c6513-7ea8-4896-9b8f-ab4442ba4d90' THEN 'Jornada 7.1'
    END AS Jornada
FROM
    SendLog_Diario s
LEFT JOIN
    Contadores c ON s.client_id = c.client_id
WHERE
    s.EmailID = '596' AND s.ActivityID = '746c6513-7ea8-4896-9b8f-ab4442ba4d90'
GROUP BY
    s.client_id, s.EmailID, s.ActivityID, c.N_Ciclos

And here's the dbfiddle link: https://www.db-fiddle.com/f/9XDzGeD1psoynYKMBH31aD/0

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .