2

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
  • 1
    Is this question about Salesforce CRM otherwise you are in the wrong site? – Dave Humm Nov 8 '16 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 Nov 8 '16 at 15:32
  • 1
    This is a marketing-cloud question and should be re-opened. – Adam Spriggs Nov 10 '16 at 12:11
  • This query was to be used in the MC – TastyBurgers Jan 4 '17 at 10:16
2

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.

| 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.