|
SQL Scripts Library |
|
|
|
caplen's aim Architect
contains the Query Manager, a ANSI SQL compliant query engine, which provides tools to perform
simple-to-complex queries and data mining tasks to locate information in
meaningful ways. |
|
|
|
NOTE: The content of the
SQL Scripts Library is dynamic ... containing tutorial examples as well as
specialized Scripts to perform complex tasks ... with additional SQL Scripts added as they
are developed by our Customer Support Group, or
submitted by aim
Users. |
|
|
|
SQL Task |
|
Syntax, Example and Description of SQL Script |
|
Select
all records in a Table. |
syntax
example
description |
SELECT
* FROM tablename
SELECT
* FROM aimMainItem
This SQL
statement demonstrates how to select all records from any data
table. Simply replace tablename with the name of the
desired data table. |
|
Select
records based on the content of a specific data field. |
syntax
example
description |
SELECT
* FROM tablename WHERE DataField
= 'content'
SELECT
* FROM aimMainItem WHERE BreedGroup
= 'Equine'
This SQL
statement demonstrates how to select records based on the content of a
specific data field. In this example, any records in the
aimMainItem table that contain Equine in the BreedGroup
data field will be displayed. |
|
Perform a
Full Text Search |
syntax
example
description |
SELECT
* FROM tablename WHERE CONTAINS ( DataField,
''content'
)
SELECT
* FROM aimCalendar WHERE CONTAINS ( Memo,
'DHLPP' )
This SQL
statement demonstrates how to perform a full text search. The
search can be performed on any data field, but is typically used to
search MEMO fields for embedded tokens of information. In
this example, any records in the aimCalendar table that contain
DHLPP in the Memo data field will be displayed. |
|
Join two
or more data tables to create a single result set. |
syntax
example
description |
SELECT
tablename1.fieldname, tablename2.fieldname
FROM
tablename1, tablename2
WHERE
tablename1.recordnumber = tablename2.foreignkeyvalue
SELECT
aimMainItem.RegName, aimCalendar.StartDate, aimCalendar.Caption,
aimCalendar.Category, aimCalendar.SubCategory, aimCalendar.Location
FROM
aimMainItem, aimCalendar
WHERE
( aimMainItem.ItemID = aimCalendar.LinkID )
This SQL
statement demonstrates how to JOIN several data tables into a single
result set. In this example, any animals that have Calendar
records will be displayed.
NOTE:
The above SQL statement can be extended to further refine the result set
to a specific data range. The following example takes the
above SQL statement and adds a date range so that animals that have
Calendar records within the date range will be displayed.
SELECT
aimMainItem.RegName, aimCalendar.StartDate, aimCalendar.Caption,
aimCalendar.Category, aimCalendar.SubCategory, aimCalendar.Location
FROM
aimMainItem, aimCalendar
WHERE
( aimMainItem.ItemID = aimCalendar.LinkID )
AND
( aimCalendar.StartDate >= '1/1/2004' )
AND
( aimCalendar.StartDate <= '12/31/2004' ) |
|
|
|
|
SQL Scripts Submitted by aim
Users ... |
|
|
|
The following SQL Scripts have
been submitted by aim Users. They have been checked by our
Customer Support Group. To use, take the following steps:
-
Copy (
CTRL-C
) the desired SQL Script.
-
Paste (
CTRL-V
) the SQL Script to the Script Editor window in the aim Architect
- Query Manager.
-
Modify the Script, as desired.
-
Run the Script.
|
|
|
|
SQL Task |
Description and SQL Syntax |
|
List of
Acquisitions
This Script joins two tables:
-
aimMainItem
-
aimAcquisition
|
Submitted by :
WNC, Asheville, North Carolina
USA
This SQL
Script will construct a list of acquisitions for a specified data range.
Note that this SQL Script includes the aimAcquisition.USDA and
aimAcquisition.AZA data
fields. These data fields were added to the Acquisition form using the
aim Architect. If you do not use these data fields, simply
remove the declarations from the SQL Script.
SELECT
aimAcquisition.Date, aimAcquisition.Category, aimAcquisition.SubCategory,
aimMainItem.UserDefinedID, aimMainItem.RegNumber,
aimAcquisition.DisplayName, aimAcquisition.Company,
aimAcquisition.AddrOne, aimAcquisition.City, aimAcquisition.State,
aimAcquisition.Postal, aimAcquisition.PhoneOne, aimAcquisition.USDA,
aimAcquisition.AZA
FROM aimAcquisition, aimMainItem
WHERE ( aimMainItem.ItemID = aimAcquisition.LinkID )
AND ( aimAcquisition.Date >= '1/1/2006' )
AND ( aimAcquisition.Date <= '12/31/2006' ) |
|
|
|
|
List of
Dispositions
This Script joins two tables:
-
aimMainItem
-
aimDisposition
|
Submitted by :
WNC, Asheville, North Carolina
USA
This SQL
Script will construct a list of dispositions for a specified data range.
Note that this SQL Script includes the aimDisposition.USDA and aimDisposition.AZA data
fields. These data fields were added to the Disposition form using the
aim Architect. If you do not use these data fields, simply
remove the declarations from the SQL Script.
SELECT
aimDisposition.Date, aimDisposition.Category, aimDisposition.SubCategory,
aimMainItem.UserDefinedID, aimMainItem.RegNumber,
aimDisposition.DisplayName, aimDisposition.Company,
aimDisposition.AddrOne, aimDisposition.City, aimDisposition.State,
aimDisposition.Postal, aimDisposition.PhoneOne, aimDisposition.USDA, aimDisposition.AZA
FROM aimDisposition, aimMainItem
WHERE ( aimMainItem.ItemID = aimDisposition.LinkID )
AND ( aimDisposition.Date >= '1/1/2006' )
AND ( aimDisposition.Date <= '12/31/2006' ) |
|
|
|
|
|
