Answer
TECHNICAL_OBJECT_REFERENCE's key column isn't a bare part number at all — it's a full key-reference-format string, like PART_NO=<value>^, so joining it directly against a bare part number column, or trying to strip a trailing character off of it, doesn't correctly match. Instead, build the comparison the other direction: construct the equivalent key-reference-format string FROM the plain part number column, and compare that against the table's own key-reference column directly, for example ON 'PART_NO=' || pc.part_no || '^' = tor.key_ref — matching the exact key-reference format the table itself uses, including the field-name prefix and trailing caret, rather than trying to reverse-engineer or strip characters from the stored key-reference string to get back to a bare part number.