Getting type issue on custom expression filder

1

I’m running into an error “operator does not exist: boolean <> text when I include two custom columns in a Custom expression for filtering.

My custom columns are both supposed to be booleans but it seems one of them both is not read/understood as a boolean.

case(
  [Is New World] = True
  AND notNull([Last Consumed Quota ID]),
  True,
  [Is New World] = False
  AND notIn([Status], 22, 21, 18, 5),
  True,
  False
)

case(notNull([Last Consumed Quota ID]), True, False)

The the filter formula is

[Has Consumed from Quota ID is true or old world] !=
  [Last Consumed Quota ID]

Combining the above runs into the error.

If I wrap the formula in a text function, the SQL runs ok but the results are wrong.

text(
  [Has Consumed from Quota ID is true or old world]
) !=
  text([Last Consumed Quota ID])

Not sure if there is a way to make sure boolean types are the outcome of a custom column without having to write the question in native SQL. Perhaps there are limitations with booleans on the query editor?

2

I think you mistyped this condition, [Last Consumed Quota ID] appears to be a non-boolean value. You probably meant to reference the custom column with definition

case(notNull([Last Consumed Quota ID]), True, False)

(whatever you called it).

I was able to write the condition that way, comparing the two custom columns for inequality, and the query succeeds.

Technically the case wrapper for the second one is extraneous, notNull already returns a boolean value (true or false, can’t return NULL).

3

AH, I think you are right.

I should not have referred to [Last Consumed Quota ID] but instead [Has Last Consumed Quota ID]. This is the name of my custom column.

The joys of being completely tired and making human mistakes.

Thanks for the help.

1 Like
4

You’re welcome once again. :slight_smile: