When I try the following test of a subquery in SFMC Query Studio, I get the error message "Unable to create temporary data extension: Field name cannot be blank."

According to official Salesforce documentation, SFMC's flavor of SQL is SQL Server 2016 with some listed limitations, and subqueries are not mentioned as not supported. And according to official SQL Server 2016 documentation my syntax below is correct.

Note that this is just a test query for simplicity's sake, and is not supposed to have practical value.

    SELECT StageName
    FROM Opportunity_Salesforce AS o1
    WHERE o1.ContactId = c.Id 
) AS test
FROM Contact_Salesforce AS c

Query Studio is prone to toss errors that do not happen inside of a real Query Activity - so keep it in mind that even if it doesn't work in Query Studio does not mean that you cannot use it in SFMC.

This is one of those cases. Query studio tosses a very vague and incorrect error on the syntax, but if you push it into a Query Activity in Automation Studio, it will validate and work as expected.

I tested a query like this in my own account:

SELECT TOP 10 a.[Subscriber Key],
    SELECT TOP 1 COUNT(b.[Subscriber Key]) as ct 
    FROM [testDE] 
    WHERE b.[Subscriber Key] = a.[Subscriber Key]
) as Cnt
FROM [testDE] a

and it validated and ran no problem, returning the expected results. So to answer your question, yes they are supported, just not always in Query Studio (it seems to work well in some places though).

  • Thanks, I'm sure you're trying to be helpful, but I can't tell whether you answered the title question. Your counter-suggestion, by the way, is not relevant because the point of the subquery is that I'm preparing fields in the result set that each are calculated using different joins. As I said in the OP, the query shown here is just a simple demo, with all my fancy logic removed, and all the other subqueries that use different join criteria for their calculations. – alanng yesterday
  • As I stated in there, Query Studio tosses errors that are not errors in a real Query Activity. I will adjust my answer to make this more prominent. But I just tested in my account to verify and subqueries exactly like the example you showed work as expected in a Query Activity despite the error tossed in Query Studio. – Gortonington yesterday
  • 1
    Thanks @Gortonington for proving that the problem is Query Studio, not SFMC not supporting subqueries. – alanng yesterday

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.