Support (800) 775-8378 Contact Us

    Tutorial: Using SQL Queries in wMobile Desktop (Part 1)

    By Fred Block • October 15th, 2014
    Audiences: Administrators, End Users

    wMobile has some excellent built in searching tools allowing you to quickly access your data. There are other times we need to sift through our data using very specific filtering. You may want to see if certain data exists; or does not exist or maybe you need a list of which customers are in a certain state (or multiple states) and even sort the data how you need to view it.

    Enter SQL (pronounced "ess-cue-ell" or "sequel" depending on the source).

    The information and examples presented here will show you how to run an SQL query in wMobile Desktop and will be quite "basic" as this is for you to get your feet wet and is not meant to be a full blown tutorial.

    If SQL appears to be something you find potentially valuable, there is a lot of free information on the web for the SQL Query language. SQL is short for Structured Query Language. Don't let that scare you because this might be just the ticket you need to get your data exactly how you want it.

    In a nutshell, SQL is a language that includes elements you use to build queries. Once written, a query is executed against your database and then returns data to you, your way. Your query can be very simple (listing all the contacts in your database) or very complicated (returning only contacts in your database created after a specific date you specify, these contacts do not have a primary email address assigned, and they also must have a specific Account Manager).

    To wrap up what SQL is, it's a way of communicating with the database. Our query asks for data and then the database gives it to us.

     



    Let's begin with the interface for writing and using SQL queries, the SQL Manager. In wMobile Desktop, go to Tools > Sql Manager.

    The SQL Manager opens:

    SQL Manager Tools:

    • Clear - Clears the query editing area.
    • Run - Runs the query contained in the editing area.
    • Save - Allows you to save (or update) the current query.
    • Export - Allows the results returned from a query to be saved as a CSV or Excel file.
    • User (drop-down) - List of GoldMine users.
    • Query (drop-down) - List of saved queries for the user selected in "User" drop-down.

    NOTE: There is also as set of font formatting tools to use as needed but please note that font formatting has no effect on SQL queries.


    To use the SQL Manager, type your query in the editor area and click the Run command. Your results will display in the area below the query editing area:


    Before we can start writing our example queries we need to cover the very basics of SQL. We'll start with a table because the data you want from the database is stored in a table. A database may contain many tables and each table within a database is uniquely identified by name. Each table is made up of columns and rows. Columns contain the column name (also the data type, and any other attributes for the column). The rows contain the records or data contained within the columns. Here is scaled down version of GoldMine's CONTACT1 table where the "main" contact information is stored. We're showing only four of its many columns and four rows of data (the first row contains column names):

    CONTACT1 [this is the table name]

    Contact

    City

    State

    Phone1

    Solange Shinko

    Metairie

    LA

    504-979-9175

    Jose Stockham

    New York

    NY

    212-675-8570

    Rozella Ostrosky

    Camarillo

    CA

    805-832-6163

    Brock Bolognia

    New York

    NY

    212-402-9216


    For this blog entry, we will write simple queries for "selecting" data. Let's get into the SQL language elements now.

    The first will be based off this example table and get us a list of all contacts in a specific state.

    The SELECT statement:

    select * from contact1


    In the query above the "*" is used to return "all" columns in the table. The table name that follows the keyword "from" specifies the table that will be queried to retrieve the desired results from.

    Column names that follow the "select" keyword determine which columns will be returned in the results. To get the table example above, we would need the query to read this way (listing the four columns separated by commas):

    select contact, city, state, phone1 from contact1


    The two prior query examples will return "all" of the contacts records in the CONTACT1 table because we are not filtering the data. The "where" clause is used to filter (or limit) the data that will be returned and its use is optional. When used in a query, criteria is placed after the "where" clause and will determine which rows will be returned. Please note that the state must be enclosed in single quotes:

    select contact, city, state, phone1 from contact1

    where state = 'NY'



    Adding the "where" clause and the "state = 'NY'" criteria, running the query would return this data:

    CONTACT1

    Contact

    City

    State

    Phone1

    Jose Stockham

    New York

    NY

    212-675-8570

    Brock Bolognia

    New York

    NY

    212-402-9216


    In our example we used the "=" condition. Here are the conditions you may use:

    Symbol

    Meaning

    =      

    Is equal to

    <> 

    Does not equal

    Is less then

    Is greater than

    <=

    Is less than or equal to

    >=

    Is greater than or equal to

    !<

    Is not less than

    !>

    Is not greater than


    The "like" pattern matching operator can also be used in the conditional selection of the "where" clause. Like is a very powerful operator that allows you to select only rows that are "like" what you specify. The percent sign "%" can be used as a wildcard to match any possible character and any number of characters that might appear before or after the characters specified. The search string must be enclosed in single quotes. For example:

    select contact, city, state, phone1 from contact1

    where contact like 'St%'


    The results would include all rows where the contact field has a value beginning with "St" and ending with any number of any other characters (only the "contact" column shown here from results):

    Stephaine Barfield

    Stephaine Vinning

    Stephen Emigh

    Stevie Westerbeck

    Staci Schmaltz

    Stephane Myricks


    Another example using the wildcard is to search for a string "within" a field (i.e., contains):

    select contact, city, state, phone1 from contact1

    where contact like '%red%'


    The results would include all rows where the contact field has a value containing "red" anywhere within the field (only the "contact" column shown here from results):

    Helga Fredicks

    Frederica Blunk

    Rhea Aredondo

    Winfred Brucato

    Lastly, please note that the wildcard may be used to search for a field "ending" with a specific string (i.e., ends with):

    select contact, city, state, phone1 from contact1

    where contact like '%m'


    (Only the "contact" column shown here from results):

    Gladys Rim

    Jose Stockham

    Lizette Stem

    Britt Galam


    Of course you can negate the "like" pattern by using "not like". Here is an example of this usage:

    select contact, city, state, phone1 from contact1

    where state not like 'NY'


    These results are from our example table above:

    CONTACT1

    Contact

    City

    State

    Phone1

    Solange Shinko

    Jefferson

    LA

    504-979-9175

    Rozella Ostrosky

    Ventura

    CA

    805-832-6163


    Here is one more extremely useful SQL query tool, the "in" and "not in" operators. The "in" operator allows you to specify multiple values in a "where" clause. NOTE: The query is looking for exact matches for the values you enter in the parentheses. The search values must be enclosed in single quotes and separated by commas as shown:

    select contact, city, state, phone1 from contact1

    where state in ('NY','CA')


    This query would return only the rows from the table named where the state equals NY or CA. You may also use this with the "not in" operator to return only the rows where the state value does not equal NY or CA.

    One more important feature is having the ability to sort the results you are after. Returning a bunch of data is great, but far better if it's organized. For this we can utilize the "order by" clause.

    "Order by" will allow you to sort on a per column basis and you may sort on one or more columns in a single query. To sort on a column, that column must be in your select statement. If you used the "*", no problem, all fields are available to sort on. To add to this, each of the columns you wish to sort on can be sorted independently in ascending or descending order. Now let's get to examples:

    select contact, city, state, phone1 from contact1

    where state in ('NY')

    order by contact asc


    Ascending (asc) order is the default and does not need to be specified in the query. If you'd like to use it for readability you may leave it in as shown above (i.e., order by contact will work the same).

    (Only the "contact" column shown here from results):

    Brock Bolognia

    Celeste Korando

    Ciara Ventura

    Cyril Daufeldt

    Dean Ketelsen

    Derick Dhamer

    Elli Mclaird

    Fausto Agramonte


    To use descending (desc) order, the "desc" must be used to avoid the default:

    select contact, city, state, phone1 from contact1

    where state in ('NY')

    order by contact desc


    (Only the "contact" column shown here from results):

    Fausto Agramonte

    Elli Mclaird

    Derick Dhamer

    Dean Ketelsen

    Cyril Daufeldt

    Ciara Ventura

    Celeste Korando

    Brock Bolognia


    The next query contains two sorts: the "primary" or first sort is by "city" in ascending order, and then "secondary" sort is by "contact" in descending order. The order of the sort columns dictates the sorting priority:

    select contact, city, state, phone1 from contact1

    where state in ('CA')

    order by city, contact desc

     

    CONTACT1

    Contact

    City

    State

    Phone1

    Tarra Nachor

    San Francisco

    CA

    415-411-1775

    Stephaine Vinning

    San Francisco

    CA

    415-767-6596

    Norah Waymire

    San Francisco

    CA

    415-306-7897

    Kallie Blackwood

    San Francisco

    CA

    415-315-2761

    Cory Gibes

    San Gabriel

    CA

    626-572-1096

    Veronika Inouye

    San Jose

    CA

    408-540-1785

    Leota Dilliard

    San Jose

    CA

    408-752-3500

    Elvera Benimadho

    San Jose

    CA

    408-703-8505

    Aliza Baltimore

    San Jose

    CA

    408-504-3552


    In these results, the city values are sorted first and are output in ascending order (A-Z). The contacts (secondary sort) are in descending order but are grouped by contacts in the same city.
     



    The information and the examples provided in this blog only scratch the surface of the power of SQL queries and we hope that you'll find this interesting enough to look further. With SQL, there are also ways to join different tables together, to group data, to search for minimum and maximum values of numbers or dates, to search on date ranges, and so much more.

    Happy SQL querying!

    More From This Author
    Fred Block
    Application Specialist at UpCurve Cloud