Finance | Project Operations, Human Resources, ...

Illegal use of WHERE expression with strReplace

(0) Share

Report

Posted on by mohammed.mqi

2,050

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 -

I have the same question (0)

All responses (5)

Answers (0)

Suggested answer

nmaenpaa 101,162 Moderator on at

Like (0)

Report

[deleted]

Was this reply helpful? Yes No
Suggested answer

nmaenpaa 101,162 Moderator on at

Like (0)

Report
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;

Was this reply helpful? Yes No
mohammed.mqi 2,050 on at

Like (0)

Report

thanks for replay

as you see here

dimensionFinancialTag.value

and here what i want to compare

i try as you said before but not working

Was this reply helpful? Yes No
mohammed.mqi 2,050 on at

Like (0)

Report

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

Was this reply helpful? Yes No
ToddB on at

Like (0)

Report

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.

Was this reply helpful? Yes No