Skip to Content
author's profile photo Henk Binnendijk
0
Henk Binnendijk

is_valid_date and raise an exception

Posted on Aug 16, 2016 at 06:07 AM 179 Views

SAP DS version 4.2 SP 6

I have a datetime field with the following code

ifthenelse(

is_valid_datetime(Q_SELECT.TIJDSTIP, 'YYYY-MM-DD HH24:MI:SS')=1,

to_date(Q_SELECT.TIJDSTIP,'YYYY-MM-DD HH24:MI:SS'),

raise_exception ('for the given inputfile an value not equel to timestamp is found on field TIJDSTIP, hence aborted')

)

with validation this code is invalid:

[Query:Q_check_datatypes]

Invalid mapping expression for column <Q_check_datatypes.TIJDSTIP>. Additional information: <Cannot parse expression.

For the call to function <ifthenelse>, cannot convert parameter number <3> to type <INT>. Provide a parameter of the correct type.. (BODI-1112351)>. (BODI-1111081)

the only way around i can image is to split the functionality into 2 field, one for test and raise on invalidation of the timestamp and 1 for the actual conversion to datetime when the inputfield is valid....

Is my code indeed not correct or is this a bug in DS?

Add a comment
10|10000 characters needed characters exceeded

Assigned Tags

Related questions

2 Answers

  • author's profile photo Viacheslav Novozhilov
    Viacheslav Novozhilov
    Posted on Aug 16, 2016 at 10:16 AM
    0

    The logic behind the mapping is to check if the input value is recognized as a valid datetime, otherwise flag it with an exception. The raise_exception() may or may not be handled later. The function always return INT 1.

    According to SAP Data Services Reference Guide

    http://help.sap.com/businessobject/product_guides/sbods42/en/ds_42_reference_en.pdf

    6.3.61 ifthenelse

    Syntax: ifthenelse(<condition>, <true_branch>, <false_branch>)

    If the data type of <false_branch> is not convertible to the data type of <true_branch>, Data Services produces an error at validation. If the data types are convertible but don't match, a warning appears at validation.


    Therefore, you would need to find an alternative way.


    Thank you,

    Viacheslav.

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Arun Sasi
    Arun Sasi
    Posted on Aug 16, 2016 at 10:49 AM
    0

    Hi,

    Best practice for handling dates would be to mark the invalid date as NULL or a future date rather than raising an exception. You cannot use raise_excption() in the mappings as there are so many dates which might contain invalid values. You can set it in a script for e.g. if the Date contains values like 00000000 then raise an exception.

    Can you try the below

    ifthenelse(

    is_valid_datetime(Q_SELECT.TIJDSTIP, 'YYYY-MM-DD HH24:MI:SS')=1,

    to_date(Q_SELECT.TIJDSTIP,'YYYY-MM-DD HH24:MI:SS'),null)

    )

    Regards

    Arun Sasi

    Add a comment
    10|10000 characters needed characters exceeded

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.