Can't see or edit SQL for just one of my Views

Sam shared this question 4 months ago
Answered

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:

/94e2fb2c8566565a4b3cb181c157f8a6

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:

/8dffbc4a6bc80f1f39dca8e7f5652cbb

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

Comments (5)

photo
1

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

photo
1

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

photo
1

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:

/2f30f901c4a21254c756e824aed0623f


Step 2: Copy the DataChunk Column and use any base64 converter to decode it. Then we can fetch the query encoded as below:


/d264c2edf903846caf319b78dfdffa2e


I have used this website to decode: https://www.base64decode.org/


Please let me know if that helps.


Best Regards,

Deepak

photo
1

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.

photo
1

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