Oracle Table Integration with Date Column as Text and Interpreting NullDate()
Application Type
Reactive

We are using an oracle external table in our application which has all the columns as text (Orders,dates, etc). It is sourcing its data from different procedures which updates the table periodically.

The external table is used in the screen to show in a table widget. There are filter options to query the table and see the results on screen. There is a filter condition for the end user to search a column with From and To Dates.

Since the From and To dates are input as Date, and the column to be searched is in Text (but the text content is in Date Time format). Is there a way in OutSystems to query for this data from Aggregate as one of the filter condition?

I have tried to change the DataType as Date during the Integration step and regenerated the extension. Now the column has null values. Now these are converted to 1900-01-01 00:00:00 in OutSystems. I have used a filter condition like Entity.DateColumn >= FromDate and Entity.DateColumn <= ToDate. When I query Entity.DateColumn=NullDate(), this is not working (as the source oracle table is with text data type where comparisons not happening?)

When FromDate and ToDate are "" (during initial page load), the table does not pull the Null Values. Those are not considered at all and fetching only those rows which has DateColumn with a valid date.

When I integrate the table in OutSystems with Text datatype itself, then the null values are empty and I'm unable to define filter condtion with From and To Dates and the filter condition itself is not working.

Which is the optimal solution in this case for filtering the column with date in OutSystems whereas the column is having text data type in Oracle end? Thanks in advance!

I just got the solution myself. I created a new calculated attribute in the aggregate and made the TextToDateTime apply on the column which had text date time. Ofcourse, I changed the text format in the source table to be same as that of OutSystems default date time (YYYY-MM-DD HH:MM:SS). Here, we need to process null column values to date time, which showed up an error. To handle this, I used the If condition to replace NullTextIdentifiers to NullDate() and valid text date to TextToDateTime(Entity.Attribute).

Thus I was able to use this in filter condition for date comparison. Hope the answer helps to some one facing similar issue.

Community GuidelinesBe kind and respectful, give credit to the original source of content, and search for duplicates before posting.