Troubleshooting Outer Join

[Former Member]

I tried to set up a left outer join between two of my 5 tables. I received an error: "If tables are already linked then join type cannot change" Is there a way around this?

[Former Member]

I added the price file a second time and linked the price key from the transaction file to the price key in the price file. Then I changed the link to a left outer join- enforced from

[former_member11696]

You may want to refer to your database Help file on how to link your tables also and what happens if you link them in Circles...

Thank you

Don

[Former Member]

Hi

I think you can't make to different joins from one table to other. The best way is to write SQL and execute through Add Command.

Thanks,

Sastry

[Former Member]

Being relatively new at CR, I'm not sure how to write a SQL stmt. Can I use an IfNull stmt? So if the record isn't in the TO file, I still want to retrieve the record from the FROM file. Example: The transaction file includes a price key; The Price detail doesn't have a price key to match the record in the transaction file. How do I write a statement to include the record from the transaction file and put zero in the place where the price is Null from the price detail file? Note: the price detail file has various effective dates and I only want to include the prices from a certain date, so I have a parameter and filter to retrieve the prices for that date.

[Former Member]

Not sure what you are trying to do.

A solution might be to add the table in twice, joined once to your TO record with a left outer and then again to your FROM record.

Crystal will warn that table already exists, just click OK and table will be added againwith alias TableName_1

If this is not what you want then please describe your joins tablename.field to tablename2.field2 etc.

Ian

[Former Member]

I added the TO table a second time. I joined the Transction File (from or primary table) in a left outer join (left enforced). That didn't yield the results I was looking for. I'm not sure what you meant about the second join to the FROM table. The files I'm joining are:

Primary(from)

Transaction file - Price key field

To

Pricing Detail File - Price key field

(This is the table I added a second time and I didn't get a join error message, but I still didn't get the records I needed)

I want to retrieve all the records from the Primary file whether they are in the TO file or not.

[Former Member]

Sorry I thought you were trying to join from two different fields from Primary to same filed in secondary.

You should just be able to use a left outer join.

Try removing table and adding back in again and rejoining with a left outer.

Ian