With v9.2 this is still the same problem. BRApi.Database.ExecuteSql enforces constraints on the resulting table even it doesn't make sense e.g. for reporting where no constraints should apply (grouping data etc.) or examples as above.
If the SQL is a bit more complicated and should have dynamic parts then the above workaround via an adapter isn't working.
Using XFBR to dynamically get the whole SQL into the adapter also doesn't work as it is limited to 128 characters.
Another workaround is to write the SQL as if BRApi.Database.ExecuteSql was used but then pass it via the dictionary to the adapter e.g.
sql.appendline("...sql...")
Dim dctVars As New Dictionary(Of String, String)
dctVars.Add("SQL", sql.ToString)
Using ds As DataSet = brapi.Dashboards.Process.GetAdoDataSetForAdapter(si, False, New PVMParameters().GetPVMWorkspaceID(si), "test", "ResultsTable", dctVars)
If ds.Tables.Count > 0 Then
Using dt As DataTable = ds.Tables(0)
Dim clonedDt As DataTable = dt.Copy()
Return clonedDt
End Using
End If
End Using
In the adapter then add just this
|!SQL!|
Another workaround is to take all needed data into the memory and do all work (sql server would to better) with BRs
