0

Everyone

The below query is working fine without bounce_rate condition. When I am adding the bounce_rate it is throwing an error. Anyone please suggest on this.

SELECT
j.emailname, j.emailsubject, s.JobID ,TotalSends ,TotalBounces ,(TotalSends - TotalBounces) AS TotalDelivered, CAST((CAST(TotalBounces AS FLOAT)/CAST(TotalSends AS FLOAT))*100 AS FLOAT) AS Bounce_RATE

FROM

(SELECT
JobID
,MAX(row) as TotalSends
FROM
    (SELECT
    JobID ,row_number()over(partition by JobID order by SubscriberKey ASC) as row
    FROM _Sent ) 

f GROUP BY f.JobID) s

INNER JOIN 

(SELECT
JobID
,MAX(row) as TotalBounces
FROM
    (SELECT
    JobID ,row_number()over(partition by JobID order by SubscriberKey ASC) as row
    FROM _Bounce ) 
d GROUP BY d.JobID) b

ON s.JobID = b.JobID Inner join _job j on s.jobid = j.jobid
1

Marketing Cloud Query Studio doesn't like CAST in SELECT. You are getting an error there, as it interprets AS in CAST to be aliasing AS. It should work, however, in the Query Activity of Automation Studio.

Regarless, you can make it work in both places by changing CAST to CONVERT:

CONVERT(FLOAT,(CONVERT(FLOAT,TotalBounces)/CONVERT(FLOAT,TotalSends))*100) AS Bounce_RATE

Depending on the use case, you might consider using the added power of the CONVERT by defining precision and scale:

CONVERT(DECIMAL(3,2),(CONVERT(DECIMAL(3,4),TotalBounces)/CONVERT(DECIMAL(3,4),TotalSends))*100) AS Bounce_RATE

Example output will be 92.45 instead of 92.4482 with the FLOAT.

You can check out more about CAST vs CONVERT and SFMC limitations in my docs.

2
  • 1
    I wouldn't necessarily say SFMC doesn't like CAST (except Query Studio, which really is not something to rely on for syntax in SFMC) it is just that CONVERT has more options to it and is acceptable in Query Studio. SFMC uses CAST fine in a Query Activity, CONVERT just has better options BUT in some cases, CAST can outperform CONVERT. So its really up to use case as to which is better option. (This has some good insight into performance: mssqltips.com/sqlservertip/3018/…) – Gortonington Mar 9 at 13:53
  • 1
    Good point @Gortonington - updated my answer to be more strict. – Mateusz Dąbrowski Mar 9 at 14:44

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.