Oracle Transactional Business Intelligence

Home› Oracle Analytics› Forums› More Solutions› Oracle Transactional Business Intelligence

Categories

How to verify and validate correct date formats in OTBI where the column is supporting references.

Received Response
24
Views
1
Comments
User_M14OP
User_M14OP ✭
in Oracle Transactional Business Intelligence

Summary:

We have a scenario where we are using a supporting reference column and casting it as date.

But due to multiple formats of date being entered in the column with different date formats,the report errors out.


Code Snippet (add any code snippets that support your topic, if applicable):

CAST(CASE WHEN "Supporting References Without Balances"."Supporting Reference Name 31" = 'Payment Due Date' THEN "Supporting References Without Balances"."Supporting Reference Value 31" WHEN "Supporting References Without Balances"."Supporting Reference Name 32" = 'Payment Due Date' THEN "Supporting References Without Balances"."Supporting Reference Value 32" WHEN "Supporting References Without Balances"."Supporting Reference Name 33" = 'Payment Due Date' THEN "Supporting References Without Balances"."Supporting Reference Value 33" WHEN "Supporting References Without Balances"."Supporting Reference Name 34" = 'Payment Due Date' THEN "Supporting References Without Balances"."Supporting Reference Value 34" WHEN "Supporting References Without Balances"."Supporting Reference Name 35" = 'Payment Due Date' THEN "Supporting References Without Balances"."Supporting Reference Value 35" WHEN "Supporting References Without Balances"."Supporting Reference Name 36" = 'Payment Due Date' THEN "Supporting References Without Balances"."Supporting Reference Value 36" WHEN "Supporting References Without Balances"."Supporting Reference Name 37" = 'Payment Due Date' THEN "Supporting References Without Balances"."Supporting Reference Value 37" WHEN "Supporting References Without Balances"."Supporting Reference Name 38" = 'Payment Due Date' THEN "Supporting References Without Balances"."Supporting Reference Value 38" WHEN "Supporting References Without Balances"."Supporting Reference Name 39" = 'Payment Due Date' THEN "Supporting References Without Balances"."Supporting Reference Value 39" WHEN "Supporting References Without Balances"."Supporting Reference Name 40" = 'Payment Due Date' THEN "Supporting References Without Balances"."Supporting Reference Value 40" WHEN "Supporting References Without Balances"."Supporting Reference Name 41" = 'Payment Due Date' THEN "Supporting References Without Balances"."Supporting Reference Value 41" WHEN "Supporting References Without Balances"."Supporting Reference Name 42" = 'Payment Due Date' THEN "Supporting References Without Balances"."Supporting Reference Value 42" WHEN "Supporting References Without Balances"."Supporting Reference Name 43" = 'Payment Due Date' THEN "Supporting References Without Balances"."Supporting Reference Value 43" WHEN "Supporting References Without Balances"."Supporting Reference Name 44" = 'Payment Due Date' THEN "Supporting References Without Balances"."Supporting Reference Value 44" WHEN "Supporting References Without Balances"."Supporting Reference Name 45" = 'Payment Due Date' THEN "Supporting References Without Balances"."Supporting Reference Value 45" WHEN "Supporting References Without Balances"."Supporting Reference Name 46" = 'Payment Due Date' THEN "Supporting References Without Balances"."Supporting Reference Value 46" WHEN "Supporting References Without Balances"."Supporting Reference Name 47" = 'Payment Due Date' THEN "Supporting References Without Balances"."Supporting Reference Value 47" WHEN "Supporting References Without Balances"."Supporting Reference Name 48" = 'Payment Due Date' THEN "Supporting References Without Balances"."Supporting Reference Value 48" WHEN "Supporting References Without Balances"."Supporting Reference Name 49" = 'Payment Due Date' THEN "Supporting References Without Balances"."Supporting Reference Value 49" WHEN "Supporting References Without Balances"."Supporting Reference Name 50" = 'Payment Due Date' THEN "Supporting References Without Balances"."Supporting Reference Value 50" WHEN "Supporting References Without Balances"."Supporting Reference Name 51" = 'Payment Due Date' THEN "Supporting References Without Balances"."Supporting Reference Value 51" WHEN "Supporting References Without Balances"."Supporting Reference Name 52" = 'Payment Due Date' THEN "Supporting References Without Balances"."Supporting Reference Value 52" WHEN "Supporting References Without Balances"."Supporting Reference Name 53" = 'Payment Due Date' THEN "Supporting References Without Balances"."Supporting Reference Value 53" WHEN "Supporting References Without Balances"."Supporting Reference Name 54" = 'Payment Due Date' THEN "Supporting References Without Balances"."Supporting Reference Value 54" WHEN "Supporting References Without Balances"."Supporting Reference Name 55" = 'Payment Due Date' THEN "Supporting References Without Balances"."Supporting Reference Value 55" WHEN "Supporting References Without Balances"."Supporting Reference Name 56" = 'Payment Due Date' THEN "Supporting References Without Balances"."Supporting Reference Value 56" WHEN "Supporting References Without Balances"."Supporting Reference Name 57" = 'Payment Due Date' THEN "Supporting References Without Balances"."Supporting Reference Value 57" WHEN "Supporting References Without Balances"."Supporting Reference Name 58" = 'Payment Due Date' THEN "Supporting References Without Balances"."Supporting Reference Value 58" WHEN "Supporting References Without Balances"."Supporting Reference Name 59" = 'Payment Due Date' THEN "Supporting References Without Balances"."Supporting Reference Value 59" ELSE "Supporting References Without Balances"."Supporting Reference Value 60" END as date)

Tagged:

Answers

  • Sahithi Kolasani-Oracle
    Sahithi Kolasani-Oracle ✭✭✭✭

    I think supporting references data type is VARCHAR, so it's a kind of free text. So unless until it is restricted at the data entry level, we won't be able to cast that as date in the report.

    One option is just show that as a free text in the report as well. But there may be inconsistency in the date format. We need to inform user about this that they are going to see whatever was entered in those support reference fields.

    I created a report in BIP to see the data types of the support reference columns of XLA_AE_LINES. All are showing as VARCHAR. (A here refers to a text field)


    Thank you

    Sahithi