SageCRMFun.com

We love SQL. We love reading it, writing it, proofing it, improving it and sometimes dropping it (always sad). We also like to see our SQL dressed to impress- not looking flabby but lean and mean. One tool we use to buff and shine our SQL is "Instant SQL Formatter" (ISF). ISF, a web site created by Gudu Software, takes ugly SQL and makes it pretty. Why is this important? Frankly, it makes debugging and modifying existing SQL statements easier – and we like easy. Let’s see an example.

"It’s not that it’s ugly… it’s just not pretty"

Below we have "vSearchListCompany". This is a popular SQL view – used in a variety of Sage CRM functions. Consequently, this would be the same view that seemly breaks when applying patches – forcing one to limit the amount of fields returned in order to fix the error. But we can save that post for another day – back to "vSearchListCompany". As we see (screen shot) not all SQL statements are easy on the eyes. Attempting to add a column, modify the first name and last name concatenation, or removing a column can get messy. To make this a little easier, let’s use ISF.

"We are going to PUMP…. You Up!"

Now, at first visit, ISF can seem a little disorganized – offering a plethora of options. Side note: We use ISF’s impressive array of options for more than beautifying SQL. ISF can be a huge time saver for creating JScript or C# string variables based on your SQL statements -  I implore you to explore the settings. For this example, we are only concerned with two settings: Database and Output. These two settings inform ISF what database platform the SQL statement is coming from and what format to output the results. For this example, our database format is "SQL" and our output format is "SQL (Text)". Selecting "SQL (Text)" as the output format will deliver a SQL statement – executable in SQL Query Analyzer. After selecting the appropriate settings, take your SQL statement, copy and paste it into the provided text box and click "Format SQL".

"Looking SWEEEEET!"

After a few milliseconds, our new and improved SQL is unveiled. We can see (below) the resulting SQL is much easier to work with. Adding or modifying existing columns becomes a cinch. Additionally, the SELECTION fields along with the JOIN and WHERE clauses are easier to disseminate. From here, we typically copy the resulting SQL statement into SQL Query Analyzer and work with it from there. Also, it is not uncommon for us to keep the formatting provided by ISF when pasting BACK into Sage CRM. This way, if we need to make changes in the future, we are ready to go!

/* Powered by General SQL Parser (www.sqlparser.com) */

CREATE VIEW VSEARCHLISTCOMPANY
AS
  SELECT RTRIM(ISNULL(PERS_FIRSTNAME,'')) + ' ' + RTRIM(ISNULL(PERS_LASTNAME,''))                                                           AS PERS_FULLNAME,
         RTRIM(ISNULL(PERS_PHONECOUNTRYCODE,'')) + ' ' + RTRIM(ISNULL(PERS_PHONEAREACODE,'')) + ' ' + RTRIM(ISNULL(PERS_PHONENUMBER,''))    AS PERS_PHONEFULLNUMBER,
         RTRIM(ISNULL(PERS_FAXCOUNTRYCODE,'')) + ' ' + RTRIM(ISNULL(PERS_FAXAREACODE,'')) + ' ' + RTRIM(ISNULL(PERS_FAXNUMBER,''))          AS PERS_FAXFULLNUMBER,
         RTRIM(ISNULL(COMP_PHONECOUNTRYCODE,'')) + ' ' + RTRIM(ISNULL(COMP_PHONEAREACODE,'')) + ' ' + RTRIM(ISNULL(COMP_PHONENUMBER,''))    AS COMP_PHONEFULLNUMBER,
         COMPANY.*,
         PERSON.*,
         ADDRESS.*
  FROM   COMPANY
         LEFT JOIN PERSON
           ON COMP_PRIMARYPERSONID = PERS_PERSONID
         LEFT JOIN ADDRESS
           ON COMP_PRIMARYADDRESSID = ADDR_ADDRESSID
  WHERE  COMP_DELETED IS NULL