Below is the screenshot of error , during running transformation.
Below is the Query used to transform, Preview is working fine. But During the Transformation Run, it is giving above error, saying start time values cannot be greater than endTime values. But According to Query it shouldn’t be the case. plz suggest solution.
Attached Sample Data(data sheet) For your reference.
select
`Notification_no` as externalId,
`startTime` as startTime,
`endTime` as endTime,
`description` as description,
`type` as type,
1118188859138991 AS dataSetId
from
(select
concat('NO_',QMNUM) as `Notification_no`,
--to_timestamp(QMDAT,'M/d/yyyy') as `startTime`,
CASE
WHEN RIGHT(MZEIT, 2)='AM' THEN
TO_TIMESTAMP(CONCAT(QMDAT,' ', SUBSTRING(MZEIT, 1, LENGTH(MZEIT) - 3)), 'M/d/yyyy h:mm:ss')
ELSE
TO_TIMESTAMP(CONCAT(QMDAT,' ', SUBSTRING(MZEIT, 1, LENGTH(MZEIT) - 3)), 'M/d/yyyy h:mm:ss') + INTERVAL 12 HOURS
END AS `startTime`,
CASE
WHEN isnull(QMDAB) THEN
TO_TIMESTAMP(CONCAT(QMDAT,' ', SUBSTRING(MZEIT, 1, LENGTH(MZEIT) - 3)), 'M/d/yyyy h:mm:ss') + INTERVAL 24 HOURS
ELSE
CASE
WHEN RIGHT(QMZAB, 2) = 'AM' THEN
TO_TIMESTAMP(CONCAT(QMDAB,' ', SUBSTRING(QMZAB, 1, LENGTH(QMZAB) - 3)), 'M/d/yyyy h:mm:ss')
ELSE
TO_TIMESTAMP(CONCAT(QMDAB,' ', SUBSTRING(QMZAB, 1, LENGTH(QMZAB) - 3)), 'M/d/yyyy h:mm:ss') + INTERVAL 12 HOURS
END
END AS `endTime`,
--to_timestamp(QMDAT, 'M/d/yyyy') + interval 24 hours as `endTime`,
QMTXT as `description`,
QMART as `type`
from `E2_SAP`.`MN_sheet`);