Touchstone 4.0 Database Documentation
SQL Query Sample

The AIRResult - Loss database has significantly reduced the amount of SQL code needed to retrieve commonly queried information. For example, the following sample compares a query written for the CLASIC/2 Loss database against the same query written for the AIRResult - Loss database. The Touchstone version of the query does not require ObjectTypes or WHERE clauses. Also, since the Dimension tables make key exposure attributes available within the AIRResult - Loss database, information about the exposures that incurred losses can be retrieved without querying the original exposure database.

CLASIC/2 Query
SELECT lob.strName AS 'UserLineOfBusiness', strL1Name AS 'CountryCode', strL2Name AS 'AreaName', strL3Name AS 'SubareaName', SUM(dblLoss1) / 10000 AS 'AAL_GU',  SUM(dblLossGR) / 10000 AS 'AAL_GR'
FROM AirCL2Loss_MyData.dbo.TblResultLossData_561D212AD606D6498326A08C34A841B1 AS loss
INNER JOIN AirAreaCode.dbo.TblAreaExternal AS geo ON loss.guidObject = geo.guidExternal
INNER JOIN AirCL2Exp_MyData.dbo.TblContract AS contract ON loss.guidParentObject = contract.guidContract
INNER JOIN AirCL2Exp_MyData.dbo.TblLineOfBusiness AS lob ON contract.guidUserLob = lob.guidLob
WHERE intParentObjectType = 5 AND intObjectType = 10
GROUP BY lob.strName, strL1Name, strL2Name, strL3Name
ORDER BY lob.strName, strL1Name, strL2Name, strL3Name
Touchstone Query
SELECT UserLineOfBusiness, CountryCode, AreaName, SubareaName
, SUM(GroundUpLoss) / 10000 AS 'AAL_GU'
, SUM(GrossLoss) / 10000 AS 'AAL_GR'
FROM AIRResult_MyData.dbo.t1234_LOSS_ByContractGeo AS loss
INNER JOIN AIRResult_MyData.dbo.t1234_LOSS_DimContract AS contract
ON loss.ExposureDataSourceSID = contract.ExposureDataSourceSID
AND loss.ContractSID = contract.ContractSID
INNER JOIN AIRGeography.dbo.tGeography AS geo ON loss.GeographySID = geo.GeographySID
WHERE CatalogTypeCode = 'STC'
GROUP BY UserLineOfBusiness, CountryCode, AreaName, SubareaName
ORDER BY UserLineOfBusiness, CountryCode, AreaName, SubareaName



AIR Client Confidential

© 2016. AIR Worldwide is a registered trademark. Touchstone is a registered trademark of AIR Worldwide.

Please contact AIR Worldwide with questions or comments. For database questions. For documentation comments.