Multiple data source problem with getting all records from one table

[matpj]

I have a report that uses two data sources - one from Notes and one from Oracle.

I want to retrieve all records from the Notes database and only those records from the oracle database that match.
Currently, by just linking I get all records where they both match.

The problem is, the Oracle side of it comprises of 3 tables linked together - and no matter what I try I cannot get it to return all Notes records.

The SQL behind the Oracel side of it is as follows:

SELECT
PCMS_CHDOC_DATA_a.CH_DOC_TYPE "TYPE",
PCMS_CHDOC_DATA_b.PDS_REF "PDS Proj",
PCMS_CHDOC_DATA_b.STATUS "CA STATUS",
PCMS_CHDOC_DATA_b.ACTION_DATE,
PCMS_CHDOC_DATA_a.CH_DOC_ID "TDR-ID",
PCMS_CHDOC_DATA_b.CH_DOC_ID "CA-ID"

FROM
PCMS_CHDOC_DATA PCMS_CHDOC_DATA_a,
PCMS_CHDOC_DATA PCMS_CHDOC_DATA_b,
PCMS_CHDOC_RELATED_CHDOC PCMS_CHDOC_RELATED_CHDOC

WHERE
PCMS_CHDOC_DATA_a.CH_UID = PCMS_CHDOC_RELATED_CHDOC.FROM_CH_UID AND
PCMS_CHDOC_RELATED_CHDOC.TO_CH_UID = PCMS_CHDOC_DATA_b.CH_UID AND
((PCMS_CHDOC_DATA_a.CH_DOC_TYPE='PDS')
AND (PCMS_CHDOC_DATA_b.TARGET_RELEASE='13.3.0'))

ORDER BY
PCMS_CHDOC_DATA_b.PDS_REF


with the Notes database (PDS) linking on PCMS_CHDOC_DATA_b.PDS_REF and PCMS.CHDOC_DATA_b.TARGET_RELEASE

to PDS.ProjectNo and PDS.StreamAddress respectively.

can any one suggest how I do this? I have tried playing with the joins, but cant get the results...



Matt
London (UK)

 

[Madawc]

One option is to get the 'notes' data via a subreport, passing the relevant key. I've never tried the combination you are using, but sometimes it is the only way to get the data I want from a complex set of sources.

Subreports make a lot of work for computers, but save time for humans. You decide which is more important for your particular circumstances.

It also helps to give your Crystal version, since newer versions have extra options, and some extra problems. I use Crystal 8.5.

Madawc Williams (East Anglia, UK)

 

[matpj]

Thanks for that.
I am using Crystal 10
I am putting the Notes data in the main report (as this is the data that 'runs' the other - and the one that I want all rows from. I've created a sub-report with the oracle table and it seems to be working.

I just need to tackle some problems I have posted on here!

thanks again,

Matt
London (UK)