Hello all,

I'm trying to create a SQL expression to view the value from an optional field in the APIBDO table. The report being used is a GL Transaction Detail report with the following source container join:

((([GLPOST]
INNER JOIN [GLAMF]
  ON [GLPOST].[ACCTID] = [GLAMF].[ACCTID])
LEFT JOIN [GLACGRP]
  ON [GLAMF].[ACCTGRPCOD] = [GLACGRP].[ACCTGRPCOD])
INNER JOIN [CSCOM]
  ON [CSCOM].[CONAME] = [CSCOM].[CONAME])

I tried the following SQL Expression, but every entry in the report has the optional field value, when there should only be one entry:

(SELECT DISTINCT [APIBDO].[VALUE]

FROM [APIBDO]

JOIN APIBH ON [APIBH].[CNTBTCH] = [APIBDO].[CNTBTCH]

WHERE [APIBH].[CNTITEM] = [APIBDO].[CNTITEM] AND [APIBH].[CNTBTCH] = [APIBDO].[CNTBTCH])

All help is greatly appreciated!