1 min read

Making qai.dbo.TREInfo Usable in QA Direct IBES v2

Featured Image

You may have seen our post about TREInfo. This post is about making it a little more usable.

The code within is in SQL. Why? SQL isn't "bad" it's misused often. Doing simple shaping operations, closest to the data, which don't tax the database engine, are ideal. These views developed by Advanti do just that.

You can download all open source code from our GitHub repo here: https://github.com/Advanti/qad

Well, let's start by looking at the code, here you go:


select
TREInfo.*,
upper(default_currency.Description) as DefCurrCode,
upper(default_per_share_currency.Description) as DefPerCurrCode,
upper(default_price_target_currency.Description) as DefTPCurrCode,
upper(expected_reporting_currency.Description) as ExpCurrCode,
upper(measure.Description) as PrefMeasureCode
from qai.dbo.TREInfo
inner join qai.dbo.TRECode as default_currency
on TREInfo.DefCurrPermID = default_currency.Code
and default_currency.CodeType = 7
inner join qai.dbo.TRECode as default_per_share_currency
on TREInfo.DefPerCurrPermID = default_per_share_currency.Code
and default_per_share_currency.CodeType = 7
inner join qai.dbo.TRECode as default_price_target_currency
on TREInfo.DefTPCurrPermID = default_price_target_currency.Code
and default_price_target_currency.CodeType = 7
inner join qai.dbo.TRECode as expected_reporting_currency
on TREInfo.ExpCurrPermID = expected_reporting_currency.Code
and expected_reporting_currency.CodeType = 7
inner join qai.dbo.TRECode as measure
on TREInfo.PrefMeasure = measure.Code
and measure.CodeType = 4
;

This case, it's pretty simple, you make this data usable, by simply joining to their "code" tables, which are a classic "map table" database design pattern, whereby you need to know in advance (try reading the PDF) the predicate to apply to the SQL.

There are cases where the ISO codes are not all capitalized, so we apply a little transformation to make it all upper case.

Yup, this one is brainless simple, if you wanted to get fancy, then you could start creating time intervals for each of the identifier fields, so that you know when a given OrgPermID entered the coverage universe and establish a hierarchy of relationships.