Crystal Reports Left outer join and link type

[hg05]

I am looking to retrieve the value CATALOGUE_ITEM_2.LIST_PRICE = 200 as follows:

Issue: Not able to retrieve the value successfully. What am i doing wrong?

So I joined two Tables TEST and CATALOGUE_ITEM_2 (got to be an left outer join since there may be occasions when there is no value present)

where

CATALOGUE_ITEM_2.ITEM_CODE entry = "ABCD-EFG" as follows.

TEST.ANALYSIS ("ABCD") to CATALOGUE_ITEM_2.ITEM_CODE ("ABCD-EFG")

TEST.VARIATION ("EFG") to CATALOGUE_ITEM_2.ITEM_CODE ("ABCD-EFG")

My current setup is as follows: (see images attached)

The formula used is as follows:

IF (instr({CATALOGUE_ITEM_2.ITEM_CODE},{TEST.ANALYSIS}) > 0 ) THEN IF (instr({CATALOGUE_ITEM_2.ITEM_CODE},{TEST.VARIATION}) > 0) THEN //for variation //IF ({CATALOGUE_ITEM_2.ITEM_CODE} like {TEST.ANALYSIS} + "*" ) THEN //IF ({CATALOGUE_ITEM_2.ITEM_CODE} like "*" + {TEST.VARIATION}) THEN //for variation IF (isNULL({CATALOGUE_ITEM_2.LIST_PRICE}) = FALSE) THEN "Test Var Price (NZD): " + ToText({CATALOGUE_ITEM_2.LIST_PRICE}) ELSE "No Price Found" ELSE "No Price Found"

Any help is appreciated.

Regards

Geeta

 

[PeterDimitrov]

If you filter the left table, which I believe you do, the left join will be come inner join.

Select ... from a LEFT join b on a.id=b.id where b.name='xxx'

will be the same as:

Select ... from a INNER join b on a.id=b.id where b.name='xxx'

if you want to filter the left table you need to add the where clause to the join
Select ... from a LEFT join b on a.id=b.id and b.name='xxx'

to the best of my knowledge this is not supported by Crystal so the only option is to use command.

http://www.R-Tag.com

Viewer and Scheduler for Crystal reports, SSRS and Dynamic Dashboards.