How can I resolve this syntax error?

Question

Im trying to write a query to pull customer data for an email. 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.site = 'wesellinsurance'
 and opp.email_bounced = 'false'
 and opp.email_unsubscribed = 'false'
 and opp.first_mkt_medium not in ('partner', 'inbound_outbound')
 and opp.latest_mkt_medium not in ('partner','inbound_outbound')
 and opp.on_cover = 'false'
 and opp.primary_group in ('market_trader', 'food_stand', 'mobile_food_van', 'caterer')
 and opp.opportunity_status = ('quote_recieved','rfq_submitted', 'policy_expired_not_renewed')
 and datediff(day, cast(latest_rfq_submitted_date as date),cast(getdate() as date)) > 30
 and opp.lifecycle = 'new_business'
 ) opp
where row_number = 1

most syntax analysers say theres an issue on line 7 but i cant seem to see it

You are using SQL Server syntax with a MySQL tag. Switching databases might be advised. — Gordon Linoff
– Gordon Linoff, Commented Nov 8, 2016 at 12:07

Jaydip Jadhav · Accepted Answer · 2016-11-08 12:21:18Z

1

In is missing on line 18

 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.site = 'wesellinsurance'
        and opp.email_bounced = 'false'
        and opp.email_unsubscribed = 'false'
        and opp.first_mkt_medium not in ('partner', 'inbound_outbound')
        and opp.latest_mkt_medium not in ('partner','inbound_outbound')
        and opp.on_cover = 'false'
        and opp.primary_group in ('market_trader', 'food_stand', 'mobile_food_van', 'caterer')
        and opp.opportunity_status in ('quote_recieved','rfq_submitted', 'policy_expired_not_renewed')
                     --------------^
        and datediff(day, cast(latest_rfq_submitted_date as date),cast(getdate() as date)) > 30
        and opp.lifecycle = 'new_business'
     ) opp
    where row_number = 1

edited Nov 8, 2016 at 12:21

Jaydip Jadhav

12.3k6 gold badges26 silver badges41 bronze badges

answered Nov 8, 2016 at 12:13

Anwar Ul-haq

1,9111 gold badge18 silver badges29 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

TastyBurgers Over a year ago

winner winner chicken dinner

Parshuram Kalvikatte · Accepted Answer · 2016-11-08 13:16:46Z

Error is (there should be (in) instead of (=) and always specify a table name BEST PRACTISE (opp1.row_number))

 select
         opp1.*
        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.site = 'wesellinsurance'
         and opp.email_bounced = 'false'
         and opp.email_unsubscribed = 'false'
         and opp.first_mkt_medium not in ('partner', 'inbound_outbound')
         and opp.latest_mkt_medium not in ('partner','inbound_outbound')
         and opp.on_cover = 'false'
         and opp.primary_group in ('market_trader', 'food_stand', 'mobile_food_van', 'caterer')
         and opp.opportunity_status in ('quote_recieved','rfq_submitted', 'policy_expired_not_renewed')
         and datediff(day, cast(latest_rfq_submitted_date as date),cast(getdate() as date)) > 30
         and opp.lifecycle = 'new_business'
         ) opp1
        where opp1.row_number = 1

Collectives™ on Stack Overflow

How can I resolve this syntax error?

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related