Alias Table or Hotfile - cross product problem

[BeckyBoop]

I have a table, not joined, bringing in 'RV Value'. (1,2, ASV, 4, 5, QID are some of the fields.)

In another table, which has multiple joins with other tables, I am bringing in 'Management Type'. (1, Management Type, 2, ASV and blank fields - for now, but other information will be brought in later).

I need a 3rd column in the report that will show 'Management Type' + 'RV Value' = 'New' . (if Management Type = 'Management Type' and 'RV Value' = 2, then 'New') OR (if 'Management Type' = 2 and RV Value = ASV then 'Used')

If I try to pull in 'Management Type' and 'RV Domain' into the report the result is a cross product. Does anyone know how I can eliminate this? Would a hotfile be a solution?

 

[griffindm]

Becky,

You are getting the cross-product message because Impromptu knows there is no logical join to table RV. The only way this would make sense (i.e. no deliberate desire to combine every row in RV with every other row in the report) would be if RV contained only one row. So...

Either you need to determine a good join to RV from one of the other report tables,OR

If there is only one row in RV, you could either turn off the governor preventing or warnings for cross-products for this report and user class (not my first choice), OR

Create a join between RV and another report table using a non-equi-join operator (something other than "=&quot, such as
&quot;RV.ASV <> table2.somefield&quot;

Be sure to pick comparison columns where there will never be a match, but the columns are of the same data type. This will give you the ONE row in RV joined to everyone row in the second report table and will avoid the cross-product warning or error. This is generally useful when the first table (RV in this case) contains a single row value that is applicable to every other row in the report.

Hope this helps.

Dave Griffin ;-)

(check out the new FAQ's I've put out and give me some feedback if you have the time)