Table Joins in Crystal 8.5

[tekniks]

Hi,

I want to display the following information in the sub-report which is linked to the Main Report on Batch_ID.

Mat_ID Request_ID
FM12 123
FM34 234
FM56 345
249
934

But the report only shows the following:
Mat_ID Request_ID
FM12 123
FM34 234
FM56 345

The tables in the sub-report are linked as follows:

MM_GENEALOGY_LG CL_RQST_BTCH_LINK_ST CL_REQUEST_ST
MATERIAL_ID BATCH_ID REQUEST_ID
LOT_ID REQUEST_ID REQUEST_TYPE
BATCH_ID

1)The MM_GENEALOGY_LG.lot_id is linked to CL_RQST_BTCH_LINK_ST by left outer join.
2)The CL_RQST_BTCH_LINK_ST.request_id is linked to CL_REQUEST_ST.request_id by equal join.

I had thought that the left outer join at step 1) above should give the desired result but it did not. The selection query resulting from above is given below:
SELECT
MM_GENEALOGY_LG."BATCH_ID", MM_GENEALOGY_LG."MATERIAL_ID", MM_GENEALOGY_LG."LOT_ID",

CL_REQUEST_ST."REQUEST_ID"
FROM
"POMS"."MM_GENEALOGY_LG" MM_GENEALOGY_LG,
"POMS"."CL_RQST_BTCH_LINK_ST" CL_RQST_BTCH_LINK_ST,
"POMS"."CL_REQUEST_ST" CL_REQUEST_ST
WHERE

MM_GENEALOGY_LG."LOT_ID" = CL_RQST_BTCH_LINK_ST."BATCH_ID" (+) AND
CL_RQST_BTCH_LINK_ST."REQUEST_ID" = CL_REQUEST_ST."REQUEST_ID" AND
MM_GENEALOGY_LG."BATCH_ID" = 'H04727'
ORDER BY
MM_GENEALOGY_LG."BOM_REF_NO" ASC

Anybody can suggest any other method to achieve the result or is it that I'm expecting too much from CR 8.5.

Thanks

Tek

 

[kskid]

Shouldn't it be

MM_GENEALOGY_LG."BATCH_ID" = CL_RQST_BTCH_LINK_ST."BATCH_ID" (+)


instead of

MM_GENEALOGY_LG."LOT_ID" = CL_RQST_BTCH_LINK_ST."BATCH_ID" (+)


Also

CL_RQST_BTCH_LINK_ST - LOJ - CL_REQUEST_ST

 

[lbass]

To add a comment supporting witchitakid's suggestions: Every join to the right of a left join must also be a left join.

-LB

 

[tekniks]

The link shown in my post is correct and that's the way it works in our business:

MM_GENEALOGY_LG."LOT_ID" = CL_RQST_BTCH_LINK_ST."BATCH_ID" (+)

I tried with
CL_RQST_BTCH_LINK_ST.REQUEST_ID=CL_REQUEST_ST.REQUEST_ID(+)

but still it doesn't work.

The sql generated is:

SELECT
MM_GENEALOGY_LG."BATCH_ID", MM_GENEALOGY_LG."MATERIAL_ID", MM_GENEALOGY_LG."LOT_ID",

CL_REQUEST_ST."REQUEST_ID"
FROM
"POMS"."MM_GENEALOGY_LG" MM_GENEALOGY_LG,
"POMS"."CL_RQST_BTCH_LINK_ST" CL_RQST_BTCH_LINK_ST,
"POMS"."CL_REQUEST_ST" CL_REQUEST_ST
WHERE

MM_GENEALOGY_LG."LOT_ID" = CL_RQST_BTCH_LINK_ST."BATCH_ID" (+) AND
CL_RQST_BTCH_LINK_ST."REQUEST_ID" = CL_REQUEST_ST."REQUEST_ID" (+) AND
MM_GENEALOGY_LG."BATCH_ID" = 'H04727'
AND CL_REQUEST_ST."REQUEST_TYPE" = 'FMR'
ORDER BY
MM_GENEALOGY_LG."BOM_REF_NO" ASC

Is it that because of the last line in the where clause says REQUEST_TYPE = 'FMR' that is why I'm getting only first 3 records with FM and because the other 2 are not of type 'FMR'.

Is there any way to bring those up too.

Thanks

Tek

 

[lbass]

Yes, that could be the problem. Your SQL query looks okay otherwise, but you need to remove: CL_REQUEST_ST."REQUEST_TYPE" = 'FMR' from the record selection formula of the subreport. Instead create a formula in the formula editor:

if isnull({CL_REQUEST_ST.REQUEST_ID}) or
{CL_REQUEST_ST.REQUEST_TYPE} <> "FMR" then "" else
{CL_REQUEST_ST.REQUEST_ID}

Use this in your subreport instead of the Request ID field if you only want to display those records from the Request table where the type = FMR.

-LB