Using a SQL/1010data hybrid

While it is possible to access and query 1010data tables solely with SQL, you can also combine SQL with 1010data Macro Language to get the most out of 1010data's platform.

The following example uses a SQL expression on the table pub.fin.risk_share.stacr.history to calculate conditional prepayment rates. However, it also uses the 1010data group function g_tshift() and the 1010data mortgage amortization function amort(). These functions can be accessed through the SQL engine by using the prefix mdb., for example, mdb.g_tshift() and mdb.amort().

However, if a group function has more than one first argument, as is the case with g_first1(), you are required to "wrap" the computation in a COMPUTE() function, as was done in this line of the code: COMPUTE( BOOLEAN , $$ g_first1("id_loan" "is_paidoff";;"fmonth") $$) is True ).

<code language_="sql" options_="{importwithlabels:0}">
<![CDATA[
SELECT F.deal,F.fmonth,round((1-(1-F.vol_smm)^12)*100,1) AS vol_cpr, 
ROUND((1-(1-F.invol_smm)^12)*100,1) AS invol_cpr,
ROUND((1-(1-(F.invol_smm+F.vol_smm))^12)*100,1) AS cpr 
FROM (SELECT E.*,(E.curtailment+E.vol_prepay)/E.schedbal AS vol_smm,
E.invol_prepay/E.schedbal AS invol_smm 
FROM (SELECT D.deal,D.fmonth,SUM(D.schedbal) AS schedbal,
SUM(D.curtailment) AS curtailment, SUM(D.vol_prepay) AS vol_prepay ,
SUM(D.invol_prepay) AS invol_prepay 
FROM (SELECT C.*,CAST((payoff_reason is NULL) AS INTEGER) * greatest(delta,0.0) 
AS curtailment, 
CAST(payoff_reason = 1 AS INTEGER)*upb_at_removal AS vol_prepay , 
CAST(payoff_reason != 1 AS INTEGER),upb_at_removal AS invol_prepay 
FROM (SELECT B.*,mdb.g_tshift(id_loan,1,fmonth,'M',schedbal_next,-1) AS schedbal, 
schedbal-act_endg_upb AS delta 
FROM (SELECT A.*,mdb.amort(new_int_rt,mths_remng,1)*act_endg_upb AS schedbal_next 
FROM (SELECT *,substr(id_loan,1,6) AS deal,
NOT coalesce(payoff_reason, NULL) AS is_paidoff 
FROM "pub.fin.risk_share.stacr.history") AS A 
WHERE payoff_reason IS NULL OR 
COMPUTE( BOOLEAN , $$ g_first1("id_loan" "is_paidoff";;"fmonth") $$) is True ) 
AS B) AS C WHERE schedbal IS NOT NULL) 
AS D GROUP BY D.deal,D.fmonth) 
AS E WHERE E.schedbal>0) AS F
]]>
</code>