Skip to main content

Notifications

Announcements

No record found.

News and Announcements icon
Community site session details

Community site session details

Session Id :
Dynamics 365 Community / Forums / Finance | Project Operations, Human Resources, ... / Illegal use of WHERE e...
Finance | Project Operations, Human Resources, ...
Suggested answer

Illegal use of WHERE expression with strReplace

(0) ShareShare
ReportReport
Posted on by mohammed.mqi 2,030

i want apply this query

select sum(NetAccountingCurrencyAmount),sum(GrossAccountingCurrencyAmount),count(Worker) from payrollPayStatement
    where payrollPayStatement.PayPeriod == payrollPayPeriodRecId
    exists join payrollPayStatementLine where payrollPayStatementLine.PayStatement==payrollPayStatement.RecId
    exists join hcmPosition where hcmPosition.RecId==payrollPayStatementLine.Position
    && strReplace(hcmPosition.PositionId, "-", "") like dimensionFinancialTag.Value;

but i getting this error 

the problem is this field (hcmPosition.PositionId) content -

and i want compare it without -

 

  • ToddB Profile Picture
    ToddB on at
    RE: Illegal use of WHERE expression with strReplace

    Hi Mohammed,

    Have you been able to select the first 8 characters of the PositionID; and then remove the '-' ?

    So 'SCR-BU01' would become 'SCRBU01'.

    And then use that to compare with the dimensionFinancialTag.value.

  • mohammed.mqi Profile Picture
    mohammed.mqi 2,030 on at
    RE: Illegal use of WHERE expression with strReplace

    it's still show me empty any idea how to solve this?

  • mohammed.mqi Profile Picture
    mohammed.mqi 2,030 on at
    RE: Illegal use of WHERE expression with strReplace

    thanks for replay

    as you see here

    dimensionFinancialTag.value

    tag.JPG

    and here what i want to compare 

    postin.JPG

    i try as you said before but not working

  • Suggested answer
    nmaenpaa Profile Picture
    nmaenpaa 101,156 on at
    RE: Illegal use of WHERE expression with strReplace

    So you have Positions, and Dimensions which resemble each other but are not really related?

    Could you please share some example values of both?

    Perhaps there's some other approach to the problem, but only such person who knows more details could know.

    But, based on only the details that you wanted to share so far, you need to first convert dimensionFinancialTag.Value into a string that you can use as criteria for HcmPosition.PositionId.

    Something like this (please treat it as an illustrative example - your responsibility is to apply this approach to your exact situation):

    HcmPositionId positionIdCriteria = strIns(dimensionFinancialTag.Value, '-', 3);

select sum(NetAccountingCurrencyAmount),sum(GrossAccountingCurrencyAmount),count(Worker) from payrollPayStatement
    where payrollPayStatement.PayPeriod == payrollPayPeriodRecId
    exists join payrollPayStatementLine where payrollPayStatementLine.PayStatement==payrollPayStatement.RecId
    exists join hcmPosition where hcmPosition.RecId==payrollPayStatementLine.Position
    && hcmPosition.PositionId like positionIdCriteria;

  • Suggested answer
    nmaenpaa Profile Picture
    nmaenpaa 101,156 on at
    RE: Illegal use of WHERE expression with strReplace
    [deleted]

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

Daivat Vartak – Community Spotlight

We are honored to recognize Daivat Vartak as our March 2025 Community…

Announcing Our 2025 Season 1 Super Users!

A new season of Super Users has arrived, and we are so grateful for the daily…

Kudos to the February Top 10 Community Stars!

Thanks for all your good work in the Community!

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 292,516 Super User 2025 Season 1

#2
Martin Dráb Profile Picture

Martin Dráb 231,430 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,156

Leaderboard

Featured topics

End-of-life Announcement for Microsoft Dynamics GP

Product updates

Dynamics 365 release plans