Ticket #29940
Question
Is there any way around this issue where a special character (#) is in the field name (LBLBL#) that is being referenced in a sql subselect in a calculation? sql subselect: (select 'lblbl#' as label from pirf.ordh left outer join pirf.ordd on odwhse=ohwhse and odord=ohord left outer join pirf.label on lbwhse=odwhse and lbord=odord and lbseq=odseq left outer join mrc_main.piritemv on ititem=oditem where ohwhse=1 and ohcord=&HHIORNR and ititemr=&HHIITMN) m-Power is wrapping the field in quotations and trying to evaluate the field as a literal string.
Answer
Unfortunately subqueries have always been a topic that is under the umbrella of custom coding, making it a challenge for us to provide helpdesk support for these kinds of questions. There are ways outside of a custom subquery to returned a lookup a value, namely the JSON Lookup Retrieval or Imports. However this is not to say these are your only options. Special characters in subquery fields have often posed complications, and what some customers have done alternatively is create an SQL view over the table that does not use a "#" symbol, and then use this View instead for your application. 
Asked on January 23, 2020