Production Issue! - Table Name Mismatch in SQL 1

[MSIsam]

We are having a production issue where a SQL error is encountered. We are currently using MSI 7.2.2 with Teradata V2R4 backend. The SQL consists of volatile tables created (ex. ZZT6T0502CXSP000), then referenced later on in ZZMD02. The problem is that the subsequent from clause of later passes references a table that does not exist. The FROM clause of ZZMD02 references ZZSP00, but the table name is actually ZZT6T0502CXSP000. It is reproducable and I can send the exact SQL if needed. Has anyone ever seen this? Any ideas on how to resolve would be greatly appreciated!

Thanks,
Sam

 

[MSIsam]

Please ignore this thread.
The issue is resolved as a Teradata issue. One thing to note is that if a query returns a SQL error during report execution, the table names of the volatile tables might not match with the name given in later passesw of SQL. This inadvertantly directed us to MSI as the issue instead of Teradata.

 

[datamart]

Try to find all the tables ZZ* in the schema. Drop all of them and start afresh.
Then try running the report.

 

[spod]

There are differences between the view mode of the report you run as to what the temporary tables are called:

ZZMD02 - table name when you just view SQL
ZZT6T0502CXSP000 - Table name when you run the report

The latter is what you'll see Database side, as the former never actually gets run against the db.

hope this helps a little?

 

[MSIsam]

Thanks spod,
You are correct. The thing that initially threw us off is that the SQL failed halfway through so when we looked at the entire pass, we saw both naming conventions in a single report. We have since resolved the SQL error as it was entirely related to our backend.

 

[Z3]

Besides, when you do "view detail" in Job view on server administrator, the passes that are not yet executed are shown in table notation like "ZZMD02", while the ones that are finished or executing would have a notation like "ZZT6T0502CXSP000". This is by design, so administrator can tell where exactly the report is at.