Incorrect syntax near the keyword 'on'.

Question

Post reply

Incorrect syntax near the keyword 'on'.

amber0517

SSC Enthusiast

Points: 172
More actions
January 17, 2023 at 7:22 pm

#4138722

I keep hitting road blocks on this, I'm pretty new so I feel like this could be a really obvious one but im striking out
SELECT
f.user_id,
f.email,
f.start_date,
s.status,
i.loyalty AS number_of_boxes,
p.silos,
l.date_of_triggered_freebie_challenge_congrats_event,
p.token,
c.customer_id,
f.business_unit,
f.challenge_handle,
f.end_date
FROM
customer c
INNER JOIN freebie_challenge_notification f on c.user_id = f.user_id
LEFT JOIN subscription_delivery s on c.customer_id = s.customer_id
on (s.status = running or s.status = skipped)
LEFT JOIN communication_preferences p on c.customer_id = s.customer_id
on p.silos LIKE %hellofresh-gifts% AND on p.channels LIKE %email%
LEFT JOIN customer_insights i on c.customer_id = i.customer_id
on (i.loyalty = 2 or i.loyalty = 4 or i.loyalty = 7)
LEFT JOIN US_RAF_FiH_Customer_Challenge_Congrats_JourneyLog l on c.user_id = l.user_id
WHERE
(l.date_of_triggered_freebie_challenge_congrats_event < DATEADD(day, -7, GETDATE())
OR l.date_of_triggered_freebie_challenge_congrats_event IS NULL)

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 1

It looks like many of your ONs should be ANDs, instead.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Ed B Hall of Fame Points: 3666 More actions · Answer 2

Many new users would put the join filter criteria in the WHERE clause which would prevent the left joins from functioning as outer joins, so this is very good for a new user. I didn't figure this out for ages. As Jeff mentioned, just replace the second ON with AND for each join, and get rid of the "AND ON". You need some single quotes too. If i.loyalty is an integer then you don't need quotes. I assume the tables are part of the dbo schema, if not then change dbo to the correct schema.

INNER JOIN dbo.freebie_challenge_notification f on c.[user_id] = f.[user_id]
LEFT JOIN dbo.subscription_delivery s on c.customer_id = s.customer_id
     AND s.status IN ('running', 'skipped')
LEFT JOIN dbo.communication_preferences p on c.customer_id = s.customer_id
     AND p.silos LIKE '%hellofresh-gifts%' AND p.channels LIKE '%email%'
LEFT JOIN dbo.customer_insights i on c.customer_id = i.customer_id
     AND (i.loyalty IN ( 2, 4, 7)
LEFT JOIN dbo.US_RAF_FiH_Customer_Challenge_Congrats_JourneyLog l on c.[user_id] = l.[user_id]

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 3

I also missed the leading wildcards in the criteria... be advised that will cause a full-scan of the table/index. No index seek will be possible.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jonathan AC Roberts SSCoach Points: 18354 More actions · Answer 4

I would write you query like this:

SELECT f.user_id,
       f.email,
       f.start_date,
       s.status,
       i.loyalty AS number_of_boxes,
       p.silos,
       l.date_of_triggered_freebie_challenge_congrats_event,
       p.token,
       c.customer_id,
       f.business_unit,
       f.challenge_handle,
       f.end_date
  FROM customer c
 INNER JOIN freebie_challenge_notification f 
         ON f.user_id = c.user_id
  LEFT JOIN subscription_delivery s 
         ON s.customer_id = c.customer_id
        AND s.status IN('running', 'skipped')
  LEFT JOIN communication_preferences p 
         ON p.customer_id = c.customer_id
        AND p.silos LIKE '%hellofresh-gifts%'
        AND p.channels LIKE '%email%'
  LEFT JOIN customer_insights i 
         ON i.customer_id = c.customer_id
        AND i.loyalty IN (2, 4, 7)
  LEFT JOIN US_RAF_FiH_Customer_Challenge_Congrats_JourneyLog l 
         ON l.user_id = c.user_id
        AND (l.date_of_triggered_freebie_challenge_congrats_event < DATEADD(day, -7, GETDATE())
              OR l.date_of_triggered_freebie_challenge_congrats_event IS NULL);

Michael L John One Orange Chip Points: 27055 More actions · Answer 5

You also forgot to enclose your strings in single quotes.

Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/