1

I'm experiencing an odd error trying to combine multiple statements in my WHERE clause in SQL.

For example, this statement runs fine and does exactly what i would expect:

select
abr.[Email Address] as [SubscriberKey],
abr.[date_triggered],
abr.[ProductRatingRef1],
abr.[NumberOfReviewsRef1],
abr.[ProductNameRef1],
abr.[ProductCategoryRef1],
abr.[ProductTypeRef1],
abr.[GBP_RegularPriceRef1],
abr.[SkuIDRef1],
abr.[ProductCodeRef1],
abr.[ProductLinkRef1],
abr.[ImageLinkRef1]

from [Abandon Browse Log] abr

where (abr.[Email Address] not in ( 
  select bkt.CustomerID
  from [Abandoned Basket Source] bkt
))

Similarly, this statement runs how i would expect, when i run it on the data extension populated by the above query:

select * from [Abandon Browse Campaign v2]

where convert(date, ([date_triggered])) = convert(date, (getdate() -1))

Even running this query works fine:

select
abr.[Email Address] as [SubscriberKey],
abr.[date_triggered],
abr.[ProductRatingRef1],
abr.[NumberOfReviewsRef1],
abr.[ProductNameRef1],
abr.[ProductCategoryRef1],
abr.[ProductTypeRef1],
abr.[GBP_RegularPriceRef1],
abr.[SkuIDRef1],
abr.[ProductCodeRef1],
abr.[ProductLinkRef1],
abr.[ImageLinkRef1]

from [Abandon Browse Log] abr

where
(convert(date, ([date_triggered])) = convert(date, (getdate() -1)))

However, to get around the issue of using 2 queries and 2 (3?) Data Extensions, I am trying to combine both in to one as below:

select
abr.[Email Address] as [SubscriberKey],
abr.[date_triggered],
abr.[ProductRatingRef1],
abr.[NumberOfReviewsRef1],
abr.[ProductNameRef1],
abr.[ProductCategoryRef1],
abr.[ProductTypeRef1],
abr.[GBP_RegularPriceRef1],
abr.[SkuIDRef1],
abr.[ProductCodeRef1],
abr.[ProductLinkRef1],
abr.[ImageLinkRef1]

from [Abandon Browse Log] abr

where (abr.[Email Address] not in ( 
  select bkt.CustomerID
  from [Abandoned Basket Source] bkt
))
and 
(convert(date, ([date_triggered])) = convert(date, (getdate() -1)))

I would expect this query to work, as both of the above work when separate, but not when combined. The only error i get out of salesforce is :

The query syntax is invalid Error while parsing query. Please check the syntax

  • What is the common field between [Abandoned Basket Source] and [Abandon Browse Log]? – Adam Spriggs Jun 13 '17 at 11:51
  • Hi Adam, the common field is [Email Address] – cantlon Jun 13 '17 at 12:33
2

If [Email Address] is the common field between [Abandoned Basket Source] and [Abandon Browse Log] then you could do something like this:

select
  abr.[Email Address] as [SubscriberKey]
, abr.[date_triggered]
, abr.[ProductRatingRef1]
, abr.[NumberOfReviewsRef1]
, abr.[ProductNameRef1]
, abr.[ProductCategoryRef1]
, abr.[ProductTypeRef1]
, abr.[GBP_RegularPriceRef1]
, abr.[SkuIDRef1]
, abr.[ProductCodeRef1]
, abr.[ProductLinkRef1]
, abr.[ImageLinkRef1]
from [Abandon Browse Log] abr
where convert(date, abr.date_triggered) = convert(date, getdate() -1)
and abr.[Email Address] not in ( 
  select bkt.[Email Address]
  from [Abandoned Basket Source] bkt
)

or

select
  abr.[Email Address] as [SubscriberKey]
, abr.[date_triggered]
, abr.[ProductRatingRef1]
, abr.[NumberOfReviewsRef1]
, abr.[ProductNameRef1]
, abr.[ProductCategoryRef1]
, abr.[ProductTypeRef1]
, abr.[GBP_RegularPriceRef1]
, abr.[SkuIDRef1]
, abr.[ProductCodeRef1]
, abr.[ProductLinkRef1]
, abr.[ImageLinkRef1]
from [Abandon Browse Log] abr
left join [Abandoned Basket Source] bkt on bkt.[Email Address] = abr.[Email Address]
where convert(date, abr.date_triggered) = convert(date, getdate() -1)
and bkt.[Email Address] is null
| improve this answer | |
1

Try to switch the position of the conditions in the WHERE clause. I just tested and it worked for me.

select
abr.[Email Address] as [SubscriberKey],
abr.[date_triggered],
abr.[ProductRatingRef1],
abr.[NumberOfReviewsRef1],
abr.[ProductNameRef1],
abr.[ProductCategoryRef1],
abr.[ProductTypeRef1],
abr.[GBP_RegularPriceRef1],
abr.[SkuIDRef1],
abr.[ProductCodeRef1],
abr.[ProductLinkRef1],
abr.[ImageLinkRef1]

from [Abandon Browse Log] abr

where (convert(date, ([date_triggered])) = convert(date, (getdate() -1)))

and 

(abr.[Email Address] not in ( 
  select bkt.CustomerID
  from [Abandoned Basket Source] bkt
))
| improve this answer | |

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.