Freehand SQL - Coalesce

Hi Misty,

Thanks for reaching out. If you take the SQL Query being generated in the report and execute directly in your RDBMS, what is returned?

That format should be correct. It's returning results for me:

As you can see, numbers is null, so it's correctly returning dateint_string first:

That said, I'm not entirely sure how things would interact with other fields in the table, so can you aside from checking the query directly in your db also provide more details on your report layout, if applicable, and perhaps also test COALESCE(field1,field2,field3), if that's not what you're already doing?

That said, as you can see above, you can see some discrepancies with what YF is returning vs. what my DB is, which is maybe a bit odd... I've found that YF seems to be ordering the result set ASC without specifying that's what it's doing:

As you can see, it now matches what I see in YF, so it may be worth also checking your query directly in your DB after adding something like 'ORDER BY C1 ASC' at the end of your query to see what's returned and compare/contrast against the non-ordered result set.

Please let me know what you find.

Thanks,

Mike

Hi Mike - unfortunately I do not have the ability to directly query the database. I did ask my DBA to do run the query, and the results turned out as expected. So that's great, except I still can't see those results on my end. I've tried refreshing the data, logging in and out of the environment, I still can't get the results in my report.

For what it's worth, I tried running the coalesce query in our production environment (which is version 8), and it did work....mostly. For COALESCE (field1, field2, field3), I received results whenever field 1 had a value and field 2 was null. But if field 1 was null and field 2 had a value, I received no results.

Hi Mike - I confirmed this in both version 8 and version 9 environments. When using "coalesce(field1, field2)", I only get results if field 1 is NOT null.

Is there a different method of writing this so I can get results? It's definitely confusing to me why the coalesce works as expected when querying the database directly, but not in my report.

Hi Misty,

Thanks for the additional info. This could very well have to do with the database being used. I was testing with MySQL. Can you please inform me of what database you are using so I can run further tests?

Regards,

Mike

The database is version 2016 Microsoft SQL Server.

Hi Misty,

I've tested this in SQL Server and it's still working when first fields results are all NULL values.

I'm now wondering if this is driver-related as technically that's the last remaining piece that would be parsing values, so I'd next like to attempt this with the driver you're using.

Please head into your Admin Console > Data Sources > choose Data Source > expand Connection Settings and take a screenshot of your Connection Settings details, while expanding the JDBC Drier drop down if it doesn't fit, like so:

Also, just to clarify, when you're saying you don't get results, what does it look like in the Report Builder? Is it just a bunch of nulls?

Thanks,

Mike

Hi Mike - I've included a screenshot of our current settings - looks like the Driver and JDBC Driver are different. I've also included screenshots of the report so you can see that "Links Coalesce" is empty whenever the first value is null.

Hi Misty,

This is still working for me. I was actually testing in v8, but I just upgraded my v9.1 and tested this in 9.2, which comes with a different driver, the one that matches yours, while also making sure to set the Resulting Field Type value to 'Text', which is also new as of 9.2 and was another variable to eliminate from possibility here, and it's still returning results as expected:

(Just FYI, I have to cast my TestNumbers to nvarchar to avoid an attempted numeric conversion issue trying to return a column full of null values whose data type is intended to be numeric. This is an error that occurs in the database itself and is related to my selected data types, so it shouldn't be related to this issue, but just pointing it out in case you're wondering why I did this)

That said, the returned results could vary depending on what else is being returned by the SQL query, but I'm testing a report with only the COALESCE Calculated Field, and nothing more. When you tested in both 8 and 9, was it just the COALESCE (field1,field2) and/or COALESCE (field1,field2,field3) Calculated Fields you were testing in a report, or were they added to already-existing reports where there are other fields in the report? If the latter, then the test would actually be to take the reports' entire SQL query then execute it directly in the configuration database and see if anything is returned for that column, as opposed to just executing the COALESCE function by itself in the db.

Ultimately, after controlling for version/build, database, and JDBC driver I'm thinking this pretty much has to in some way be report setup and/or data related at this point, but let's also take a look at your logs just in case something is being generated there that YF isn't liking. Can you please provide those as well?

Thanks,

Mike

Hi Mike - I sent your comments to our developer and it turns out that some of the values I'm attempting to coalesce contain white spaces instead of null values. So this is definitely an issue with our data and not Yellowfin reporting. My apologies for creating extra work for you! You can close this ticket.

Hi Misty,

No problem. Glad to hear the issue has been identified!

As such, I’ll go ahead and close this out, but please don’t hesitate to reach out with any other questions or concerns.

Regards,

Mike