I have a report which uses an SQL Expression to determine a value related to an Involvement. Nowadays, I tend to write my reports with Commands from the start, so slotting in a subquery is no problem. But this one was built with tables.
The SQL Expression is
(SELECT vv.UDF_VALUE
FROM VIEW_UDF_ENTITY_VALUE vv INNER JOIN UDF_ENTITYTYPE_LINK ulnk
ON vv.UDF_ENTITY_LINK_ID=ulnk.UDF_ENTITY_LINK_ID
INNER JOIN UDF_DEFINITION udef ON ulnk.UDF_ID=udef.UDF_ID
WHERE udef.UDF_ID=10250 and vv.ENTITY_ID="INVOLVEMENT"."INVOLVEMENT_ID")
which worked fine. Then I got informed there was a lookup table and I needed to show the description instead of the value. So I changed it to
(SELECT lk.description
FROM VIEW_UDF_ENTITY_VALUE vv INNER JOIN UDF_ENTITYTYPE_LINK ulnk
ON vv.UDF_ENTITY_LINK_ID=ulnk.UDF_ENTITY_LINK_ID
INNER JOIN UDF_DEFINITION udef ON ulnk.UDF_ID=udef.UDF_ID
left outer join udf_lookups lk on vv.udf_value = lk.int_code
WHERE udef.UDF_ID=10250 and vv.ENTITY_ID="INVOLVEMENT"."INVOLVEMENT_ID")
And now it fails saying "INVOLVEMENT"."INVOLVEMENT_ID" is not a valid expression.
I think this is to do with the way crystal parses SQL expressions. Once in there, always in there, even if deleted. I have tried all sorts of ways to get it to accept it, but no go.
Any suggestions, short of rebuilding the report? Which, in frustration, I have done in the past. But this one is a bit big.
Thanks