[SalesForce] Errors: Incorrect syntax near ‘LIMIT’

I am trying to query only 10 records and using LIMIT in query, but it is giving me the following error :

An error occurred while checking the query syntax. Errors: Incorrect
syntax near 'LIMIT'.

Following is my query:

SELECT SubscriberKey, EmailAddress 
    FROM MASTER_IMPORT
        WHERE EmailAddress LIKE "%gmail.com" LIMIT 10

Anything that I'm doing wrong here?

Best Answer

SFMC uses T-SQL syntax, so you need to rewrite your query using the TOP expression instead of LIMIT.

SELECT TOP(10) SubscriberKey, EmailAddress 
FROM MASTER_IMPORT
WHERE EmailAddress LIKE "%gmail.com"

Also refer to official documentation for more details on the TOP expression:

Limits the rows returned in a query result set to a specified number of rows or percentage of rows.

In a SELECT statement, always use an ORDER BY clause with the TOP clause. This is the only way to predictably indicate which rows are affected by TOP.

Related Solutions

[SalesForce] Data extension Inner Join – Incorrect syntax near the keyword ‘WHERE’

When conducting a JOIN function, you need to specify what the 2 tables are joining on.

Try this:

SELECT 
[Account ID] AS 'Account ID'
,[Created Date Backend] AS 'Created Date'
FROM [DE A] A
INNER JOIN [DE B] B ON A.[Account ID] = B.[Lead ID]

Note that you may need to specify what table Account ID and Created Date Backend come from.

Reference: https://www.w3schools.com/sql/sql_join.asp

This image will help you understand the basics of JOINs:

[SalesForce] Marketing Cloud query activity syntax error

There's no aggregation/grouping going on in your query there to do the "> 0" evaluation.

Try this:

SELECT 
      csf.Id as SubscriberKey, 
      csf.channel__c as Channel, 
      COUNT(1) as HasHardBounced
FROM
    Contact_Salesforce csf INNER JOIN
    _Bounce b WITH (NOLOCK)
        ON  b.SubscriberKey = csf.Id
WHERE
    b.BounceCategory = 'Hard bounce'
GROUP BY
    csf.Id
HAVING
    COUNT(1) > 0

Best Answer

Related Solutions

[SalesForce] Data extension Inner Join – Incorrect syntax near the keyword ‘WHERE’

[SalesForce] Marketing Cloud query activity syntax error

Related Topic