to_date error

[Former Member]

I've created a view and filtered it to produce a single row result.

LIFNR_Account_Number_of_Vendor	AEDAT_Shipment_Last_Changed
10033970	20091215

The AEDAT field is a nvarchar(8) at database source (HANA), and a varchar(8) in the Info Steward View Output Schema.

Now, I need to change this value to a date type, so I did this in the field mapping:

          to_date(Views.Shipment_Header_TOTAL.AEDAT_Last_Changed_on, 'yyyymmdd')

That validates perfectly, but when I preview data, everything falls apart.

The error message is jibberish to me, and I cannot see any meaningful clues.

Can anyone shed some light on what's occurring ?

Many Thanks

Simon

Data Services execution failed for EX1_FAIL_AS_DATE. Error :
(14.2) 01-26-15 12:10:23 (R) (8432:2660)
DBS-054003: |Data flow vviewdf_9763_45370f99_2057_4b7d_b26b_8f1cf3dda52b|Reader vviewQuery9763_1
SELECT query <SELECT * FROM "_SYS_BIC"."DS_VIEW_4A1B1"> failed.
The database error message is <SQL submitted to database <> resulted in error
<
[SAP AG][LIBODBCHDB DLL][HDBODBC] General error;
129 transaction rolled back by an internal error:
[129] transaction rolled back by an internal error:
column store error:
search table error: [2981] function is not implemented (, yet);calcToQo: expression
>.
The SQL submitted is <SELECT * FROM "_SYS_BIC"."DS_VIEW_4A1B1">.>.(14.2) 01-26-15 12:10:23 (E) (8432:2660) DBS-054003: |Data flow vviewdf_9763_45370f99_2057_4b7d_b26b_8f1cf3dda52b|Reader vviewQuery9763_1 SELECT query <*****> failed. The database error message is <*****>. This message contains some internal system details which have been hidden for security.
If you need to see the full contents of the original message, ask your administrator to assign additional privileges to your account. (COR-10690)

[Former Member]

The original error relates to a specific date format : YYYYMMDD.

We are now working around this by simply using an alternate format, eg. YYYY-MM-DD

One could say to now ignore it, as a workaround is available.
However, this is unecessary coding for handling what is after all, the default date string format used in ECC.
Also confuses users/wastes time  if you are not aware of the issue.

Plus ... it's not guranteed to work for all future requirements.

I'm asking our admin to submit as a bug to SAP.

Again ... I can't imagine we're the only ones affected.... be interesting to hear other experiences.

For the record:

     technical description for the function as linked below.

      http://help.sap.com/businessobject/product_guides/sboIS42/en/is_421_user_en.pdf
     Page 213-215 : “2.13.3.4.6 Convert to date”

[former_member187605]

That looks very strange to me. If the datatype in HANA of AEDAT_Last_Changed_on is really nvarchar(8) and its values are stored as YYYYMMDD, a to_date(<input string>,'YYYY-MM-DD') will never work.

I suspect it has datatype date. The to_date function expects a first parameter of type char. Because the type is not correct, an implicit to_char function is applied first with default format YYYY-MM-DD.

Can you doublecheck?

[Former Member]

Sorry Dirk, it's just my poor explaination.

I meant, to take the 8char yyyymmdd, string,  and manupilate it into 'yyyy-mm-dd' with substring and concatenation.

Then, I can convert to date using the new format.

Apologies.

[former_member187605]

So, it's to_date(AEDAT_Last_Changed_on,'YYYYMMDD') vs. to_date(substr(AEDAT_Last_Changed_on,1,4) || '-' || substr(AEDAT_Last_Changed_on,5,2) || '-' || substr(AEDAT_Last_Changed_on,7,2),'YYYY-MM-DD')? Fair enough, both wil work.

[former_member187605]

Most probably somle inconsistency with the definition of a Calculation view in HANA. Unfortunately, you don't get the real underlying error message.

Can you have the instructions in applied and post the error message again?

[Former Member]

My admin is trying to sort that out Dirk.
We've granted permission as per article, but alas no joy.

Suspect it might be something to do with the 'Granular' as opposed to 'Full Control' .... having a hard time finding the right documentation.

[former_member187605]

Or with the inheritance of the rights...

Have your admin check the rights of your user (2nd screenshot - select your user then: View Security).

Security setup is actually part of the BI platform. That's where it's fully documented.