Main Report Field not working as parameter in SQL Expression

[DrBrentRD]

CR 10.0.5.820 For Sage

I have the following SQL Expression that doesn't work. The issue is with the last select statement which tries to pull the criteria from the main report ("timItem"."ItemID") table, which is the same table both of the nested queries are using. I've tried every permutation of the main report field, table aliases, CONVERT/CAST, etc. and the result is always blank. However, if I hard code the criteria (e.g. '24075') then it returns the correct value. Any help is greatly appreciated.

(SELECT timItem.ItemID
FROM timItem
INNER JOIN to2BOMLine ON to2BOMLine.ItemKey=timItem.ItemKey
INNER JOIN to2BOM ON to2BOMLine.BOMKey=to2BOM.BOMKey
WHERE timItem.ItemID LIKE 'BL%' AND to2BOM.BOMID IN
(SELECT timItem.ItemID
FROM timItem
INNER JOIN to2BOMLine ON to2BOMLine.ItemKey=timItem.ItemKey
INNER JOIN to2BOM ON to2BOMLine.BOMKey=to2BOM.BOMKey
WHERE BOMID = "timItem"."ItemID" AND timItem.ItemID LIKE 'CP%'))

Thanks.

 

[kray4660]

Just a suggestion. I would alias the tables in the sub-query and fully qualify the field names in the sub-query (avoids confusion for the query interpreter).

 

[DrBrentRD]

Kray4660,

Thanks for the suggestion. I should have mentioned that I already tried that. When I did. It gave me the "cannot bind multipart identifier timItem.ItemID" error. I can't seem to get around this error regardless of how try and demarcate the actual parameter field. The above is the only syntax that I've managed to actually execute, it just returns nothing.

Thanks.

 

[kray4660]

Unfortunately I know nothing about the Sage database and the SQL it likes. Also you did not share what you had tried, maybe there was something amiss that could have been fixed.