Did you know that you can easily access the SQL running behind your Hubble® Reports? Some reasons that you might want to include:
- A request from your Business Analyst to see the SQL
- Why a result-set isn’t returning as expected
- Why performance is slow
- Quick way to capture code for use in a Virtual View.
More on Virtual Views: Along with gaining access to reporting over control and security tables, quicker performance by joining two tables directly, creating Unions across modules, and Julian Date conversions, virtual views are something you can create on-the-fly, as opposed to the necessity for a dba to create a database view for you.
How about an example, where an automatically generated SQL statement could come in handy?:
You are joining the Account Ledger (F0911) to Work Order Time Transactions (F31122), and the key fields don’t match. You’d like to join from a subledger field (GLSBL) which includes a subledger type to a document number (WTDOCO) which does not have a subledger type. You can write the SQL statement for that one, or you can get a little help by having Hubble® create it for you. Simply create a report over the Account Ledger table and use the substring function in a calculation to trim the subledger type out of GLSBL field. Include only the label columns and amounts you’ll be using in your finished report. Now you have the SQL statement for your Virtual View, and you’ll join the trimmed/custom column to WTDOCO.
How to turn on the View SQL icon:
At the top of your Hubble® screen is a drop-down menu to Customize Quick Access Toolbar. Select it and then select More Commands:
At the bottom of the list in the left pane is your View SQL and icon, a pair of sunglasses. Highlight it and select the Add button, located between the left and right pane, so that View SQL is now in the right pane.
After you select OK, you now have the View SQL icon available in your Quick Access Tool Bar:
Selecting the sunglasses, a dialog will open displaying the SQL statement:
(Actual SQL was intentionally blurred in example above)