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

by Fred Block on October 15, 2014

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.

SQL Manager Image

The SQL Manager opens:

SQL Manager Tools

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 a 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:

SQL Manager Run Command


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 on 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!

Find similar articles in these categories:

PRODUCT: wMobile

AUDIENCES: Administrators End Users

Fred Block
Application Specialist at W-Systems
More From This Author »