• Global community
    • Language:
      • Deutsch
      • English
      • Español
      • Français
      • Português
  • 日本語コミュニティ
    Dedicated community for Japanese speakers
  • 한국 커뮤니티
    Dedicated community for Korean speakers
Exit
0

QoQ and modulus?

LEGEND ,
Oct 18, 2017 Oct 18, 2017

Copy link to clipboard

Copied

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,

^ _ ^

Views

704

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines

correct answers 1 Correct answer

Advocate , Oct 18, 2017 Oct 18, 2017

I created a quick test and got the same error.

It appears the modulus operator is not supported in QoQ.

Cheers

Eddie

Votes

Translate

Translate
Advocate ,
Oct 18, 2017 Oct 18, 2017

Copy link to clipboard

Copied

I created a quick test and got the same error.

It appears the modulus operator is not supported in QoQ.

Cheers

Eddie

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
LEGEND ,
Oct 19, 2017 Oct 19, 2017

Copy link to clipboard

Copied

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

V/r,

^ _ ^

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Advocate ,
Oct 19, 2017 Oct 19, 2017

Copy link to clipboard

Copied

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

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
LEGEND ,
Oct 19, 2017 Oct 19, 2017

Copy link to clipboard

Copied

Good idea.  Hadn't considered that.  Thanks. 

V/r,

^ _ ^

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Community Expert ,
Oct 19, 2017 Oct 19, 2017

Copy link to clipboard

Copied

WHERE cola MOD 2 = 0

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Advocate ,
Oct 19, 2017 Oct 19, 2017

Copy link to clipboard

Copied

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

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Community Expert ,
Oct 19, 2017 Oct 19, 2017

Copy link to clipboard

Copied

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.

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
LEGEND ,
Oct 19, 2017 Oct 19, 2017

Copy link to clipboard

Copied

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,

^ _ ^

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
LEGEND ,
Oct 19, 2017 Oct 19, 2017

Copy link to clipboard

Copied

Hey!  It worked!

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

Much appreciated.

V/r,

^ _ ^

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Community Expert ,
Oct 19, 2017 Oct 19, 2017

Copy link to clipboard

Copied

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

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
LEGEND ,
Oct 19, 2017 Oct 19, 2017

Copy link to clipboard

Copied

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

V/r,

^ _ ^

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Advocate ,
Oct 19, 2017 Oct 19, 2017

Copy link to clipboard

Copied

He's manually doing what the modulus operator does.

Cheers

Eddie

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
LEGEND ,
Oct 19, 2017 Oct 19, 2017

Copy link to clipboard

Copied

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

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Community Expert ,
Oct 19, 2017 Oct 19, 2017

Copy link to clipboard

Copied

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

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
LEGEND ,
Oct 19, 2017 Oct 19, 2017

Copy link to clipboard

Copied

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.

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Advocate ,
Oct 19, 2017 Oct 19, 2017

Copy link to clipboard

Copied

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

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Community Expert ,
Oct 19, 2017 Oct 19, 2017

Copy link to clipboard

Copied

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!

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
LEGEND ,
Oct 19, 2017 Oct 19, 2017

Copy link to clipboard

Copied

LATEST

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,

^ _ ^

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Community Expert ,
Oct 19, 2017 Oct 19, 2017

Copy link to clipboard

Copied

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

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Resources
Documentation