QoQ and modulus?

[WolfShade]

Hello, all,

I have been trying to use a modulus in a QoQ, and it's not working.

SELECT cola, colb, colc 
FROM queryName 
WHERE cola % 2 = 0 
/* cola is an integer 1 - 10 */

.. and I get..

Encountered "cola 2 =. Incorrect conditional expression, Incorrect conditional expression, Lexical error at line 1, column 45. Encountered: " " (32), after : "%" 
The error occurred on line 445.

(There are 16 lines of code, so I assume that the line 445 is in query.cfc.)

Can you use % or mod() in a QoQ???

V/r,

^ _ ^

[EddieLotter · Accepted solution]

I created a quick test and got the same error.

It appears the modulus operator is not supported in QoQ.

Cheers

Eddie

[WolfShade]

Bummer.  I'll go and submit a feature request, coz that could definitely come in handy.

V/r,

^ _ ^

[EddieLotter]

If you are populating the query from a database and then using QoQ to process the database query results, an interim solution to your scenario would be to add a column using the modulus operator in your database query. You can then use that additional column's value as a simple integer in your QoQ.

Cheers

Eddie

[WolfShade]

Good idea.  Hadn't considered that.  Thanks. 

V/r,

^ _ ^

[BKBK]

WHERE cola MOD 2 = 0

[EddieLotter]

BKBK  wrote

WHERE cola MOD 2 = 0

That gives the lovely message:

Encountered "MOD 2. Incorrect conditional expression, Expected one of [like|null|between|in|comparison] condition, Unrecognized comparison operator,

Note further that the message opens a double-quote but does not close it and ends with a comma.

Cheers

Eddie

[BKBK]

There's really no modulus operator in query-of-query? I am surprised - and a bit disappointed. @Wolfshade, let us know when you're done with the feature request. Then we can vote for it.

[WolfShade]

I filled out the form, clicked submit, and it failed.  I'll try, again, and let you know the number.  If it succeeds. 

V/r,

^ _ ^

[WolfShade]

Hey!  It worked!

Okay, BKBK and EddieLotter​, the URL is: https://tracker.adobe.com/#/view/CF-4200041

Much appreciated.

V/r,

^ _ ^

[BKBK]

Here are some nuts while u wait:

WHERE cola - 2*CAST(cola/2 as INTEGER) = 0

That is for MOD 2. For MOD 7 you would do:

WHERE cola - 7*CAST(cola/7 as INTEGER) = 0

[WolfShade]

OMG!!!  That's brilliant!!  How did you come up with this??

V/r,

^ _ ^

[EddieLotter]

He's manually doing what the modulus operator does.

Cheers

Eddie

[WolfShade]

Wait.. it's returning everything.  (I loved the math, didn't test it first.)

cola                    manual mod                   result
1                        1 - 2 * (1 / 2)              1 / 2 = .5; 2 * .5 = 1; 1 - 1 = 0
2                        2 - 2 * (2 / 2)              2 / 2 = 1; 2 * 1 = 2; 2 - 2 = 0
3                        3 - 2 * (3 / 2)              3 / 2 = 1.5; 2 * 1.5 = 3; 3 - 3 = 0
4                        4 - 2 * (4 / 2)              4 / 2= 2; 2 * 2 = 4; 4 - 4 = 0

On the last line, the "22" is not me.. the JIVE editor is doing that.  ^^

[BKBK]

WolfShade wrote:

cola                    manual mod                          result 

2                        2 - 2 * (2 / 2)              2 / 2 = 1; 2 * 1 = 2; 2 - 2 = 0 

3                        3 - 2 * (3 / 2)              3 / 2 = 1.5; 2 * 1.5 = 3; 3 - 3 = 0

It works instead like this (the casting getting rid of the halves):

cola                       manual mod                          result

2                        2 - 2 * cast(2/2 as INTEGER)      2 - 2*1 = 0

3                        3 - 2 * cast(3/2 as INTEGER)      3 - 2*1 = 1

[WolfShade]

Okay.. I had been getting errors and it looked like the CAST was causing it.  But, you're right.. once I was able to get the CAST working, it did exactly that.  This is an excellent workaround until Adobe (ahem) adds this functionality.  Thanks!

V/r,

^ _ ^

UPDATE:  I would mark your answer as correct, even though EddieLotter​'s answer is technically (and currently) correct.  But Jive isn't allowing that.

[EddieLotter]

I don't care about points. 🙂

I have sent you a PM about how to possibly change which post is marked as the answer, otherwise you can get Adobe to do it.

Cheers

Eddie

[BKBK]

WolfShade  wrote

I would mark your answer as correct, even though EddieLotter 's answer is technically (and currently) correct.  But Jive isn't allowing that.

I think EddieLotter's is the correct answer!

[WolfShade]

BKBK  wrote

I think EddieLotter's is the correct answer!

Since modulus isn't supported in QoQ, yes, agreed.  But your workaround solution is perfect.  Spot on!

V/r,

^ _ ^

[BKBK]

Thanks for explaining, EddieLotter. I couldn't have put it better.