Querying PeopleSoft tables

[former_member672670]

Hie Guys,

I have successfully created a combined report from 2 different universes.

"Universe 1" was created based on a SQL query from a MySQL connection/database. I created a derived table using the SQL query. Now, one of the objects in the derived table named "Sponsor Name" had "Sponsor ID' values from "Universe 2". Thus, I had to create a detail variable to get the Sponsor Names. The problem I am facing is - some of the ID fields from "Universe 1" are not available in "Universe 2". Thus, I have some blank Sponsor Names in the combined report.

Details about the MySQL Connection:

The "MySQL" database queries a "PeopleSoft - Oracle" table to get the Sponsor Names. I've attached snapshot of the query below.

Can anyone help me on getting this issue resolved? Thanks.

[Former Member]

So what exactly is the issue. If you do not have the matching ids in both the universe and you are using a left outer join you will get blank data for sponsor name. This is how it works.

If you do not want to show the records where the sponsor name is not blank then you will have to use inner join (caution: this will remove the entire rows with non matching id) or you need to have the matching id in both your left and right tables

[former_member672670]

The Ids are simply missing in both universe. I need to use the attached query in another database (PeopleSoft) to retrieve the names for all the ids.

[Former Member]

If that is the case then you should not merge the objects rather add a additional query in the query panel to be used as a sub query and get the IDs which will be fed to the main query in a condition...

[former_member672670]

But I do not have a universe built on that connection to get the IDs. Is it ideal to build a universe just to get the names from the existing Ids? The connection is to a SQL Server Database. Is there any other way besides building a universe out of it? I'm using UDT 4.0.

[Former Member]

You do not have to build a new universe .. you can just utilize the same universe to achieve this in the report level using a sub query and passing the values to the main query..