Custom Column Error

[Bill_Balas]

Hello,

upon creating a column with this code, I'm getting a "The statement is not valid SQL" pop-up.
It translates the "NOT (Connectedness on Turn Any Made Straight is Possible)" filter.

Would you take a look, please?

Thanks

CODE:
sum(if[(((NOT (((((cash_hand_summary.card_4 > 0 and ((0 in (cash_hand_summary.card_1 % 13, cash_hand_summary.card_2 % 13, cash_hand_summary.card_3 % 13, cash_hand_summary.card_4 % 13) AND ((1 in (cash_hand_summary.card_1 % 13, cash_hand_summary.card_2 % 13, cash_hand_summary.card_3 % 13, cash_hand_summary.card_4 % 13) AND (2 in (cash_hand_summary.card_1 % 13, cash_hand_summary.card_2 % 13, cash_hand_summary.card_3 % 13, cash_hand_summary.card_4 % 13) OR 3 in (cash_hand_summary.card_1 % 13, cash_hand_summary.card_2 % 13, cash_hand_summary.card_3 % 13, cash_hand_summary.card_4 % 13) OR 4 in (cash_hand_summary.card_1 % 13, cash_hand_summary.card_2 % 13, cash_hand_summary.card_3 % 13, cash_hand_summary.card_4 % 13))) OR (2 in (cash_hand_summary.card_1 % 13, cash_hand_summary.card_2 % 13, cash_hand_summary.card_3 % 13, cash_hand_summary.card_4 % 13) AND (3 in (cash_hand_summary.card_1 % 13, cash_hand_summary.card_2 % 13, cash_hand_summary.card_3 % 13, cash_hand_summary.card_4 % 13) OR 4 in (cash_hand_summary.card_1 % 13, cash_hand_summary.card_2 % 13, cash_hand_summary.card_3 % 13, cash_hand_summary.card_4 % 13))) OR (3 in (cash_hand_summary.card_1 % 13, cash_hand_summary.card_2 % 13, cash_hand_summary.card_3 % 13, cash_hand_summary.card_4 % 13) AND 4 in (cash_hand_summary.card_1 % 13, cash_hand_summary.card_2 % 13, cash_hand_summary.card_3 % 13, cash_hand_summary.card_4 % 13)) OR (12 in (cash_hand_summary.card_1 % 13, cash_hand_summary.card_2 % 13, cash_hand_summary.card_3 % 13, cash_hand_summary.card_4 % 13) AND (11 in (cash_hand_summary.card_1 % 13, cash_hand_summary.card_2 % 13, cash_hand_summary.card_3 % 13, cash_hand_summary.card_4 % 13) OR 10 in (cash_hand_summary.card_1 % 13, cash_hand_summary.card_2 % 13, cash_hand_summary.card_3 % 13, cash_hand_summary.card_4 % 13) OR 9 in (cash_hand_summary.card_1 % 13, cash_hand_summary.card_2 % 13, cash_hand_summary.card_3 % 13, cash_hand_summary.card_4 % 13))) OR (11 in (cash_hand_summary.card_1 % 13, cash_hand_summary.card_2 % 13, cash_hand_summary.card_3 % 13, cash_hand_summary.card_4 % 13) AND (10 in (cash_hand_summary.card_1 % 13, cash_hand_summary.card_2 % 13, cash_hand_summary.card_3 % 13, cash_hand_summary.card_4 % 13) OR 9 in (cash_hand_summary.card_1 % 13, cash_hand_summary.card_2 % 13, cash_hand_summary.card_3 % 13, cash_hand_summary.card_4 % 13))) OR (10 in (cash_hand_summary.card_1 % 13, cash_hand_summary.card_2 % 13, cash_hand_summary.card_3 % 13, cash_hand_summary.card_4 % 13) AND 9 in (cash_hand_summary.card_1 % 13, cash_hand_summary.card_2 % 13, cash_hand_summary.card_3 % 13, cash_hand_summary.card_4 % 13)))) OR (0 not in (cash_hand_summary.card_1 % 13, cash_hand_summary.card_2 % 13, cash_hand_summary.card_3 % 13, cash_hand_summary.card_4 % 13) AND (((cash_hand_summary.card_1 % 13) + 4 in (cash_hand_summary.card_2 % 13, cash_hand_summary.card_3 % 13, cash_hand_summary.card_4 % 13) AND ((cash_hand_summary.card_2 % 13 BETWEEN (cash_hand_summary.card_1 % 13) + 1 AND (cash_hand_summary.card_1 % 13) + 3) OR (cash_hand_summary.card_3 % 13 BETWEEN (cash_hand_summary.card_1 % 13) + 1 AND (cash_hand_summary.card_1 % 13) + 3) OR (cash_hand_summary.card_4 % 13 BETWEEN (cash_hand_summary.card_1 % 13) + 1 AND (cash_hand_summary.card_1 % 13) + 3))) OR ((cash_hand_summary.card_1 % 13) + 3 in (cash_hand_summary.card_2 % 13, cash_hand_summary.card_3 % 13, cash_hand_summary.card_4 % 13) AND ((cash_hand_summary.card_2 % 13 BETWEEN (cash_hand_summary.card_1 % 13) + 1 AND (cash_hand_summary.card_1 % 13) + 2) OR (cash_hand_summary.card_3 % 13 BETWEEN (cash_hand_summary.card_1 % 13) + 1 AND (cash_hand_summary.card_1 % 13) + 2) OR (cash_hand_summary.card_4 % 13 BETWEEN (cash_hand_summary.card_1 % 13) + 1 AND (cash_hand_summary.card_1 % 13) + 2))) OR ((cash_hand_summary.card_1 % 13) + 2 in (cash_hand_summary.card_2 % 13, cash_hand_summary.card_3 % 13, cash_hand_summary.card_4 % 13) AND (cash_hand_summary.card_2 % 13 = (cash_hand_summary.card_1 % 13) + 1 OR cash_hand_summary.card_3 % 13 = (cash_hand_summary.card_1 % 13) + 1 OR cash_hand_summary.card_4 % 13 = (cash_hand_summary.card_1 % 13) + 1)) OR ((cash_hand_summary.card_2 % 13) + 4 in (cash_hand_summary.card_1 % 13, cash_hand_summary.card_3 % 13, cash_hand_summary.card_4 % 13) AND ((cash_hand_summary.card_1 % 13 BETWEEN (cash_hand_summary.card_2 % 13) + 1 AND (cash_hand_summary.card_2 % 13) + 3) OR (cash_hand_summary.card_3 % 13 BETWEEN (cash_hand_summary.card_2 % 13) + 1 AND (cash_hand_summary.card_2 % 13) + 3) OR (cash_hand_summary.card_4 % 13 BETWEEN (cash_hand_summary.card_2 % 13) + 1 AND (cash_hand_summary.card_2 % 13) + 3))) OR ((cash_hand_summary.card_2 % 13) + 3 in (cash_hand_summary.card_1 % 13, cash_hand_summary.card_3 % 13, cash_hand_summary.card_4 % 13) AND ((cash_hand_summary.card_1 % 13 BETWEEN (cash_hand_summary.card_2 % 13) + 1 AND (cash_hand_summary.card_2 % 13) + 2) OR (cash_hand_summary.card_3 % 13 BETWEEN (cash_hand_summary.card_2 % 13) + 1 AND (cash_hand_summary.card_2 % 13) + 2) OR (cash_hand_summary.card_4 % 13 BETWEEN (cash_hand_summary.card_2 % 13) + 1 AND (cash_hand_summary.card_2 % 13) + 2))) OR ((cash_hand_summary.card_2 % 13) + 2 in (cash_hand_summary.card_1 % 13, cash_hand_summary.card_3 % 13, cash_hand_summary.card_4 % 13) AND (cash_hand_summary.card_1 % 13 = (cash_hand_summary.card_2 % 13) + 1 OR cash_hand_summary.card_3 % 13 = (cash_hand_summary.card_2 % 13) + 1 OR cash_hand_summary.card_4 % 13 = (cash_hand_summary.card_2 % 13) + 1)) OR ((cash_hand_summary.card_3 % 13) + 4 in (cash_hand_summary.card_2 % 13, cash_hand_summary.card_1 % 13, cash_hand_summary.card_4 % 13) AND ((cash_hand_summary.card_2 % 13 BETWEEN (cash_hand_summary.card_3 % 13) + 1 AND (cash_hand_summary.card_3 % 13) + 3) OR (cash_hand_summary.card_1 % 13 BETWEEN (cash_hand_summary.card_3 % 13) + 1 AND (cash_hand_summary.card_3 % 13) + 3) OR (cash_hand_summary.card_4 % 13 BETWEEN (cash_hand_summary.card_3 % 13) + 1 AND (cash_hand_summary.card_3 % 13) + 3))) OR ((cash_hand_summary.card_3 % 13) + 3 in (cash_hand_summary.card_2 % 13, cash_hand_summary.card_1 % 13, cash_hand_summary.card_4 % 13) AND ((cash_hand_summary.card_2 % 13 BETWEEN (cash_hand_summary.card_3 % 13) + 1 AND (cash_hand_summary.card_3 % 13) + 2) OR (cash_hand_summary.card_1 % 13 BETWEEN (cash_hand_summary.card_3 % 13) + 1 AND (cash_hand_summary.card_3 % 13) + 2) OR (cash_hand_summary.card_4 % 13 BETWEEN (cash_hand_summary.card_3 % 13) + 1 AND (cash_hand_summary.card_3 % 13) + 2))) OR ((cash_hand_summary.card_3 % 13) + 2 in (cash_hand_summary.card_2 % 13, cash_hand_summary.card_1 % 13, cash_hand_summary.card_4 % 13) AND (cash_hand_summary.card_2 % 13 = (cash_hand_summary.card_3 % 13) + 1 OR cash_hand_summary.card_1 % 13 = (cash_hand_summary.card_3 % 13) + 1 OR cash_hand_summary.card_4 % 13 = (cash_hand_summary.card_3 % 13) + 1)) OR ((cash_hand_summary.card_4 % 13) + 4 in (cash_hand_summary.card_2 % 13, cash_hand_summary.card_3 % 13, cash_hand_summary.card_1 % 13) AND ((cash_hand_summary.card_2 % 13 BETWEEN (cash_hand_summary.card_4 % 13) + 1 AND (cash_hand_summary.card_4 % 13) + 3) OR (cash_hand_summary.card_3 % 13 BETWEEN (cash_hand_summary.card_4 % 13) + 1 AND (cash_hand_summary.card_4 % 13) + 3) OR (cash_hand_summary.card_1 % 13 BETWEEN (cash_hand_summary.card_4 % 13) + 1 AND (cash_hand_summary.card_4 % 13) + 3))) OR ((cash_hand_summary.card_4 % 13) + 3 in (cash_hand_summary.card_2 % 13, cash_hand_summary.card_3 % 13, cash_hand_summary.card_1 % 13) AND ((cash_hand_summary.card_2 % 13 BETWEEN (cash_hand_summary.card_4 % 13) + 1 AND (cash_hand_summary.card_4 % 13) + 2) OR (cash_hand_summary.card_3 % 13 BETWEEN (cash_hand_summary.card_4 % 13) + 1 AND (cash_hand_summary.card_4 % 13) + 2) OR (cash_hand_summary.card_1 % 13 BETWEEN (cash_hand_summary.card_4 % 13) + 1 AND (cash_hand_summary.card_4 % 13) + 2))) OR ((cash_hand_summary.card_4 % 13) + 2 in (cash_hand_summary.card_2 % 13, cash_hand_summary.card_3 % 13, cash_hand_summary.card_1 % 13) AND (cash_hand_summary.card_2 % 13 = (cash_hand_summary.card_4 % 13) + 1 OR cash_hand_summary.card_3 % 13 = (cash_hand_summary.card_4 % 13) + 1 OR cash_hand_summary.card_1 % 13 = (cash_hand_summary.card_4 % 13) + 1))))))))))))), 1, 0])

[WhiteRider]

You can't use "in" in column expressions because the commas cause complications. You will need to test the values explicitly with equals, less than, greater than, between, etc.

[Bill_Balas]

Ok, thanks. My ability to reconfigure this string is limited. Would you be able to exemplify?

Thank you

[WhiteRider]

I'm not at my normal computer right now so I can't do it for you, but I'd keep removing parts until I got it to validate, and then add sections back in to narrow down the part where the problem occurs. Alternatively start with a small part of it to make sure the basic building block is ok and then again add in parts until it stops validating.

[Bill_Balas]

Thanks for the perspective.
Please help me understand a couple of code sections. Considering,

A) (original code section):
(cash_hand_summary.card_4 > 0 and ((0 in (cash_hand_summary.card_1 % 13, cash_hand_summary.card_2 % 13, cash_hand_summary.card_3 % 13, cash_hand_summary.card_4 % 13))))

B) (custom code section):
(cash_hand_summary.card_4 > 0 AND (cash_hand_summary.card_1 % 13 = 0 AND cash_hand_summary.card_2 % 13 = 0 AND cash_hand_summary.card_3 % 13 = 0 cash_hand_summary.card_4 % 13 =0))

Despite not being "valid SQL", is the B) code an accurate translation of the original code A)?


Considering, (cash_hand_summary.card_1 % 13 = (cash_hand_summary.card_2 % 13) + 1, is this section indicating the 1st and 2nd Flop cards are sequenced, like 67 or JQ?

Thanks

[WhiteRider]

You need to use 'OR' instead of 'AND' in:
(cash_hand_summary.card_1 % 13 = 0 AND cash_hand_summary.card_2 % 13 = 0 AND cash_hand_summary.card_3 % 13 = 0 cash_hand_summary.card_4 % 13 =0)
..if you want to test for any of them matching (which is what 'in' does).

Considering, (cash_hand_summary.card_1 % 13 = (cash_hand_summary.card_2 % 13) + 1, is this section indicating the 1st and 2nd Flop cards are sequenced, like 67 or JQ?

Yes, although you may need to do some extra tests for sequenced aces, and you'll also want to test for "-1" as well as "+1" in order to catch 76 as well as 67.

[Bill_Balas]

Ok, thanks. And hopefully the last question... are these 2 transformations correct?:

OR (0 not in (cash_hand_summary.card_1 % 13, cash_hand_summary.card_2 % 13, cash_hand_summary.card_3 % 13, cash_hand_summary.card_4 % 13)

to

OR (cash_hand_summary.card_1 % 13>=1 OR cash_hand_summary.card_2 % 13>=1 OR cash_hand_summary.card_3 % 13>=1 OR cash_hand_summary.card_4 % 13>=1)


...&...


AND (((cash_hand_summary.card_1 % 13) + 4 in (cash_hand_summary.card_2 % 13, cash_hand_summary.card_3 % 13, cash_hand_summary.card_4 % 13)

to

AND (((cash_hand_summary.card_1 % 13) + 4 AND (cash_hand_summary.card_2 % 13=0) OR
(cash_hand_summary.card_1 % 13) + 4 AND (cash_hand_summary.card_3 % 13=0) OR
(cash_hand_summary.card_1 % 13) + 4 AND (cash_hand_summary.card_4 % 13=0)))


Thanks

[WhiteRider]

OR (0 not in (cash_hand_summary.card_1 % 13, cash_hand_summary.card_2 % 13, cash_hand_summary.card_3 % 13, cash_hand_summary.card_4 % 13)

to

OR (cash_hand_summary.card_1 % 13>=1 OR cash_hand_summary.card_2 % 13>=1 OR cash_hand_summary.card_3 % 13>=1 OR cash_hand_summary.card_4 % 13>=1)

For 'not in' you'll need to use AND in this case because you want to test for all of them not matching 0.

AND (((cash_hand_summary.card_1 % 13) + 4 in (cash_hand_summary.card_2 % 13, cash_hand_summary.card_3 % 13, cash_hand_summary.card_4 % 13)

to

AND (((cash_hand_summary.card_1 % 13) + 4 AND (cash_hand_summary.card_2 % 13=0) OR
(cash_hand_summary.card_1 % 13) + 4 AND (cash_hand_summary.card_3 % 13=0) OR
(cash_hand_summary.card_1 % 13) + 4 AND (cash_hand_summary.card_4 % 13=0)))

"(cash_hand_summary.card_1 % 13) + 4 AND (cash_hand_summary.card_2 % 13=0)" isn't valid - you're not comparing the first part numerically (you're not using = or greater than/less than for example).
Instead of "(cash_hand_summary.card_1 % 13) + 4 AND (cash_hand_summary.card_2 % 13=0)" use "(cash_hand_summary.card_1 % 13) + 4 = (cash_hand_summary.card_2 % 13)"

[Bill_Balas]

Hello,

something not working on the code. Would you take a look at the attached file, please?
It's divided, section by section.

Thanks

[WhiteRider]

You still have "in" in the transformed code part and that won't work in column expressions. You also have at least one mismatched bracket over the whole expression, but that may be a factor of extracting things to separate lines.