1

I am creating a Query Activity in the Automation Studio. The query gives the following message in red:

An error occurred while checking the query syntax. Errors: Error while parsing query. Please check the syntax

One usually gets more information about this in the Activity tab of the automation. But what should I do when I cannot even save this query (the step before choosing the destination Data Extension)?

I have gone through these steps in Adam Spriggs blog. But none of them seem to apply here.

I just need to know more about the query. It seems to work fine on the SQL Server database where it originally comes from.

UPDATE: The query code

SELECT DISTINCT a.PassengerId,
       a.BookingKey,
       a.FlightId,
       a.Email,
       a.FirstDepartureDateTime,
       a.Origin,
       a.Destination,
       a.FirstName,
       a.LastName,
       a.Gender,
       a.FrequentFlyerId,
       a.HasAncillaries,
       a.HasAncillaryBioFuel
FROM
  (SELECT DISTINCT t8.BookingKey,
                   t8.Email,
                   t8.PassengerId,
                   t8.FirstDepartureDateTime,
                   t8.FirstName,
                   t8.LastName,
                   t8.FrequentFlyerId,
                   t9.refFirst_FlownTransactionID AS FlightId,
                   Gender,
                   HasAncillaries,
                   HasAncillaryBioFuel,
                   Destination,
                   Origin
   FROM
     (SELECT DISTINCT t1.BookingKey,
                      t3.Email,
                      t3.PassengerID,
                      t2.FirstDepartureDateTime,
                      t3.FirstName,
                      t3.LastName,
                      t3.FrequentFlyerId,
                      t3.Gender,
                      t2.HasAncillaries,
                      t2.HasAncillaryBioFuel,
                      t2.Destination,
                      t2.FirstDepartureStation AS Origin
      FROM [Flights] AS t1
      INNER JOIN [Bookings] AS t2 ON (t1.BookingKey = t2.BookingKey)
      INNER JOIN [Customers] AS t3 ON (t1.PassengerID = t3.PassengerID)
      WHERE 1 = 1
        AND convert(date,t2.BookingDateTime) = convert(date, dateadd(DAY, -2, getdate()))
        AND IsBooked = 1
        AND IsPassenger = 1
        AND t1.IsCancelled = 0
        AND t1.IsCrew = 0
        AND t1.RBD NOT IN ('I',
                           'Z',
                           'R')
        AND t1.PassengerType NOT IN ('CHD')
        AND t2.SalesChannel <> 'Travel Agency'
        AND t3.IsBRAMember = 1
        AND t3.CommunicationOptIn = 1
        AND t3.FrequentFlyerId = t1.FrequentFlyerId
        AND DATEDIFF(DAY, t2.BookingDateTime, t2.FirstDepartureDateTime) >= 3
        AND t3.FirstBookingEverKey = t1.BookingKey ) AS t8
   INNER JOIN
     (SELECT DISTINCT BookingKey,
                      PassengerID ,
                      FIRST_VALUE(FlownTransactionID) OVER (PARTITION BY BookingKey,
                                                                         PassengerID
                                                            ORDER BY BookingKey,
                                                                     PassengerID,
                                                                     ScheduledDepartureDateTime) AS refFirst_FlownTransactionID
      FROM [Flights]) AS t9 ON (t8.BookingKey = t9.BookingKey
                                       AND t8.PassengerID = t9.PassengerID)
   UNION SELECT DISTINCT t8.BookingKey,
                         t8.Email,
                         t8.PassengerId,
                         t8.FirstDepartureDateTime,
                         NULL AS FirstName,
                         NULL AS LastName,
                         NULL AS FrequentFlyerId,
                         refFirst_FlownTransactionID AS FlightId,
                         Gender,
                         HasAncillaries,
                         HasAncillaryBioFuel,
                         Destination,
                         Origin
   FROM
     (SELECT DISTINCT BookingKey,
                      Email,
                      FIRST_VALUE(PassengerID) OVER (PARTITION BY BookingKey
                                                     ORDER BY BookingKey,
                                                              first3CharMatch DESC, CountTrips12M DESC, PassengerID DESC) AS PassengerId,
                                                    FirstDepartureDateTime,
                                                    NULL AS FirstName,
                                                    NULL AS LastName,
                                                    NULL AS FrequentFlyerId,
                                                    Gender,
                                                    HasAncillaries,
                                                    HasAncillaryBioFuel,
                                                    Destination,
                                                    Origin
      FROM
        (SELECT DISTINCT t1.BookingKey,
                         t2.BookingDateTime,
                         t2.email_booker,
                         t3.Email,
                         t2.FirstDepartureDateTime,
                         CASE
                             WHEN LEFT(replace(replace(replace(replace(lower(t1.PassengerFirstName), 'ø', 'o'), 'å', 'a'), 'ä', 'a'), 'ö', 'o'), 3) = LEFT(t2.email_booker, 3) THEN 1
                             ELSE 0
                         END AS first3CharMatch,
                         t3.CountTrips12M,
                         t3.IsBRAMember,
                         t1.PassengerID,
                         t1.PassengerFirstName,
                         t3.Gender,
                         t2.HasAncillaries,
                         t2.HasAncillaryBioFuel,
                         t2.Destination,
                         t2.FirstDepartureStation AS Origin
         FROM [Flights] AS t1
         INNER JOIN [Bookings] AS t2 ON (t1.BookingKey = t2.BookingKey)
         INNER JOIN [Customers] AS t3 ON (t1.PassengerID = t3.PassengerID)
         WHERE 1 = 1
           AND convert(date, t2.BookingDateTime) = convert(date, dateadd(DAY, -2, getdate()))
           AND t1.IsBooked = 1
           AND t1.IsPassenger = 1
           AND t1.IsCancelled = 0
           AND t1.IsCrew = 0
           AND t1.RBD NOT IN ('I',
                              'Z',
                              'R')
           AND t1.PassengerType NOT IN ('CHD')
           AND t2.SalesChannel <> 'Travel Agency'
           AND t3.IsBRAMember = 0
           AND t3.Email IS NOT NULL
           AND t3.Email = t2.email_booker
           AND DATEDIFF(DAY, t2.BookingDateTime, t2.FirstDepartureDateTime) >= 3
           AND t3.FirstBookingEverKey = t1.BookingKey) AS t1) AS t8
   INNER JOIN
     (SELECT DISTINCT BookingKey,
                      PassengerID ,
                      FIRST_VALUE(FlownTransactionID) OVER (PARTITION BY BookingKey,
                                                                         PassengerID
                                                            ORDER BY BookingKey,
                                                                     PassengerID,
                                                                     ScheduledDepartureDateTime) AS refFirst_FlownTransactionID
      FROM [Flights]) AS t9 ON (t8.BookingKey = t9.BookingKey
                                       AND t8.PassengerID = t9.PassengerID)) a
INNER JOIN [Flights] b ON (a.FlightId=b.FlownTransactionID and a.PassengerId = b.PassengerID)
  • 1
    Can you put the query here? It's hard to tell without looking at it.. – Rachid Mamai Oct 4 '19 at 13:11
  • @RachidMamai Sure, sorry! I added the query text. – Disasterkid Oct 4 '19 at 13:16
  • Hey DisasterKid, incredible query here. Is this designed to run daily and target recently booked travelers (*with criteria) for an email? If so - I think this query could be simplified. – Cameron Robert Oct 4 '19 at 13:44
  • @CameronRobert lol. you're damn right. this is someone else's query to be honest. – Disasterkid Oct 4 '19 at 13:55
  • 1
    @CameronRobert thanks but i doubt if the CONVERT is causing the issue because they've been there when things went well. I just have no clue why this query which is working fine in the local database is not working here. – Disasterkid Oct 4 '19 at 14:41

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.