3

this is the query:

select
  opp.*
from
  (
  select 
    opp.*,
    row_number() over (partition by opp.contact_email_address order by opp.status_date desc) as row_number
from
  opportunity_data opp
where
    opp.email_bounced = 'false'
    and opp.email_unsubscribe = 'false'
    and opp.first_mkt_medium not in ('partner', inbound_outbound')
    and opp.latest_mkt_medium not in ('partner', 'inbound_outbound')
    and diffdate(day, cast(latest_rfq_submitted_date as date), cast(getdate() as date)) > 30
    and opp.on_cover = 'no'
    and opp.primary_group in ('Market_trader', 'Food_stand', 'Mobile_food_van', 'Caterer')
    and opp.site = 'simplybusiness'
    and opp.opportunity_status = ('quote_recieved', 'rfq_submitted', 'policy_expired_not_renewed')
 ) opp
where row_number = 1
4
  • 1
    Is this question about Salesforce CRM otherwise you are in the wrong site?
    – Dave Humm
    Commented Nov 8, 2016 at 15:02
  • 1
    This could possibly be a question about Marketing-Cloud, though I can't say for certain (never used/programmed for that particular SF product)
    – Derek F
    Commented Nov 8, 2016 at 15:32
  • 1
    This is a marketing-cloud question and should be re-opened. Commented Nov 10, 2016 at 12:11
  • This query was to be used in the MC Commented Jan 4, 2017 at 10:16

3 Answers 3

3

The error is kind of misleading, but you're missing a quote:

and opp.first_mkt_medium not in ('partner', inbound_outbound')

Should be:

and opp.first_mkt_medium not in ('partner', 'inbound_outbound')

The syntax highlighting in the question (at least on my device), revealed the error.

In the future, I suggest using something like Programmer's Notepad in SQL mode when writing queries; simply having syntax highlighting can often point out minor typos like this.

2

The error might not have anything to do with Square Brackets.

You might also get it because of syntax error, or even a SQL comment block

(in between /* SQL COMMENT */).

Try eliminating all comments in the code, which might simply solve your problem.

Even more specifically, I have seen this when the comment block is between SELECT columns (before or after a comma)

0

To add to the above answers, the issue might appear because of unmatched quote ' characters in the whole query, meaning that if you use it in the comment block odd number of times, it can cause issues. Also, not in this specific case but in general, this error can appear when column names contain space or hyphen - and are not enclosed within square brackets.

You must log in to answer this question.

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