Datatype Requirements for Custom ADS Mappings

When a custom ADS SQL or mapping is created or extended in DFCS — for example, to include additional data elements for Borrowings or Liability instruments — ensure that every derived column has a determinable Oracle datatype. A CASE expression that can return only NULL, without an explicit datatype, may cause the ADS CREATE TABLE AS SELECT (CTAS) operation to fail with:

ORA-01723: zero-length columns are not allowed

In this case, do not use:

CASE
  WHEN dfstg.stg_borrowings.v_data_source_code NOT IN ('XYZ') THEN NULL
END AS liquidity_premium_rate

Use: (cast NULL to the target attribute's datatype):

CASE
  WHEN dfstg.stg_borrowings.v_data_source_code NOT IN ('XYZ')
    THEN CAST(NULL AS NUMBER)
END AS liquidity_premium_rate

Use the datatype appropriate for the target attribute, for example NUMBER, VARCHAR2(n), or DATE.

Note:

Custom ADS mappings are authored and maintained in DFCS. If this error occurs, share the affected mapping with your DFCS administrator for correction, then refresh the extension in PBSMCS as described in Refresh the Extension from DFCS.