HI can you please give the code generated in thar error step.you just execute that sql code generated in toad or sqldeveloper see what error you are getting.
I will consider your logical schema for source and target datstore working fine.
There is a possibility of mismatch of datatype, that can cause the problem.
Say in ODI model, you have defined a 'Date type' field to be stored as 'String' however at the time of mapping in 'Interface', no conversion happens (from date to string) for this particular object. This causes the problem.
The original query remains valid at DB side (fires for datetype) however fails while integrating. Therefore the best way would be to apply conversion for that particular field (in this case, use TO_CHAR(Date type,'Desired Date Format').