AWS Athena (Presto) ' ' cannot be applied to varchar, varchar error-CodePudding

I'm having a bit of trouble with some Presto SQL that I have written in Athena. I get the following error which I'm a bit confused about:

SYNTAX_ERROR: line 46:39: ' ' cannot be applied to varchar, varchar

Here is my script:

SELECT Duplicate.AircraftTypeCode,
       Duplicate.LineNumber,
       Serial.SerialNumber
FROM  (SELECT *
       FROM  (SELECT DISTINCT TypeCode AS AircraftTypeCode,
                              LineNumber
              FROM  (SELECT acl.aircraft_type AS Type,
                            achl.aircraft_type_code_internal     AS TypeCode,
                            acl.aircraft_line_number             AS LineNumber,
                            row_number()
                              OVER (
                                partition BY aahl.aircraft_id
                                ORDER BY aahl.aircraft_id, aahl.start_event_date
                              DESC,
                              aahl.event_sequence_number DESC)   AS Rown
                     FROM   fleets.aircraft_all_history_latest aahl
                            LEFT OUTER JOIN fleets.aircraft_latest acl
                                         ON aahl.aircraft_id = acl.aircraft_id
                            LEFT OUTER JOIN
                            fleets.aircraft_configuration_history_latest achl
                                         ON acl.aircraft_id = achl.aircraft_id)
                    AH
              WHERE  linenumber IS NOT NULL
              GROUP  BY TypeCode,
                        LineNumber)LineNumber
       GROUP  BY AircraftTypeCode,
                 LineNumber
       HAVING Count(LineNumber) > 1)Duplicate
      LEFT OUTER JOIN (SELECT *
                       FROM  (SELECT achl.aircraft_type_code_internal AS
                                     TypeCode,
                                     acl.aircraft_serial_number       AS
                                     SerialNumber,
                                     acl.aircraft_line_number         AS
                                     LineNumber
                              FROM   fleets.aircraft_all_history_latest aahl
                                     LEFT OUTER JOIN fleets.aircraft_latest acl
                                                  ON aahl.aircraft_id =
                                                     acl.aircraft_id
            LEFT OUTER JOIN fleets.aircraft_configuration_history_latest achl
                         ON acl.aircraft_id = achl.aircraft_id) SerialNumber
                       WHERE  LineNumber IS NOT NULL
                       GROUP  BY TypeCode,
                                 SerialNumber,
                                 LineNumber) Serial
                   ON Serial.TypeCode   Serial.LineNumber =
                      Duplicate.AircraftTypeCode
                        Duplicate.LineNumber

Everything I am using is of type String. Is there is something in Presto that i should be doing differently as my thinking is more along the lines of MSSQL

CodePudding user response：

I assume you want to concatenate TypeCode and LineNumber in your JOIN condition. In Presto / Athena you need to use the CONCAT function or the || operator for that.