Can't open file: './insider/JMTrans_Types.frm' (errno: 24)
SELECT
I.SECPath, /* 1 */
I.xFileName, /* 2 */
I.filer_name, /* 3 */
I.II_title, /* 4 */
IF ((NOT I.OptionsFlag = '' AND I.OptionsFlag IS NOT NULL), I.OptionsFlag, I.JMTrans_Type) as JMTrans_Type, /* 5 */
I.dollar_value, /* 6 */
I.trans_amount, /* 7 */
I.trans_date_from, /* 8 */
I.trans_date_to, /* 9 */
I.trans_price_from, /* 10 */
I.trans_price_to, /* 11 */
I.amount_owned, /* 12 */
#insider.udf_GetDeltaOwn(IF ((NOT OptionsFlag = '' AND OptionsFlag IS NOT NULL), OptionsFlag, JMTrans_Type), CAST(amount_owned as DECIMAL), CAST(trans_amount as DECIMAL)) as delta_owned,
I.delta as delta_owned,
I.owned_type, /* 14 */
I.formType, /* 15 */
I.entered_date, /* 16 */
insider.udf_OtherInfo(CAST(I.entered_date AS CHAR), CAST(I.formType AS CHAR)) as other_info, /* 17 */
('1/1/1900') as upd_date, /* 18 */
I.UID, /* 19 */
J.Order as JMOrder, /* 20 */
I.SIC_Code as SIC_Code, /* 21 */
S.SIC_Sector, /* 22 */
S.SIC_Industry, /* 23 */
S.SIC_SubIndustry, /* 24 */
I.isRestricted,
I.is10b51,
I.OwnerCIK,
I.IssuerCIK,
CONCAT(I.SECPath, I.xFileName) as FullSECPath,
I.ru_id
,
(
CASE WHEN
(
(I.JMTrans_Type = 'B' AND I.OptionsFlag = '')
OR (I.JMTrans_Type = 'AB' AND I.OptionsFlag = '')
OR (I.JMTrans_Type = 'JB*' AND I.OptionsFlag = '')
OR (I.JMTrans_Type = 'OE*' AND I.OptionsFlag = '')
OR (I.JMTrans_Type = 'OB' AND I.OptionsFlag = '')
OR I.OptionsFlag = 'B'
OR I.OptionsFlag = 'AB'
OR I.OptionsFlag = 'JB*'
OR I.OptionsFlag = 'OE*'
OR I.OptionsFlag = 'OB'
)
THEN
CASE WHEN sa.ps_ed_1y_n IS NULL OR sa.ps_ed_1y_n = 0 OR 365 > 366 THEN
CASE WHEN sa.ps_ed_9m_n IS NULL OR sa.ps_ed_9m_n = 0 OR 270 > 366 THEN
CASE WHEN sa.ps_ed_6m_n IS NULL OR sa.ps_ed_6m_n = 0 OR 180 > 366 THEN
CASE WHEN sa.ps_ed_3m_n IS NULL OR sa.ps_ed_3m_n = 0 OR 90 > 366 THEN
CASE WHEN sa.ps_ed_1m_n IS NULL OR sa.ps_ed_1m_n = 0 OR 30 > 366 THEN
CASE WHEN sa.ps_ed_1w_n IS NULL OR sa.ps_ed_1w_n = 0 OR 7 > 366 THEN
CONCAT('--|--|--|--|', sa.company_count)
ELSE
-- CASE WHEN 7 > 366 THEN
-- CONCAT('--|--|--|--|', sa.company_count)
-- ELSE
CONCAT('1w', '|', sa.ps_ed_1w_n, '|', sa.ps_ed_1w_hit, '|', sa.ps_ed_1w_mean, '|', sa.company_count)
-- END
END
ELSE
-- CASE WHEN 30 > 366 THEN
-- CONCAT('--|--|--|--|', sa.company_count)
-- ELSE
CONCAT('1m', '|', sa.ps_ed_1m_n, '|', sa.ps_ed_1m_hit, '|', sa.ps_ed_1m_mean, '|', sa.company_count)
-- END
END
ELSE
-- CASE WHEN 90 > 366 THEN
-- CONCAT('--|--|--|--|', sa.company_count)
-- ELSE
CONCAT('3m', '|', sa.ps_ed_3m_n, '|', sa.ps_ed_3m_hit, '|', sa.ps_ed_3m_mean, '|', sa.company_count)
-- END
END
ELSE
-- CASE WHEN 180 > 366 THEN
-- CONCAT('--|--|--|--|', sa.company_count)
-- ELSE
CONCAT('6m', '|', sa.ps_ed_6m_n, '|', sa.ps_ed_6m_hit, '|', sa.ps_ed_6m_mean, '|', sa.company_count)
-- END
END
ELSE
-- CASE WHEN 270 > 366 THEN
-- CONCAT('--|--|--|--|', sa.company_count)
-- ELSE
CONCAT('9m', '|', sa.ps_ed_9m_n, '|', sa.ps_ed_9m_hit, '|', sa.ps_ed_9m_mean, '|', sa.company_count)
-- END
END
ELSE
-- CASE WHEN 365 > 366 THEN
-- CONCAT('--|--|--|--|', sa.company_count)
-- ELSE
CONCAT('1y', '|', sa.ps_ed_1y_n, '|', sa.ps_ed_1y_hit, '|', sa.ps_ed_1y_mean, '|', sa.company_count)
-- END
END
ELSE
CASE WHEN
(
(I.JMTrans_Type = 'S' AND I.OptionsFlag = '')
OR (I.JMTrans_Type = 'AS' AND I.OptionsFlag = '')
OR (I.JMTrans_Type = 'JS*' AND I.OptionsFlag = '')
OR (I.JMTrans_Type = 'OS' AND I.OptionsFlag = '')
OR I.OptionsFlag = 'S'
OR I.OptionsFlag = 'AS'
OR I.OptionsFlag = 'JS*'
OR I.OptionsFlag = 'OS'
)
THEN
CASE WHEN sa.ss_ed_1y_n IS NULL OR sa.ss_ed_1y_n = 0 OR 365 > 366 THEN
CASE WHEN sa.ss_ed_9m_n IS NULL OR sa.ss_ed_9m_n = 0 OR 270 > 366 THEN
CASE WHEN sa.ss_ed_6m_n IS NULL OR sa.ss_ed_6m_n = 0 OR 180 > 366 THEN
CASE WHEN sa.ss_ed_3m_n IS NULL OR sa.ss_ed_3m_n = 0 OR 90 > 366 THEN
CASE WHEN sa.ss_ed_1m_n IS NULL OR sa.ss_ed_1m_n = 0 OR 30 > 366 THEN
CASE WHEN sa.ss_ed_1w_n IS NULL OR sa.ss_ed_1w_n = 0 OR 7 > 366 THEN
CONCAT('--|--|--|--|', sa.company_count)
ELSE
-- CASE WHEN 7 > 366 THEN
-- CONCAT('--|--|--|--|', sa.company_count)
-- ELSE
CONCAT('1w', '|', sa.ss_ed_1w_n, '|', sa.ss_ed_1w_hit, '|', sa.ss_ed_1w_mean, '|', sa.company_count)
-- END
END
ELSE
-- CASE WHEN 30 > 366 THEN
-- CONCAT('--|--|--|--|', sa.company_count)
-- ELSE
CONCAT('1m', '|', sa.ss_ed_1m_n, '|', sa.ss_ed_1m_hit, '|', sa.ss_ed_1m_mean, '|', sa.company_count)
-- END
END
ELSE
-- CASE WHEN 90 > 366 THEN
-- CONCAT('--|--|--|--|', sa.company_count)
-- ELSE
CONCAT('3m', '|', sa.ss_ed_3m_n, '|', sa.ss_ed_3m_hit, '|', sa.ss_ed_3m_mean, '|', sa.company_count)
-- END
END
ELSE
-- CASE WHEN 180 > 366 THEN
-- CONCAT('--|--|--|--|', sa.company_count)
-- ELSE
CONCAT('6m', '|', sa.ss_ed_6m_n, '|', sa.ss_ed_6m_hit, '|', sa.ss_ed_6m_mean, '|', sa.company_count)
-- END
END
ELSE
-- CASE WHEN 270 > 366 THEN
-- CONCAT('--|--|--|--|', sa.company_count)
-- ELSE
CONCAT('9m', '|', sa.ss_ed_9m_n, '|', sa.ss_ed_9m_hit, '|', sa.ss_ed_9m_mean, '|', sa.company_count)
-- END
END
ELSE
-- CASE WHEN 365 > 366 THEN
-- CONCAT('--|--|--|--|', sa.company_count)
-- ELSE
CONCAT('1y', '|', sa.ss_ed_1y_n, '|', sa.ss_ed_1y_hit, '|', sa.ss_ed_1y_mean, '|', sa.company_count)
-- END
END
ELSE
'er'
END
END) as savvy_analysis,
insider.udf_SEOFriendlyTitle(I.filer_name) as seo_filer_name
FROM
insider.details_import I
LEFT JOIN (insider.SIC_Matrix S) ON (S.SIC = I.SIC_Code)
LEFT JOIN (insider.JMTrans_Types J) ON (J.Type = IF ((NOT I.OptionsFlag = '' AND I.OptionsFlag IS NOT NULL), I.OptionsFlag, I.JMTrans_Type))
LEFT JOIN ratings.savvy_analysis_final sa
ON sa.owner_cik = I.OwnerCIK
AND sa.issuer_cik = I.IssuerCIK
WHERE UID IN(5432478,5432476,5432475,5432474,5425340,5425321,5425326,5425325,5406977,5406976,5407616,5406974,5409280,5409279,5406975,5406790,5404349,5404347,5404345,5404344,5404313,5404310,5401427,5404024,5401425,5401426,5399522,5397717,5397718,5395920,5395917,5395916,5395919,5395918,5395915,5395914,5395913,5393101,5393102,5393100,5357489,5352948,5352925,5352916,5322841,5322835,5322838,5311219,5235892,5235878)
AND DATE(DATE_SUB(CAST(CURDATE() AS DATE),INTERVAL 2 YEAR)) < CAST(trans_date_to AS DATE)
#AND DATE(DATE_SUB(CAST(CURDATE() AS DATE),INTERVAL 1 DAY)) >= CAST(entered_date AS DATE)
ORDER BY 9 DESC, 16 DESC ,
JMOrder ASC