INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Main Report Field not working as parameter in SQL Expression

Main Report Field not working as parameter in SQL Expression

(OP)
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.

RE: Main Report Field not working as parameter in SQL Expression

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).

RE: Main Report Field not working as parameter in SQL Expression

(OP)
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.

RE: Main Report Field not working as parameter in SQL Expression

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.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close