Preprocessor Query

[JasmeenB]

Hi Team,

We have a requirement to calculate the grade based on grade code.

the logic is.

if (Grade >= 1 && Grade <= 7)   {
  Result= "A";
}
else if (Grade >= 8 && Grade <= 10)  {
  Result= "B";
}
else {
Result="C";
}

We have written a preprocessor query to calculate that value.

"UPDATE NEWUSERDATA LEFT JOIN CURRENTUSERS ON NEWUSERDATA.USERNAME = CURRENTUSERS.USERNAME SET NEWUSERDATA.customproperty45 = (case when (CURRENTUSERS.customproperty12 >='1' & CURRENTUSERS.customproperty12 <='7') then 'A' when (CURRENTUSERS.customproperty12 >='8' & CURRENTUSERS.customproperty12 <='10') then 'B' else 'C' end)"

But the result is not correct , it is always giving result as A.

Could anyone please point out what is wrong here?

[RakeshMG]

Could you please try:

UPDATE NEWUSERDATA LEFT JOIN CURRENTUSERS ON NEWUSERDATA.USERNAME = CURRENTUSERS.USERNAME SET NEWUSERDATA.customproperty45 = (case when (CURRENTUSERS.customproperty12 >='1' & CURRENTUSERS.customproperty12 <'8') then 'A' when (CURRENTUSERS.customproperty12 >'7' & CURRENTUSERS.customproperty12 <'11') then 'B' else 'C' end)

[JasmeenB]

Hi rakesh,

Thank you for your reply. But the result is same the value is only coming as A.

Also one thing we are storing the grade code as char and the value is coming as 08.

[DixshantValecha]

The issue seems to be with the use of the & operator in the case statement ,the logical AND operator is represented by AND, not &. Try replacing & with AND in your query and see if that fixes the issue.

Could you please try query:

“UPDATE NEWUSERDATA LEFT JOIN CURRENTUSERS ON NEWUSERDATA.USERNAME = CURRENTUSERS.USERNAME SET NEWUSERDATA.customproperty45 = (case when (CURRENTUSERS.customproperty12 >=‘1’ AND CURRENTUSERS.customproperty12 <=‘7’) then ‘A’ when (CURRENTUSERS.customproperty12 >=‘8’ AND CURRENTUSERS.customproperty12 <=‘10’) then ‘B’ else ‘C’ end)”

[JasmeenB]

Hi,

We had tried this also replacing '&' with AND but this also gave same knd of response. either the value is A or C everytime

[DixshantValecha · Accepted solution]

Make sure that the values in the CURRENTUSERS.customproperty12 column are numeric. If they are stored as strings, the comparison operators (>=, <=) will perform a lexicographic comparison instead of a numeric comparison. You can try casting the values to a numeric type before performing the comparison, like this: CAST(CURRENTUSERS.customproperty12 AS SIGNED) >= 1.

"UPDATE NEWUSERDATA LEFT JOIN CURRENTUSERS ON NEWUSERDATA.USERNAME = CURRENTUSERS.USERNAME SET NEWUSERDATA.customproperty45 = (CASE WHEN (CAST(CURRENTUSERS.customproperty12 AS SIGNED) >= 1 AND CAST(CURRENTUSERS.customproperty12 AS SIGNED) <= 7) THEN 'A' WHEN (CAST(CURRENTUSERS.customproperty12 AS SIGNED) >= 8 AND CAST(CURRENTUSERS.customproperty12 AS SIGNED) <= 10) THEN 'B' ELSE 'C' END)"

[dgandhi]

Hi @JasmeenB 

Can you try below?

"UPDATE NEWUSERDATA u1 SET u1.customproperty45 =
(case when u1.customproperty12 >=1 AND u1.customproperty12 <=7 THEN 'A'
when u1.customproperty12 >=8 AND u1.customproperty12 <=10 THEN 'B' ELSE 'C' END)"

Thanks