cancel
Showing results for 
Search instead for 
Did you mean: 
cancel
Showing results for 
Search instead for 
Did you mean: 

"View as SQL" disabled under query builder

Highlighted
akr
Level 1

"View as SQL" disabled under query builder


Hello All,

I need to generate SQL statements for some existing QML's. For some of QML reports "View as SQL" option is disabled. "View as SQL" is working for OOTB QML files and few custom QML's where limited # of tables used in join.

Is there any constraint on query size to generat SQL statements?

Thanks,

Ashwini

6 REPLIES 6

Re: "View as SQL" disabled under query builder

I generally see this after introducing soft types into the query. Is that what is happening in your situation? There are queries that the UI tool can handle, but SQL cannot. Another example is querying user information. Some is stored in the DB, but most is stored in the LDAP. So the UI tool can handle that interaction whereas straight SQL cannot.

Re: "View as SQL" disabled under query builder

Thanks for your reponse Ben. Yes I have used soft types in the query.. Is there any alternative to generate SQL command for this type of QML's.

Re: "View as SQL" disabled under query builder

I don't think there is a work-around. I have asked PTC tech support a few times. It is just not possible generally, for multiple reasons.

Re: "View as SQL" disabled under query builder

Re: "View as SQL" disabled under query builder

I found SPR 1758333, which was opened for this same issue. According to the SPR notes, this is intended functionality because soft type queries do not have a direct SQL statement, so the option is disabled.

Re: "View as SQL" disabled under query builder

As a last resort you can always enable wt.pom.stacktrace logging to get the SQL queries run when a report is executed. However, this will output A LOT of query information and is cumbersome to go through. A possible reason for the SQL query not being generated is due to multiple queries having to be run and potential Info*Engine tasks having to be executed in order to get the data for the report.