Display SQL Statement tabs regardless of SQL error

Jonas De Beukelaer shared this idea 6 years ago
Completed

Currently, if there is a SQL error when trying to run a report, due to changes in the view or otherwise, the 'SQL statement' tab and 'view SQL' button are not available (attached).

I would argue that this tab is actually more useful to have when there is an error, as opposed to when it runs correctly, to help with debugging the SQL issue. The actual SQL error returned from the DB could also be included in this tab / on the report page, to help with resolving it.

Replies (12)

photo
1

Hi Jonas,

Thank you for reaching out. Unfortunately, I'm unsure what you are referring to here. Both of your screenshots are showing the option to view the SQL even though your query didn't return any results, which I've also been able to replicate:

7dc6de1293893542cc71cc7b8713b43454eee8cc9117899d334b1202e91f4c01

The only way I was able to get an error message where I couldn't view the SQL is when I created a Column with no values, and switched the last part of this code

SELECT DISTINCT
   COUNT(`lastfmimportdata`.`Artist`) / COUNT(`lastfmimportdata`.`Album`),
   `lastfmimportdata`.`Song`
FROM `lastfmimportdata`
GROUP BY 
   `lastfmimportdata`.`Song`
from 'Song' to 'Country,' which is an unpopulated Column in my database. However, Yellowfin does say it had an error retreiving the data from the database, so that narrows the issue down to something at the db level, which is unrelated to the SQL:

c36a4df1f593a4412af772251f60c7ad

Being able to view the SQL here wouldn't help with troubleshooting the issue in this case.

Basically, since what you're asking for is to see the SQL Error in the View SQL tab, I'm looking for an example where a SQL error is what is causing a report not to load. Can you please give me your use case with a corresponding screenshot of where you're not seeing the option to view the SQL, so I can get a better idea of what you're referring to?

Thanks,

Mike

photo
1

I understand what he is saying. He is speaking about cases when there is an error. The SQL is available if you click on the info button next to the title of the view for example:


4ce2ed566ce32b81a76d6ea906a4636c

Although it is available there, it is not available on errors and it is the SQL from the view, not the SQL after the report manipulates it. For example, I created a Freehand view with a CTE and even though my SQL is valid I received an error. I can assume that it's due to YellowfinBI's manipulation to the view's underlying SQL. Based on other reports YellowfinBI's natural response is to place the view's SQL in a sub-query which in this case makes the SQL error out as a CTE cannot be placed in a subquery:

1275475d34472ad4e4cd117b0036e7ab

I can only venture to guess that as I do not get to see the SQL that is generated after the report manipulates it which is typically made available by clicking on the "View SQL" button to the left of the "Refresh Report" button. If I could click on that button then perhaps I would be able to prove that my CTE is in fact written as a sub-query afterwards. That way, I could try to use something else rather than going back into the view editing a piece at a time when in fact, it will never work unless I remove CTE all together:

6dc5894ff8a2fdabf1cd318f7e203d30

photo
1

Hi Larry,

Thank you for your input. Indeed this is what I was looking for to proceed on this matter - an example where the SQL doesn't show up that can actually be traced back to the SQL itself, and not another potential error. I've gone ahead and raised this as an enhancement request and placed both of you on the client list in favor of this enhancement. Any potential updates regarding this matter will be posted here.

Thanks,

Mike

photo
1

I would also like to add that it would be nice to know what SQL was generated and thrown at the DBMS. I just had an error after changing a subquery join condition. It shouldn't have been an issue but I want to know the SQL syntax so that I can resolve the error.

photo
1

Hi Larry,

We do have LCTech on our list of clients who'd like to see this enhancement implemented. I'm afraid I don't have any updates as of yet, but I will certainly keep you all posted as soon as I have one!

Regards,

Mike

photo
2

Just want to add my support for this request.


Yellowfin should always be able to expose the SQL that it generated, regardless of whether it was successful, or received no rows, etc. Helps a great deal with troubleshooting views and report issues.

photo
1

Hi Bogdan,

Thank you for the feedback. Haven't heard anything on this yet, but I've added you to our client list in our internal task.

Regards,

Mike

photo
1

This would be extremely helpful for us, too. This may have been said upthread, but it seems that most of the time/always when a SQL error throws the report doesn't even show the query it was trying and failed to run in the Details panel--it's just omitted, making diagnosis impossible:

/fVpYtsRCAAAAAElFTkSuQmCC

photo
1

Hi Will,

Thank you for your feedback. I've added you to our client list of those who would like to see this enhancement in our internal task. Since my last update here, the CTO has indicated he thinks this would be a good idea, so I suspect this will be implemented at some point in the future.

Regards,

Mike

photo
1

This is essential in my opinion - otherwise when the sql code does not work (e.g. divide by zero in only some cases) you have no way to see why the query has failed.

We have recently spent hours trying to resolve an issue, not knowing if it was a problem with the yellowfin report structure or not.

It was only by pure luck, selecting filters that resulted in the report running, that we were able to copy the sql, and then run manually in SMSS using the filter values that failed.


Fortunately our system admin found that the error is reported in the log so in future we can at least find out what the issue is, even if it is an inefficient way to do so.

photo
1

Hi Gerri,

Thanks for the feedback. I am in agreement with you. I should note we now have this enhancement set to Highest priority in the internal task and is being considered for an upcoming build. I also added you to the client list in the internal task of those who'd like to see this enhancement. I will post further updates as they come along.

Regards,

Mike

photo
photo
2

Hi all,

Good news! This enhancement has been developed and will be published in the upcoming 9.0.1 (approximately a month from now), 7.4.13 (~1 month), and 8.0.6 (~2 months) builds.

Regards,

Mike

photo
1

Hi All,

This enhancement has been developed and can be found in version 9, the latest version of which can be found here.

Regards,

Mike

Leave a Comment
 
Attach a file