How to change an SQL command


H2OS comes with a library of sample SQL commands for a variety of purposes.


These commands sometimes require specific adjustments to work in your environment, or to change information such as dates, zip codes, etc.  These are usually easy changes to make.


Example: print mailing labels for customers who had estimates but did not order a job in 2009


In this case, we know that we have an SQL command on file that performs this record selection, but it needs some changes (the year and removal of another selection criteria)


In this procedure, we'll get to the SQL Commands library by selecting the Print ->Mailing Labels screen option.  We could also get to the SQL Commands library by clicking the SQL command. Either way doesn't affect the handling of the library.


The procedure to find and change this command:


On the menu, select Print -> Mailing Labels -> Avery labels




The Avery address label printer screen will appear:




Click Select Records, and the Record Selection Manager screen will appear




Click Select using SQL, and the SQL SELECT command library screen will appear



Since we're interested in customers who had estimates, we can search the library for commands that include the word "estimate" in the command description.


We can do this by clicking the ESTIM command above (a shortcut), or we can type the word "estimate" in the filter entry field.





Now only commands in the library with the word ESTIMATE appear in the grid





I see the command named "I would like to send follow-up mailing out to estimates that did not become jobs from last year in Suffolk County", which was an question asked by an H2OS customer.


In the next screenshot, I've


1. Checked the row

2. Clicked the "Both descriptions and commands" option

3. Adjusted the row height to 124





The changes I'll need to make to this command:


1.  the year needs to be changed from 2007 to 2009 (or 2010, etc)

2. I want all counties, not just 103 (Suffolk NY)


To make changes to a command, switch into ADMIN mode (click ADMIN command).


Note: This "barrier" (having to switch to ADMIN) is not intended to provide security, but some protection of the library from accidental changes.



A message will appear. Click OK




The SQL SELECT command edit screen will appear. You are now in ADMIN mode and can make changes to SQL commands and descriptions using this screen.




We need to find the command we're going to change. Again, I'll search on ESTIMATES


Actually, it's even easier in this case, since there is a command button on this screen to select commands with the word "ESTIM" in the description, so I'll click it.




The screen showing only estimates



Here I've selected (clicked in the SEL column checkbox) the command we want to change (and also adjusted the row high to show the whole command)






Now I over-type the command and description with my changes:





click CLOSE




Returning to the SQL Commands library manager, we see the changed command