Unable to convert a varchar field in database to date data type in OAC
Summary
I am creating a dataset from a table in database that has a date field in varchar2 datatype. I am unable to convert it to date datatype in OAC
Content
I am creating a dataset in OAC connecting to a table in database that has a date field in varchar2 datatype. I am unable to convert it to "date" datatype in OAC. I get a query error.
I am trying to use the "Cast(expression as type)" function in data flow to do this conversion. My expression looks like 'Cast(RECORD_DATE AS DATE)'.
When i Validate this expression and Apply, it succeeds. However there is a query error that shows up in the "preview data" section (attached image).
Any help is highly appreciated. Also, please let me know if we could write any custom function or logic in OAC to convert from varchar2 to date datatype.
Note - when I try the same using CSV as my dataset I am able to convert from "varchar2" datatype to "date" datatype. It is giving me this problem when I use database table as my dataset.
Version
OAC version 105.3.0-117
Answers
-
Use ---to_date---
0 -
Hello Chethana,
Can you please try the below formula:
EVALUATE('TO_DATE(%1,%2)' AS DATE,"Record_Date",'MM-DD-YYYY')
this should give you the expected output.
Regards,
Aman Jain
0 -
Thanks a tonne for the response Aman.
I tried this in my data flow and it works fine.
0 -
My requirement is to process billions of records whose month and year values are to be extracted to show different visualisations in OAC.
Thus my concern here is, would there be a hit on performance when I use Evaluate function, to create these charts from this huge no. of records?
0 -
Hello Chethana,
If you are processing these many records then you must be having an ETL tool, if I am not wrong.
Rather than using this calculation at the reporting layer, you can push this calculation at the ETL layer and do the reporting on top of your warehouse.
Please let me know your thoughts on the same.
Regards,
Aman Jain
0 -
Yes Aman, agreed with you. Ideally this needs to be done at the data-pipeline and due to some limitations this was asked to be done in the OAC layer. We are simultaneously looking at doing this at the data-pipeline and that should be the right solution for this kind of dataset.
Regards,
Chethana
0 -
Hello @Aman Jain-Oracle
I am hitting below issue.
Executing dataflow in OAC results in below error (in italics). This dataflow does a single transformation, that converts the field record_date in the dataset, from varchar to DATE. Transformation used is, “CAST(EVALUATE(‘TO_DATE(%1,%2)’ AS DATE, RECORD_DATE”, ‘YYYY-MM-DD’) AS DATE)”
In the dataflow, output of every step is successful and the results are shown, including for the step ‘Save Data’ as in the screenshot.
However, the result of dataflow execution throws this error. As a result, data for the corresponding date (03/21/2022) does not get added to the final table. We have been using this transformation from quite a long time and never came across this issue before. It is happening particularly for the date 03/21/2022.
Tried removing this record from input dataset and re-ran the dataflow and it succeeded.
Any pointers on how to get rid of this please?
Step |j| Execution failed. Status: FAILED. Message: [nQSError: 17011] SQL statement execution failed.
[nQSError: 17001] Oracle Error code: 12801, message: ORA-12801: error signaled in parallel query server P000, instance 1
ORA-29913: error in executing ODCIEXTTABLEFETCH callout
ORA-01843: not a valid month
at OCI call OCIStmtExecute.
[nQSError: 43224] The Dataflow “vb_daily_access_dataflow_test” failed during the execution.
[nQSError: 43204] Asynchronous Job Manager failed to execute the asynchronous job.
Thanks,
Chethana
0 -
Hello @Chethana Kumari-Oracle
one solution would be separate the date using a substring and concatenate together as date and cast it would be work.
Just an Eg: CAST(SUBSTRING( <YOUR_COLUMN> ,1,2)||'-'|| CAST(----) .....
0
