BODI-1170037 The optimized sql cannot be generated for data flow ...

[Former Member]

Hi,

Today something strange happened to one of my dataflow.

I introduced some change, basically there was 2 table in outer join with another one. I changed the 2 tables left joined and I remapped the fields.

Validation gives some warning about data type conversion, as it used to be before my changes, but anything else seems ok.

I tried to run it and I saw a unique constraing error.

I opened again my DF and I try to have a look the the SQL generated and here it is the message

BODI-1170037; The optimized SQL cannot be generated for data flow <my dataflow>. There are no SQL sources in this data flow.

The df has been saved before the run. I tried also to restore a backup of the dataflow done before the changes... same error.

Done a new dataflow, cut & paste from the old one of the whole content... same error.

Any suggestions ?

DI version is 11.7.3.5, repository is on Oracle 10

Thanks in advance

[Former Member]

have you tried export the ATL to file and re-import the ATL ?

check the language of the DF do you see and tags like &ERROR ..

send me the ATL, I can take a look

[Former Member]

Hi,

I tried the "export to ATL and import" way and it seems to succeed, I just needed to close the designer before the import...

Thanks

Achille

Edited by: Achille Masserano on May 11, 2010 3:23 PM

Edited by: Achille Masserano on May 11, 2010 3:24 PM

[former_member467822]

You aren't going to like either of my suggestions but these are probably your best solutions:

1. Upgrade to the latest version of Data Services. I don't know for sure if your problem was caused by a bug within the software, but why take a chance? Upgrade.

However, if you can't upgrade and need to use the current version then...

2. Recreate your dataflow from scratch. Don't replicate it or try to change the current version. Just start a new dataflow from scratch. Something obviously went wrong with the first data flow behind the scenes. You may eventually figure it out in designer but your easiest and fastest solution is probably just to start over.