Getting mixed results using Lotus Notes as data source

[khartman]

Hello all, I am using CR10 against a Lotus Notes database. I am linking two tables (aka Forms in Notes) together and using an outer join. There are two parameters being used, fiscal month and fiscal year. In all it is a pretty simple report.

I think the issue is in the way Crystal is handling the join but I can't be certain. TableA is joined to TableB, TableA is populated with data up to March. TableB is currently empty but will have data soon. The tables are joined by a common field (Fiscal Year) from TableA to TableB.

If my thinking is correct by using the outer join it should alway return data because TableA is populated.

The issue I am having is when I run the report in the designer for say fiscal month = January and fiscal year = 2005 it waffles the results. By that I mean I run it the first time and get data, I run it the second time (same parameters) get a blank report, the third time get data, the fourth time no data....etc.

This is driving me nuts because it seems that the join is switching from left outer to right outer each time I run the report.

Has anyone else seen this behavior? Is it the assumption I am making about the join? BTW, the SELECT statement does select data from both tables.

Kenneth Hartman
Manager, EIS
Hughes Network Systems

http://www.hns.com

 

[kutoose]

Hi Kenneth,

Did you check the SQL generated in Crystal and see how the join is implemented ?

Run the generated SQL separately in the database and see the results. If you think the join condition in the generated SQL is not correct, can you modify it and run it again in database ?

You can see the SQL Query generated by looking at Database -> Show SQL Query

 

[khartman]

Kutoose,

The SQL generated is correct and the join looks like it is constructed correctly.

I talked with BO Tech support this morning and we ran the query through the SQL32Connect utility they have. I plugged in the query and ran it and received the same results as I did when running it through Crystal. The first time run the data is there, the second time it isn't, back and forth like clockwork. The SQL statement never changes just the apparent join.

This led the tech to two conclusions: 1 - the driver, which is supplied by IBM has a bug in it and is not interpreting the SQL correctly, or 2 - Crystal is building the join incorrectly; however, as I indicated the join looks like it is built correctly, so I am leaning towards 1 right now.

In the interim I believe I came up with a workaround. Remember I said the second table was empty - no records in it. I had the Notes developers insert a single record into the second table. Once they inserted the dummy record in the table I reran it. The report now works correctly and the "data's there, data is not there" issue went away.

When BO gets back to me regarding their investigation into this error I will post what they tell me.

Thanks,
--Ken

Kenneth Hartman
Manager, EIS
Hughes Network Systems

http://www.hns.com