Using AND and OR's in CASE WHEN statement

Erik Aalbregt shared this problem 11 months ago
Defect Logged

Hello,

We are setting up a calculated field that includes a CASE WHEN statement.

Our first idea was the formula as seen in screenshot 1.

We are testing for values of 0 or NULL and in the end divide the difference by the first value.

This formula validates, but gives an error during execution. The error as shown is attached as screenshot 2.

We have spread the logic of the first when statement over 4 smaller when statements and this option does work.

We also tried various alternatives with less parentheses, but they all failed. And the alternative without any parentheses does work, but doesnt give the correct outcome, because the AND has precedence over the OR.


Why is option 1 with parentheses not working?


Thanks,

Erik

Comments (7)

photo
1

Hi Erik,

unfortunately I can't work out the cause of the error just from the screen shots, so could you please zip up and send across all log files from the folder <yellowfin>\appserver\logs

thanks,

David

photo
1

Hello Dave,

We have restarted the YF server with an empty log folder.

And replayed adding the calculated field to the report.

The error message is shown in the yellowfin.txt logfile.

Something to do with an invalid type operator.

Hopefully you can determine what the cause is.

With kind regards,

Erik Aalbregt

From: Yellowfin Support <support@yellowfin.bi>

Sent: Tuesday, July 24, 2018 4:30 AM

To: Erik Aalbregt <erik@visionbi.nl>

Subject: New Comment in "Using AND and OR's in CASE WHEN statement"

photo
1

Hi Erik,

thanks for the logs, I can see what's happened although I don't know why. Within the definition of the calculated field's formula there will be fragments like this:

OPERATOR[EQUAL]

or

OPERATOR[ISNOTNULL]

etc.

where the keyword OPERATOR is informing the parser that what's about to follow is the type of operator, however the the parsing has somehow gone awry and the parser thinks the keyword OPERATOR is a type.

I have tried to replicate the issue over here, I copied your formula but unfortunately I didn't get the error. I have attached a short video of my failure for you to see (don't worry about the 1.0 * I added to the formula, that is just a way of getting FLOAT results instead of INT).

I think it would certainly be interesting to see how Yellowfin has stored the formula, so if you are able to could you please run the following query and send the result to me:

SELECT ValidationFunctionName 
FROM ReportFieldTemplate 
WHERE ShortDescription = 'Verschil ingewikkelder'

Also, please tell me which DBMS your data source is stored in so I can better replicate your setup over here.

thanks,

David

photo
1

Hi David,

We are running SQL Server 2016.

And this is the formula as stored in the database (by using the query you provided):

CASE[CASE]CASE[WHEN]SET[(]SET[(]SET[(]FIELDID[2]OPERATOR[EQUAL]VALUE[0]SET[)]BOOLEAN[OR]SET[(]FIELDID[2]OPERATOR[ISNULL]SET[)]SET[)]BOOLEAN[AND]SET[(]SET[(]FIELDID[4]OPERATOR[EQUAL]VALUE[0]SET[)]BOOLEAN[OR]SET[(]FIELDID[4]OPERATOR[ISNULL]SET[)]SET[)]SET[)]CASE[ENDWHEN]CASE[THEN]VALUE[0]CASE[ENDTHEN]CASE[ELSE]SET[(]FIELDID[4]ARITHMETIC[-]FIELDID[2]SET[)]ARITHMETIC[/]FIELDID[2]CASE[ENDELSE]CASE[END]


Hopefully this would provide a clue.

With kind regards,

Erik

photo
1

Hi Erik,

thanks for running the query, although unfortunately the formula definition is well-formed.

I noticed from your logs that you are using the 20180226 build of 7.4, so I tried to replicate the issue again, this time using that same build and also using SQL Server 2016 but still no success.

In this situation the best thing to do is for us to set up your environment over here, so with that in mind are you able to please send across a dump of your YF_REPOS_TRAIN database, and also the schema of your data warehouse (i.e. we won't need the data to replicate this issue)?

Our ftp site is:

http://yellowfin.brickftp.com

and you don't need an account to access it, just upload the files.

regards,

David

photo
1

Hi David


I’ve uploaded the backup of the YF_REPOS_TRAIN

The schema of the database is actually the demo ski data project of Yellowfin.

And also a real environment at a client of ours.

We were trying to setup the calculation, which in the end gave us the error message.

So we made a comparable query at our training environment. I’ve send the earlier screenshots based on the query on the ski data project.

I’ve also attached the whole report (which contains a master query and a sub query and the generated SQL)


This query is without the calculation (the case when-statement), because with that field included I can’t request the SQL.

Hope this helps.


With kind regards,

Erik

photo
1

Hi Erik,

thanks for that, now I can easily replicate the issue, so I have raised product defect YFN-11643 so that it gets fixed.

Thanks for alerting us to this bug, and apologies for the inconvenience caused by it.

regards,

David