2 Replies Latest reply on Oct 16, 2013 5:21 AM by Egor Ushakov

    Using modular SQL queries

    Egor Ushakov

      While trying to implement Tableau-based BI for my current MySQL-hosted SugarCRM instance  I faced with necessity of building very long and "hard enough to maintain" SQL queries. For example, to analyze Contacts by type of Accounts I need also join tables storing info for Users, its Roles and Security Group. Yes, it's pretty simple SELECT statement but rather long and, what is most important, not so flexible as I would like. On founding myself typing or copy-pasting SQL snippets again and again I decided to split long queries into shorter ones each of which represent one definite and solid "hierarchy". For example, Account-Contacts, Users-SecurityGroups-Roles, etc. And use that short snippets to create individual data source to reuse it in workbooks with Data Blending.

       

      But as it turned out after reading "Understanding Data Blending" article data blending is not a JOIN substitution. It's just ... blending! And one of the warning message directly advices me to use "Multiple Tables" join. But for me it's really a pain to use "Multiple Join". First of all, I need to use rather small non-resizable window to choose from several tables. Secondly I needed to apply a lot of filters and maintain them which are completely useless and supposed to be maintained just due to database structure feature (I mean "deleted" TINYINT field in every SugarCRM table).

       

      After all, it's really much easier for me to build a query "by hand" and insert  SQL code into "Custom SQL" window than clicking, scrolling, searching, etc. But I would like to make a process of creating SQL query a little bit "modular".

      Can someone suggest any ideas about how to implement combining small SQL chunks in worksheet?

       

      Thank you!