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?
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.
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.
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.
Maybe you can vote up this idea Possibility to load SQL statements in Query Builder Report in Windchill PDMLink
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.
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.