ODBC for QuickBase
ODBC for QuickBaseApplication Note for QuNect ODBC for QuickBase
Writing SQL Statements for QuNect ODBC for QuickBase
Make sure to refer to the SQL Syntax application note as well as the application notes on the available date/time, numeric and conversion functions. You should enclose all your column names, column aliases and table names in double quotes. So for instance you would write a simple select statement like this:
SELECT "Column Name" "Column Alias" FROM "Table Name bbsd23jlm"
Table names should end with the DBID of the table as the last word in the table name. You can use just the DBID itself as the table name if you wish. You can use the column names as they appear in Quickbase. However if you do a SELECT * on a table the column names that you'll see in your result set may have all special characters (everything except letters, numbers and spaces) replaced with underscores, depending on the setting in your DSN or connection string. Each column in Quickbase has an alternate name that never changes. The alternate name always starts with the characters fid and finishes with the FID of the field. So you could write a SQL statement like this:
SELECT "fid19" FROM "Table Name bbsd23jlm"
The advantage of using the alternate name is that you can change the name of a field in Quickbase and your SQL statement will continue to work. As long as the field isn't deleted the alternate name will always work. Unfortunately the alternate name is completely useless in telling you what the field is intended for. You have no way of knowing what type of field it is (date, number, text, etc.) or what its purpose is. You can use SQL columns aliases to get around this problem. So if fid19 is the alternate name for a field called Start Date then you would probably want to write your SQL statement like this:
SELECT "fid19" "Start Date", "fid20" "End Date", "fid12" "First Name", "fid14" "Last Name" FROM "Table Name bbsd23jlm"
Joins
QuNect ODBC for QuickBase supports two of the four kinds of joins. The INNER and LEFT OUTER joins. A inner join is where there is a matching value in each joined record. In a left outer join the result set of the join contains at least one record from the table on the "left" even if there are no matching records in the table on the "right". Left and right are determined by the order the tables first appear in the SQL statement. Here are some examples of an inner join. The first example is between just two tables. The second example does a join across three tables using only WHERE clauses. The third example uses the JOIN keyword to report on three tables. By continuing to nest paretheses you can join together as many tables as you like.
SELECT * FROM "Projects bcg9m2wqz" INNER JOIN "Time Log bcg9m2wqw" ON "Projects bcg9m2wqz"."Record ID#" = "Related Project"
SELECT bcg9m2wqt.fid3 FROM bcg9m2wqt, bcg9m2wqv, bcg9m2wqw WHERE bcg9m2wqt.fid3 = bcg9m2wqv.fid52 AND bcg9m2wqv.fid3 = bcg9m2wqw.fid25
SELECT bcg9m2wqt.fid3 FROM bcg9m2wqt INNER JOIN (bcg9m2wqv INNER JOIN bcg9m2wqw ON bcg9m2wqv.fid3 = bcg9m2wqw.fid25) ON bcg9m2wqt.fid3 = bcg9m2wqv.fid52
This example of a left outer join which will show at least one record for every Project even if there are no related Time Log records.
SELECT * FROM "Projects bcg9m2wqz" LEFT OUTER JOIN "Time Log bcg9m2wqw" ON "Projects bcg9m2wqz"."Record ID#" = "Related Project"
Joins with three or more tables requires that you use parentheses. The syntax looks like this assuming table A has a one to many relationship to table B and table B has a one to many relationship with table C:
SELECT * FROM A INNER JOIN (B INNER JOIN C ON B.KeyFieldName = C.ForeignKeyFieldName) ON A.KeyFieldName = B.ForeignKeyFieldName
With four tables A, B, C and D the syntax looks like this:
SELECT * FROM A INNER JOIN (B INNER JOIN (C INNER JOIN D ON C.KeyFieldName = D.ForeignKeyFieldName) ON B.KeyFieldName = C.ForeignKeyFieldName) ON A.KeyFieldName = B.ForeignKeyFieldName
Where Clauses
LIKE can be used in a WHERE clause to do wildcard matching. The following SQL statement will match all names ending in Smith. The percent sign (%) matches any number of characters including no characters. The underscore (_) matches any single character. The match to the percent sign or the underscore character specifically you need to escape them by preceding them with a backslash (\).
SELECT * FROM "People bcggj2wqz" WHERE "Name" LIKE '%Smith'
Date, Date/Time, and Time of Day fields in Quickbase need special attention when used in a WHERE clause. For example to retrieve all the records where the BirthDate field is after July 4th, 1976 you would use a SQL statement like this:
SELECT * FROM "People bcggj2wqz" WHERE "BirthDate" > {d '1976-07-04'}
Now let's get fancy and develop a query that brings back all people born in the last thirty days. We will use the date and time scalar functions available to us in ODBC SQL.
SELECT * FROM "People bcg9m2npn" WHERE {fn CONVERT("BirthDate", SQL_TIMESTAMP)} > {fn TIMESTAMPADD(SQL_TSI_DAY, -30, {fn CURRENT_TIMESTAMP()})}
The built-in fields Date Created and Date Modified are Date/Time fields which are know to QuNect ODBC for QuickBase as timestamp fields. For example to retrieve all the records where the Date Created field is after noon on July 4th, 1976 you would use a SQL statement like this:
SELECT * FROM "People bcggj2wqz" WHERE "Date Created" > {ts '1976-07-04 12:00:00.000'}
Let's assume there is a Time of Day field called "BirthTime". To retrieve all the records where the BirthTime field is after 3PM you would use a SQL statement like this:
SELECT * FROM "People bcggj2wqz" WHERE "BirthTime" > {t '15:00:00'}
Now let's get fancy and develop a query that brings back all records that were created in the past.
SELECT * FROM "People bcg9m2npn" WHERE "Date Created" < {fn CURRENT_TIMESTAMP()}
That wasn't very useful because all records are created in the past. So let's modify that to look for all records created 60 days ago.
SELECT "Date Created" FROM "People bcg9m2wqw" WHERE "Date Created" < {fn TIMESTAMPADD(SQL_TSI_DAY, -60, {fn CURRENT_TIMESTAMP()})}
Now how about presenting a Quickbase Date/Time field (in this case "Date Created") as "Friday, September 25 2008". Here's how using the convert function.
SELECT {fn concat({fn concat({fn concat({fn concat({fn concat
(
{fn dayname("Date Created")},
', '
)
},
{fn monthname("Date Created")}
)
}
,
' '
)
}
,
{fn convert({fn dayofmonth("Date Created")}, SQL_CHAR)}
)
},
{fn
convert
(
{fn year("Date Created")}, SQL_CHAR
)
}
)
}
from bce33vr7t