Knowledge Base : ORA-00928 in ODI mappings

I have seen this once before and immediately forgot about it since it was so ridiculous, so I write it down to remember:


My Oracle Data Integrator mappings / scenarios do not run.

ODI creates inexecutable SQL code which generates an ORA-00928 error.

The generated code looks like:

AND ACCOUNT_REPORT.DWH_GUELTIG_VON   <= to_date('#BI.V_NAECHSTES_DELTA', 'DD.MM.YYYY HH24:MI:SS')
AND ACCOUNT_REPORT.DWH_GUELTIG_BIS   > to_date('#BI.V_NAECHSTES_DELTA', 'DD.MM.YYYY HH24:MI:SS')
AND ADRESSE.DWH_GUELTIG_VON <= to_date('#BI.V_NAECHSTES_DELTA', 'DD.MM.YYYY HH24:MI:SS')
AND ADRESSE.DWH_GUELTIG_BIS > to_date('#BI.V_NAECHSTES_DELTA', 'DD.MM.YYYY HH24:MI:SS')
) 
  ) UNION   
  ) DSTNCT_ACCREPRT_LAND
where	(1=1)
) S
where NOT EXISTS

Why’s that?

Beware of operator names in ODI that consist of SQL reserved words, like:

Do not name a set operator “Union”, for example. ODI uses the operator name as an alias in SQL without checking for reserved words or any form of escaping.

And once you rename an operator in ODI’s logical view, also rename it manually (Warnung) in the physical view, too, since this is not done automatically.