Crystal Reports Left outer join and link type
Crystal Reports Left outer join and link type
(OP)
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
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
Talk To Other Members
RE: Crystal Reports Left outer join and link type
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.
www.R-Tag.com Viewer and Scheduler for Crystal reports, SSRS and Dynamic Dashboards.