Can't see or edit SQL for just one of my Views
I have a customer requesting edits to a report such that I need to edit the View to create a new field. The View was made via Freehand SQL in 2019 (data source summary is "MS SQL Server"). For this View, there's no "SQL" tab - I just see this:
And then in the clone or edit process, there's Procedure and Prepare tabs but no ability to see or edit SQL. After I click "Publish" and save it, it takes me back to the Admin Console instead of back to the browser screen, which often indicates a Yellowfin error though there's no error message.
When I edit a different Freehand SQL View, it behaves as expected - I can see the SQL:
Is this a documented problem? Do you have advice for restoring the ability to edit this view? Or if not, can I recover at least the SQL query itself via a Yellowfin database object?
The only unusual thing I can think of about this query is that the report that uses it has a JavaScript chart - though I doubt that's relevant.
Thanks,
Sam
The same question
Hi Sam,
Thanks for reaching out.
I think, there is no easy way for getting the SQL query of a view. Normally we would get it from the SQL tab from UI like you have mentioned. I think its better if we can see why we are unable to see the SQL tab on this particular view.
Could you please let me know your availability so that we can jump on a call to discuss the issue.
Best Regards,
Deepak
Hi Sam,
Thanks for reaching out.
I think, there is no easy way for getting the SQL query of a view. Normally we would get it from the SQL tab from UI like you have mentioned. I think its better if we can see why we are unable to see the SQL tab on this particular view.
Could you please let me know your availability so that we can jump on a call to discuss the issue.
Best Regards,
Deepak
Hi Deepak,
A screenshare sounds good. Best for me is 9am-5pm USA Eastern time but I recall that might be outside of your workday. I could meet at 9pm USA ET most nights, including Tues (tonight,) Weds, Thurs this week, do any of those work for you?
Thanks,
Sam
Hi Deepak,
A screenshare sounds good. Best for me is 9am-5pm USA Eastern time but I recall that might be outside of your workday. I could meet at 9pm USA ET most nights, including Tues (tonight,) Weds, Thurs this week, do any of those work for you?
Thanks,
Sam
Hi Sam,
Thanks for that. Sure, 9PM ET Wednesday 13 Apr works for me. It will be 11 am AEST Thursday.
Please use the below link to join the meeting: https://meetings.ringcentral.com/j/1490630793
And regarding the SQL query, I gave few tries yesterday and was able to fetch the query(Just in case if you need sql urgently)
Step 1: Execute the below query by replacing the ViewDescription - '%ViewName%' with the name of the view.
SELECT rv.ViewId, rv.ViewDescription, rv.ViewStatusCode, rv.SQLText, rv.SQLDocumentId, di.DocumentId, dr.RevisionId, dd.DataChunk
FROM ReportView rv
JOIN DocumentItem di ON rv.SQLDocumentId = di.DocumentId
JOIN DocumentRevision dr ON dr.DocumentId = di.DocumentId
JOIN DocumentData dd ON dd.RevisionId = dr.RevisionId
WHERE rv.ViewStatusCode = 'OPEN' AND
rv.ViewDescription like '%ViewName%'
We may get a result something like this:
Step 2: Copy the DataChunk Column and use any base64 converter to decode it. Then we can fetch the query encoded as below:
I have used this website to decode: https://www.base64decode.org/
Please let me know if that helps.
Best Regards,
Deepak
Hi Sam,
Thanks for that. Sure, 9PM ET Wednesday 13 Apr works for me. It will be 11 am AEST Thursday.
Please use the below link to join the meeting: https://meetings.ringcentral.com/j/1490630793
And regarding the SQL query, I gave few tries yesterday and was able to fetch the query(Just in case if you need sql urgently)
Step 1: Execute the below query by replacing the ViewDescription - '%ViewName%' with the name of the view.
SELECT rv.ViewId, rv.ViewDescription, rv.ViewStatusCode, rv.SQLText, rv.SQLDocumentId, di.DocumentId, dr.RevisionId, dd.DataChunk
FROM ReportView rv
JOIN DocumentItem di ON rv.SQLDocumentId = di.DocumentId
JOIN DocumentRevision dr ON dr.DocumentId = di.DocumentId
JOIN DocumentData dd ON dd.RevisionId = dr.RevisionId
WHERE rv.ViewStatusCode = 'OPEN' AND
rv.ViewDescription like '%ViewName%'
We may get a result something like this:
Step 2: Copy the DataChunk Column and use any base64 converter to decode it. Then we can fetch the query encoded as below:
I have used this website to decode: https://www.base64decode.org/
Please let me know if that helps.
Best Regards,
Deepak
Sounds good, see you then! If we can't restore the Yellowfin View to be editable, that approach to retrieving the SQL could be really handy.
Sounds good, see you then! If we can't restore the Yellowfin View to be editable, that approach to retrieving the SQL could be really handy.
Hi Sam,
Thanks for taking your time to join the call.
I am glad that we were able to conclude that the view was built on a stored procedure hence we couldn't find in the config db/sql for it. Instead, we had a procedure that is being call. As discussed, we may make changes to the store proc to add a new field or we may create a new view using the details.
Please feel free to reach out if there is anything that we can assist you with.
Best Regards,
Deepak
Hi Sam,
Thanks for taking your time to join the call.
I am glad that we were able to conclude that the view was built on a stored procedure hence we couldn't find in the config db/sql for it. Instead, we had a procedure that is being call. As discussed, we may make changes to the store proc to add a new field or we may create a new view using the details.
Please feel free to reach out if there is anything that we can assist you with.
Best Regards,
Deepak
Replies have been locked on this page!