0

I have a sql-query where i do not know my mistake therefore it would be nice if you could help.

SELECT
SUM(b.HardBounceCount) AS HardBounceCount,
SUM(b.SoftBounceCount) AS SoftBounceCount, 
SUM(b.BlockBounceCount) AS BlockBounceCount,
SUM(b.UnknownBounceCount) AS UnknownBounceCount, 
SUM(b.TechnicalBounceCount) AS TechnicalBounceCount,
SUM(b.BounceCount) AS BounceCount,
SUM(b.SendCount) AS SendCount,
b.Domain AS Domain
FROM [SubscriberBounces] b
GROUP BY b.Domain

My source data extension looks like (other values than 0 and 1 possible):enter image description here

My target data extension looks like: enter image description here

Do you have any advice how i can correct my query? Additionally can i insert BouncePercent directly when i add an additional line like

cast(round((SUM(b.BounceCount)*100.0)/SUM(b.SendCount),2) as numeric(5,2)) as BouncePercent,

Furthermore i have seen that datakid / Gortonington / adam spriggs have been dumping the error results in data extensions (but i could not find any of these posts, if you can share how i can dump the sql error in a data extension it would be nice too)

Errors: First - Activity View Instant Error: enter image description here Inside Automation when you try to run once: enter image description here Inside Automation Studio Picture 2: enter image description here

First try to solve it without success:

SELECT
SUM(b.HardBounceCount) AS HardBounceCount,
SUM(b.SoftBounceCount) AS SoftBounceCount, 
SUM(b.BlockBounceCount) AS BlockBounceCount,
SUM(b.UnknownBounceCount) AS UnknownBounceCount, 
SUM(b.TechnicalBounceCount) AS TechnicalBounceCount,
SUM(b.BounceCount) AS BounceCount,
SUM(b.SendCount) AS SendCount,
cast(round((SUM(b.BounceCount)*100.0)/SUM(b.SendCount),2) as numeric(5,2)) as BouncePercent,
LEFT(b.Domain,128) AS Domain
FROM [SubscriberBounces_LastMonth] b
GROUP BY b.Domain
9
  • You can see some errors if you run your Query Activity in an Automation. Jul 17, 2018 at 13:13
  • When you say it's crashing, what exactly do you mean? The syntax is valid -- even with the BouncePercent column included. Jul 17, 2018 at 13:18
  • Why is BouncePercent a string in your target DE? Jul 17, 2018 at 13:19
  • Because of this post help.salesforce.com/… my BouncePercent is text. Jul 17, 2018 at 13:25
  • 1
    It's likely a truncation or datatype mismatch. Query errors can only be one of four things. Jul 17, 2018 at 13:41

1 Answer 1

2

I set this up in my local SQL Server Management Studio with some test data and got it to run fine:

SELECT
SUM(b.HardBounceCount) AS HardBounceCount,
SUM(b.SoftBounceCount) AS SoftBounceCount, 
SUM(b.BlockBounceCount) AS BlockBounceCount,
SUM(b.UnknownBounceCount) AS UnknownBounceCount, 
SUM(b.TechnicalBounceCount) AS TechnicalBounceCount,
SUM(b.BounceCount) AS BounceCount,
cast(round((SUM(b.BounceCount)*100.0)/SUM(b.SendCount),2) as numeric(5,2)) as BouncePercent,
SUM(b.SendCount) AS SendCount,
b.Domain AS Domain
FROM [dbo].[subscriberBounces] b
GROUP BY b.Domain

query results

2
  • Do you generally develop your queries on a local development system to test and analyse your queries? If so which setup do you use for it? Do you use function like EXPLAIN ANALYZE for it? Jul 17, 2018 at 14:09
  • Sometimes. It depends on the complexity of the query or queries involved. I do frequently use SQL Server Management Studio for basic query validation. That kind of syntax validation isn't dependent on any schema. Jul 17, 2018 at 14:23

You must log in to answer this question.

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