I have two query subjects I'm trying to join to emulate a subquery. I have one query that narrows down a list of vendors. Then another query that lists amounts associated with that vendor.
I'm using native SQL to create the list of vendors and I'm using insertable objects to create the other. I've joined the two with a 1:1 cardinality...should it be 0:1 because not all the amounts associated with the vendors will have a matching entry in the results of the SQL query?
Also, when I do this I get a 'Logon Failed' message when trying to run the report. ??? I tested each individual query and they bring back the proper results but when I try to join them together I get the Logon Failed message. Any suggestions?
If you join 2 seperate queries then you are NOT emulating a subquery. With 1:1 <--> 1:1 cardinality you are performing the intersection of the two sets.
In most cases it should be possible to avoid joining 2 queries. You simply make sure that the correct filter is set for the single query.
An alternative can be found by combining a prompt query with a data query. Essentially this is a real subquery as you pass the parameter of the prompt to the main data query. Making the prompt optional may then be all you need.
Mixing native with Cognos SQL from a problem as well. Anyway, did you populate the 3rd query with dataitems from 1st and 2nd?
Yes, the third query was populated with data itesm from the 1st and 2nd.
The first one is a total list of vendors and amounts owed.
The second one is a list of vendors classified as consultants.
I'm trying to create a list of all the consultants and the amount owed. This doesn't sound like it would be too hard but the only way I can find out if a vendor is considered a consultant is to check their account number (which requries joining 3 tables).