Join with a Union

[Ayush12]

Hello Experts,

This is my first question here.
I am trying to create a report where my source is Teradata(through framework model).
In this report, I have done a Union of two queries. That works fine.
Then I wanted to add a third table and did a join between the existing Union and the third table, which for some reasons doesn't show any data in the output.
When I capture the queries from Terdata query logs, I get two queries, one for union and another which looks like the "Join" query, but only queries the third table.
Both these queries result in data. But I don't see any query where actual Join is happening in these logs, neither do I get any data in my report. Any suggestions what I may be doing wrong?

Thanks
Ayush

[SpareTire]

Have you considered what you are joining on? Is its the same field and format in the unioned query and the 3rd table?

If your running tabular data on both Unioned Query and third table and seeing data there--then I would question how the two are being joined.

[Ayush12]

I just added a cast expression on both the queries (union) and the single, on the column based on which I am joining.
Still no results.
My question is, does teh join happen inside the cognos engine? Because even though there won't be any data, I would expect to see a join query running in Teradata?
Based on what I am seeing, there are two queries submitted to Teradata..and I was expecting 3.

[tjohnson3050]

For any query, or the entire report you can view the generated SQL inside report studio.  When you view the generated SQL, there is a choice of Cognos SQL, or Native SQL.  The Native SQL is what is sent to the data source (Teradata in your case).  What it sounds like in your case is two queries are going to Teradata, and the results are being joined by Cognos.

This gets into performance tuning, but if you want to try and push more logic down to the datasource, you can set the processing property on the query to database only.  When this happens, you may get an error that points to some logic in the report that Cognos doesn't know how to push down to the datasource.

[Ayush12]

Thankyou very much Tjohnson.
I will try these things.

Ayush