Skip to main content
Solved

Facing Issue while transforming Events.


Forum|alt.badge.img+1

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`);

 

 

Best answer by Dilini Fernando

Hi @Viswanadha Sai Akhil Pujyam,

I have create a Support ticket, so our team can follow up and help you.

Best regards,
Dilini 

View original
Did this topic help you find an answer to your question?

2 replies

Dilini Fernando
Seasoned Practitioner
Forum|alt.badge.img+2
  • Seasoned Practitioner
  • 671 replies
  • Answer
  • August 24, 2023

Hi @Viswanadha Sai Akhil Pujyam,

I have create a Support ticket, so our team can follow up and help you.

Best regards,
Dilini 


Forum|alt.badge.img
  • Seasoned Practitioner
  • 282 replies
  • August 28, 2023

@Viswanadha Sai Akhil Pujyam , What’s helpful in these instances it to output the result of your transformation to a CDF Raw table.  This way, you can query instances where startTime is greater than endTime.  It’ll help debug your transformation.  Looking at your transformation, it does not explicitly filter out instances where start is greater than end.  Dealing with this can be tricky.  

Jason


Reply


Cookie Policy

We use cookies to enhance and personalize your experience. If you accept you agree to our full cookie policy. Learn more about our cookies.

 
Cookie Settings