I'm a newbie and I guess if it's possibile to retrieve which sql instructions does a report on SBM.
I'm not so practice with SQL and so I need, instead of using advanced SQL option to create a report, to create a Report with all fields I need and then to see which SQL instructions this report uses.
This SQL instruction later will need to creat a specific view on DB Sql, so we'll use this view instead of report.
Thanks in advance.
Accepted AnswerEmployeeGarry WomackOnline0Hello Simone. Using SQL as a newbie can be a daunting task. It would probably be easier to just create the reports within SBM and share links to the reports to those who need to see the data. Links can be shared to enable users to run the report dynamically or you can schedule reports which contain a snapshot of the data.
If you really wish to pursue the direct SQL approach, I recommend visiting the links below:
SBM Database Schema Guide - http://help.serena.com/doc_center/sbm/ver11_5/sbm_schema_doc.pdf
This doc provides information on the SBM database schema.
You may also want to visit sites on SQL itself such as https://www.w3schools.com/sql/
Combining the information above with your knowledge of the process apps (maintained in SBM Composer) you can achieve your goal.
Accepted AnswerPaul ThompsonOnline0One trick I use is to select the "Advanced SQL" option in "Search Filter" then enter something that will to cause the query to fail. For example, "@where blah". This gets passed directly to the database server. Since it is not valid SQL, the report fails and it prints a message "an error occurred while processing the last request". The Windows Application Event log on the database server will contain an error which shows the full SQL for the report, including my defective "where blah" clause.
For reports where I am using "Basic Conditions" in the filter, I will add a "Join" to the CONTACTS table, select "This related item filters the report results", then enter a clause like "Last Name contains ' " (i.e. a single quote).
Accepted Answer0Hi Paul,
that's a great trick !!!
I tried your first example and I got some query instructions in Event Log.
I put these instructions in Sql Developer but I got a query that doesn't give me the result I expect.
That's probably because using advanced Sql option all Basic Condition I've put in the report are ignored, isn't it?
So I tried with your second example, in which, if I've understood, you use an existing report and you want to extract what that report does exactly in Sql mode.
I added a Join (but I've not found CONTACTS in my selections, I've tried with some of our entries) and I put something like ' quote in the blank field.
In effect it cause a message error but something like "the string is not accepted" and in this case no sql instruction is printed in the event log.
So in my case the perfect soluction would be to get the exact SQL version of my existing Report to extract the column and the filter I've put in my selection but in SQL mode.
Why in the second case Join option doesn't give back in the log the SQL string even if the Report error occurred?
Thanks for your replies
Accepted AnswerDavid SheaffeOnline0Another option is to use SQL Profiler. If you run your existing report and check profiler and it will spell out the "where" clause, but won't show the column details that you have selected to be included in the report.
Note that Profiler will generate 100's or rows so it is a matter of scolling through them to find the row when the report was run.