«Звезда» — оптимальная структура данных при переходе на российский BI22.07.2022 15:01

--Basic link blocks
DROP TABLE IF EXISTS "Visiology_DM_Categories_LINK_BLOCK";
CREATE TABLE "Visiology_DM_Categories_LINK_BLOCK" AS SELECT 
'Categories' AS "DM_Entity",
"CAT__Category_ID", 'CAT__Category_ID' AS "CAT__Category_ID_TYPE"
from "Categories";

DROP TABLE IF EXISTS "Visiology_DM_Companies_LINK_BLOCK";
CREATE TABLE "Visiology_DM_Companies_LINK_BLOCK" AS SELECT 
'Companies' AS "DM_Entity",
"COM__Company_ID", 'COM__Company_ID' AS "COM__Company_ID_TYPE",
"COM__Region_ID" AS "REGC__Region_ID", 'COM__Region_ID' AS "REGC__Region_ID_TYPE",
"COM__UserID" AS "USR__User_ID", 'COM__UserID' AS "USR__User_ID_TYPE"
from "Companies";

DROP TABLE IF EXISTS "Visiology_DM_Contacts_LINK_BLOCK";
CREATE TABLE "Visiology_DM_Contacts_LINK_BLOCK" AS SELECT 
'Contacts' AS "DM_Entity",
"CON__Contact_ID", 'CON__Contact_ID' AS "CON__Contact_ID_TYPE",
"CON__Company_ID" AS "COM__Company_ID", 'CON__Company_ID' AS "COM__Company_ID_TYPE"
from "Contacts";

DROP TABLE IF EXISTS "Visiology_DM_Products_LINK_BLOCK";
CREATE TABLE "Visiology_DM_Products_LINK_BLOCK" AS SELECT 
'Products' AS "DM_Entity",
"PRD__Product_ID", 'PRD__Product_ID' AS "PRD__Product_ID_TYPE",
"PRD__Category_ID" AS "CAT__Category_ID", 'PRD__Category_ID' AS "CAT__Category_ID_TYPE"
from "Products";

DROP TABLE IF EXISTS "Visiology_DM_Regions_LINK_BLOCK";
CREATE TABLE "Visiology_DM_Regions_LINK_BLOCK" AS SELECT 
'Regions' AS "DM_Entity",
"REG__Region_ID", 'REG__Region_ID' AS "REG__Region_ID_TYPE"
from "Regions";

DROP TABLE IF EXISTS "Visiology_DM_Regions_for_companies_LINK_BLOCK";
CREATE TABLE "Visiology_DM_Regions_for_companies_LINK_BLOCK" AS SELECT 
'Regions_for_companies' AS "DM_Entity",
"REGC__Region_ID", 'REGC__Region_ID' AS "REGC__Region_ID_TYPE"
from "Regions_for_companies";

DROP TABLE IF EXISTS "Visiology_DM_Sales_LINK_BLOCK";
CREATE TABLE "Visiology_DM_Sales_LINK_BLOCK" AS SELECT 
'Sales' AS "DM_Entity",
"SLS__Sales_Comp_Key", 'SLS__Sales_Comp_Key' AS "SLS__Sales_Comp_Key_TYPE",
"SLS__User_ID" AS "USR__User_ID", 'SLS__User_ID' AS "USR__User_ID_TYPE",
"SLS__Contact_ID" AS "CON__Contact_ID", 'SLS__Contact_ID' AS "CON__Contact_ID_TYPE",
"SLS__Product_ID" AS "PRD__Product_ID", 'SLS__Product_ID' AS "PRD__Product_ID_TYPE"
from "Sales";

DROP TABLE IF EXISTS "Visiology_DM_Sales_plan_LINK_BLOCK";
CREATE TABLE "Visiology_DM_Sales_plan_LINK_BLOCK" AS SELECT 
'Sales_plan' AS "DM_Entity",
"PLN__Plan_Comp_Key", 'PLN__Plan_Comp_Key' AS "PLN__Plan_Comp_Key_TYPE",
"PLN__Plan_month" AS "DATE", 'PLN__Plan_month' AS "DATE_TYPE",
"PLN__User_ID" AS "USR__User_ID", 'PLN__User_ID' AS "USR__User_ID_TYPE"
from "Sales_plan";

DROP TABLE IF EXISTS "Visiology_DM_Users_LINK_BLOCK";
CREATE TABLE "Visiology_DM_Users_LINK_BLOCK" AS SELECT 
'Users' AS "DM_Entity",
"USR__User_ID", 'USR__User_ID' AS "USR__User_ID_TYPE",
"USR__Region_ID" AS "REG__Region_ID", 'USR__Region_ID' AS "REG__Region_ID_TYPE"
from "Users";




--Multy-variant association
--- Expanding association DATE for Sales
DROP TABLE IF EXISTS "Visiology_DM_Sales_LINK_BLOCK_DATE";
CREATE TABLE "Visiology_DM_Sales_LINK_BLOCK_DATE" AS SELECT 
"SLS__Sales_Comp_Key", 
"SLS__CreateDate" AS "DATE", 'SLS__CreateDate' AS "DATE_TYPE"
from "Sales" WHERE "SLS__CreateDate" IS NOT NULL

UNION
SELECT 
"SLS__Sales_Comp_Key", 
"SLS__CloseDate" AS "DATE", 'SLS__CloseDate' AS "DATE_TYPE"
from "Sales" WHERE "SLS__CloseDate" IS NOT NULL;

DROP TABLE IF EXISTS "Visiology_DM_Sales_LINK_BLOCK_JOIN";
CREATE TABLE "Visiology_DM_Sales_LINK_BLOCK_JOIN" AS SELECT * FROM "Visiology_DM_Sales_LINK_BLOCK"
LEFT JOIN "Visiology_DM_Sales_LINK_BLOCK_DATE" USING ("SLS__Sales_Comp_Key");

DROP TABLE IF EXISTS "Visiology_DM_Sales_LINK_BLOCK";
ALTER TABLE "Visiology_DM_Sales_LINK_BLOCK_JOIN" RENAME TO "Visiology_DM_Sales_LINK_BLOCK";
DROP TABLE "Visiology_DM_Sales_LINK_BLOCK_DATE";


--Association restoration
--Link restoration for Companies from Users, level 1
	DROP TABLE IF EXISTS "Visiology_DM_Companies_LINK_RESTORE_PART";
	CREATE TABLE "Visiology_DM_Companies_LINK_RESTORE_PART" AS SELECT DISTINCT
	"USR__User_ID",
	"REG__Region_ID", "REG__Region_ID_TYPE"
	FROM "Visiology_DM_Users_LINK_BLOCK";
	
	DROP TABLE IF EXISTS "Visiology_DM_Companies_JOIN";
	CREATE TABLE "Visiology_DM_Companies_JOIN" AS SELECT DISTINCT *
	FROM "Visiology_DM_Companies_LINK_BLOCK" LEFT JOIN "Visiology_DM_Companies_LINK_RESTORE_PART" USING ("USR__User_ID");
	DROP TABLE IF EXISTS "Visiology_DM_Companies_LINK_BLOCK";
	DROP TABLE IF EXISTS "Visiology_DM_Companies_LINK_RESTORE_PART";
	ALTER TABLE "Visiology_DM_Companies_JOIN" RENAME TO "Visiology_DM_Companies_LINK_BLOCK";


--Link restoration for Contacts from Companies, level 1
	DROP TABLE IF EXISTS "Visiology_DM_Contacts_LINK_RESTORE_PART";
	CREATE TABLE "Visiology_DM_Contacts_LINK_RESTORE_PART" AS SELECT DISTINCT
	"COM__Company_ID",
	"REGC__Region_ID", "REGC__Region_ID_TYPE",
	"USR__User_ID", "USR__User_ID_TYPE"
	FROM "Visiology_DM_Companies_LINK_BLOCK";
	
	DROP TABLE IF EXISTS "Visiology_DM_Contacts_JOIN";
	CREATE TABLE "Visiology_DM_Contacts_JOIN" AS SELECT DISTINCT *
	FROM "Visiology_DM_Contacts_LINK_BLOCK" LEFT JOIN "Visiology_DM_Contacts_LINK_RESTORE_PART" USING ("COM__Company_ID");
	DROP TABLE IF EXISTS "Visiology_DM_Contacts_LINK_BLOCK";
	DROP TABLE IF EXISTS "Visiology_DM_Contacts_LINK_RESTORE_PART";
	ALTER TABLE "Visiology_DM_Contacts_JOIN" RENAME TO "Visiology_DM_Contacts_LINK_BLOCK";


--Link restoration for Contacts from Users, level 2
		DROP TABLE IF EXISTS "Visiology_DM_Contacts_LINK_RESTORE_PART";
		CREATE TABLE "Visiology_DM_Contacts_LINK_RESTORE_PART" AS SELECT DISTINCT
		"USR__User_ID",
		"REG__Region_ID", "REG__Region_ID_TYPE"
		FROM "Visiology_DM_Users_LINK_BLOCK";
		
		DROP TABLE IF EXISTS "Visiology_DM_Contacts_JOIN";
		CREATE TABLE "Visiology_DM_Contacts_JOIN" AS SELECT DISTINCT *
		FROM "Visiology_DM_Contacts_LINK_BLOCK" LEFT JOIN "Visiology_DM_Contacts_LINK_RESTORE_PART" USING ("USR__User_ID");
		DROP TABLE IF EXISTS "Visiology_DM_Contacts_LINK_BLOCK";
		DROP TABLE IF EXISTS "Visiology_DM_Contacts_LINK_RESTORE_PART";
		ALTER TABLE "Visiology_DM_Contacts_JOIN" RENAME TO "Visiology_DM_Contacts_LINK_BLOCK";


--Link restoration for Sales from Contacts, level 1
	DROP TABLE IF EXISTS "Visiology_DM_Sales_LINK_RESTORE_PART";
	CREATE TABLE "Visiology_DM_Sales_LINK_RESTORE_PART" AS SELECT DISTINCT
	"CON__Contact_ID",
	"COM__Company_ID", "COM__Company_ID_TYPE"
	FROM "Visiology_DM_Contacts_LINK_BLOCK";
	
	DROP TABLE IF EXISTS "Visiology_DM_Sales_JOIN";
	CREATE TABLE "Visiology_DM_Sales_JOIN" AS SELECT DISTINCT *
	FROM "Visiology_DM_Sales_LINK_BLOCK" LEFT JOIN "Visiology_DM_Sales_LINK_RESTORE_PART" USING ("CON__Contact_ID");
	DROP TABLE IF EXISTS "Visiology_DM_Sales_LINK_BLOCK";
	DROP TABLE IF EXISTS "Visiology_DM_Sales_LINK_RESTORE_PART";
	ALTER TABLE "Visiology_DM_Sales_JOIN" RENAME TO "Visiology_DM_Sales_LINK_BLOCK";


--Link restoration for Sales from Products, level 1
	DROP TABLE IF EXISTS "Visiology_DM_Sales_LINK_RESTORE_PART";
	CREATE TABLE "Visiology_DM_Sales_LINK_RESTORE_PART" AS SELECT DISTINCT
	"PRD__Product_ID",
	"CAT__Category_ID", "CAT__Category_ID_TYPE"
	FROM "Visiology_DM_Products_LINK_BLOCK";
	
	DROP TABLE IF EXISTS "Visiology_DM_Sales_JOIN";
	CREATE TABLE "Visiology_DM_Sales_JOIN" AS SELECT DISTINCT *
	FROM "Visiology_DM_Sales_LINK_BLOCK" LEFT JOIN "Visiology_DM_Sales_LINK_RESTORE_PART" USING ("PRD__Product_ID");
	DROP TABLE IF EXISTS "Visiology_DM_Sales_LINK_BLOCK";
	DROP TABLE IF EXISTS "Visiology_DM_Sales_LINK_RESTORE_PART";
	ALTER TABLE "Visiology_DM_Sales_JOIN" RENAME TO "Visiology_DM_Sales_LINK_BLOCK";


--Link restoration for Sales from Users, level 1
	DROP TABLE IF EXISTS "Visiology_DM_Sales_LINK_RESTORE_PART";
	CREATE TABLE "Visiology_DM_Sales_LINK_RESTORE_PART" AS SELECT DISTINCT
	"USR__User_ID",
	"REG__Region_ID", "REG__Region_ID_TYPE"
	FROM "Visiology_DM_Users_LINK_BLOCK";
	
	DROP TABLE IF EXISTS "Visiology_DM_Sales_JOIN";
	CREATE TABLE "Visiology_DM_Sales_JOIN" AS SELECT DISTINCT *
	FROM "Visiology_DM_Sales_LINK_BLOCK" LEFT JOIN "Visiology_DM_Sales_LINK_RESTORE_PART" USING ("USR__User_ID");
	DROP TABLE IF EXISTS "Visiology_DM_Sales_LINK_BLOCK";
	DROP TABLE IF EXISTS "Visiology_DM_Sales_LINK_RESTORE_PART";
	ALTER TABLE "Visiology_DM_Sales_JOIN" RENAME TO "Visiology_DM_Sales_LINK_BLOCK";


--Link restoration for Sales from Companies, level 2
		DROP TABLE IF EXISTS "Visiology_DM_Sales_LINK_RESTORE_PART";
		CREATE TABLE "Visiology_DM_Sales_LINK_RESTORE_PART" AS SELECT DISTINCT
		"COM__Company_ID",
		"REGC__Region_ID", "REGC__Region_ID_TYPE"
		FROM "Visiology_DM_Companies_LINK_BLOCK";
		
		DROP TABLE IF EXISTS "Visiology_DM_Sales_JOIN";
		CREATE TABLE "Visiology_DM_Sales_JOIN" AS SELECT DISTINCT *
		FROM "Visiology_DM_Sales_LINK_BLOCK" LEFT JOIN "Visiology_DM_Sales_LINK_RESTORE_PART" USING ("COM__Company_ID");
		DROP TABLE IF EXISTS "Visiology_DM_Sales_LINK_BLOCK";
		DROP TABLE IF EXISTS "Visiology_DM_Sales_LINK_RESTORE_PART";
		ALTER TABLE "Visiology_DM_Sales_JOIN" RENAME TO "Visiology_DM_Sales_LINK_BLOCK";


--Link restoration for Sales_plan from Users, level 1
	DROP TABLE IF EXISTS "Visiology_DM_Sales_plan_LINK_RESTORE_PART";
	CREATE TABLE "Visiology_DM_Sales_plan_LINK_RESTORE_PART" AS SELECT DISTINCT
	"USR__User_ID",
	"REG__Region_ID", "REG__Region_ID_TYPE"
	FROM "Visiology_DM_Users_LINK_BLOCK";
	
	DROP TABLE IF EXISTS "Visiology_DM_Sales_plan_JOIN";
	CREATE TABLE "Visiology_DM_Sales_plan_JOIN" AS SELECT DISTINCT *
	FROM "Visiology_DM_Sales_plan_LINK_BLOCK" LEFT JOIN "Visiology_DM_Sales_plan_LINK_RESTORE_PART" USING ("USR__User_ID");
	DROP TABLE IF EXISTS "Visiology_DM_Sales_plan_LINK_BLOCK";
	DROP TABLE IF EXISTS "Visiology_DM_Sales_plan_LINK_RESTORE_PART";
	ALTER TABLE "Visiology_DM_Sales_plan_JOIN" RENAME TO "Visiology_DM_Sales_plan_LINK_BLOCK";





--Link Table finalization
--Creating empty Link Table with full associations set
---Preparing associations sample blocks
DROP TABLE IF EXISTS "Categories_ASSOC_SET";
CREATE TABLE "Categories_ASSOC_SET" AS SELECT 
"DM_Entity",
"CAT__Category_ID", "CAT__Category_ID_TYPE"
FROM "Visiology_DM_Categories_LINK_BLOCK" LIMIT 0;

DROP TABLE IF EXISTS "Companies_ASSOC_SET";
CREATE TABLE "Companies_ASSOC_SET" AS SELECT 
"DM_Entity",
"REGC__Region_ID", "REGC__Region_ID_TYPE",
"USR__User_ID", "USR__User_ID_TYPE"
FROM "Visiology_DM_Companies_LINK_BLOCK" LIMIT 0;

DROP TABLE IF EXISTS "Contacts_ASSOC_SET";
CREATE TABLE "Contacts_ASSOC_SET" AS SELECT 
"DM_Entity",
"COM__Company_ID", "COM__Company_ID_TYPE",
"CON__Contact_ID", "CON__Contact_ID_TYPE"
FROM "Visiology_DM_Contacts_LINK_BLOCK" LIMIT 0;

DROP TABLE IF EXISTS "Products_ASSOC_SET";
CREATE TABLE "Products_ASSOC_SET" AS SELECT 
"DM_Entity",
"PRD__Product_ID", "PRD__Product_ID_TYPE"
FROM "Visiology_DM_Products_LINK_BLOCK" LIMIT 0;

DROP TABLE IF EXISTS "Regions_ASSOC_SET";
CREATE TABLE "Regions_ASSOC_SET" AS SELECT 
"DM_Entity",
"REG__Region_ID", "REG__Region_ID_TYPE"
FROM "Visiology_DM_Regions_LINK_BLOCK" LIMIT 0;

DROP TABLE IF EXISTS "Sales_ASSOC_SET";
CREATE TABLE "Sales_ASSOC_SET" AS SELECT 
"DM_Entity",
"DATE", "DATE_TYPE",
"SLS__Sales_Comp_Key", "SLS__Sales_Comp_Key_TYPE"
FROM "Visiology_DM_Sales_LINK_BLOCK" LIMIT 0;

DROP TABLE IF EXISTS "Sales_plan_ASSOC_SET";
CREATE TABLE "Sales_plan_ASSOC_SET" AS SELECT 
"DM_Entity",
"PLN__Plan_Comp_Key", "PLN__Plan_Comp_Key_TYPE"
FROM "Visiology_DM_Sales_plan_LINK_BLOCK" LIMIT 0;



---Joining all blocks to single table
DROP TABLE IF EXISTS "Visiology_DM_LINK_TABLE_TMP";
CREATE TABLE "Visiology_DM_LINK_TABLE_TMP"AS SELECT * FROM "Categories_ASSOC_SET"
LEFT JOIN "Contacts_ASSOC_SET" USING ("DM_Entity")
LEFT JOIN "Sales_ASSOC_SET" USING ("DM_Entity")
LEFT JOIN "Sales_plan_ASSOC_SET" USING ("DM_Entity")
LEFT JOIN "Products_ASSOC_SET" USING ("DM_Entity")
LEFT JOIN "Companies_ASSOC_SET" USING ("DM_Entity")
LEFT JOIN "Regions_ASSOC_SET" USING ("DM_Entity");

---Droping associations blocks
DROP TABLE IF EXISTS "Categories_ASSOC_SET";
DROP TABLE IF EXISTS "Contacts_ASSOC_SET";
DROP TABLE IF EXISTS "Sales_ASSOC_SET";
DROP TABLE IF EXISTS "Sales_plan_ASSOC_SET";
DROP TABLE IF EXISTS "Products_ASSOC_SET";
DROP TABLE IF EXISTS "Companies_ASSOC_SET";
DROP TABLE IF EXISTS "Regions_ASSOC_SET";



DROP TABLE IF EXISTS "Visiology_DM_LINK_TABLE";
CREATE TABLE "Visiology_DM_LINK_TABLE" AS SELECT 
"DM_Entity",
"CAT__Category_ID", "CAT__Category_ID_TYPE",
"COM__Company_ID", "COM__Company_ID_TYPE",
"CON__Contact_ID", "CON__Contact_ID_TYPE",
"DATE", "DATE_TYPE",
"PLN__Plan_Comp_Key", "PLN__Plan_Comp_Key_TYPE",
"PRD__Product_ID", "PRD__Product_ID_TYPE",
"REG__Region_ID", "REG__Region_ID_TYPE",
"REGC__Region_ID", "REGC__Region_ID_TYPE",
"SLS__Sales_Comp_Key", "SLS__Sales_Comp_Key_TYPE",
"USR__User_ID", "USR__User_ID_TYPE"
FROM "Visiology_DM_LINK_TABLE_TMP"


UNION SELECT
"DM_Entity",
"CAT__Category_ID", "CAT__Category_ID_TYPE",
null as "COM__Company_ID", null as "COM__Company_ID_TYPE",
null as "CON__Contact_ID", null as "CON__Contact_ID_TYPE",
null as "DATE", null as "DATE_TYPE",
null as "PLN__Plan_Comp_Key", null as "PLN__Plan_Comp_Key_TYPE",
null as "PRD__Product_ID", null as "PRD__Product_ID_TYPE",
null as "REG__Region_ID", null as "REG__Region_ID_TYPE",
null as "REGC__Region_ID", null as "REGC__Region_ID_TYPE",
null as "SLS__Sales_Comp_Key", null as "SLS__Sales_Comp_Key_TYPE",
null as "USR__User_ID", null as "USR__User_ID_TYPE"
FROM "Visiology_DM_Categories_LINK_BLOCK"

UNION SELECT
"DM_Entity",
null as "CAT__Category_ID", null as "CAT__Category_ID_TYPE",
"COM__Company_ID", "COM__Company_ID_TYPE",
null as "CON__Contact_ID", null as "CON__Contact_ID_TYPE",
null as "DATE", null as "DATE_TYPE",
null as "PLN__Plan_Comp_Key", null as "PLN__Plan_Comp_Key_TYPE",
null as "PRD__Product_ID", null as "PRD__Product_ID_TYPE",
"REG__Region_ID", "REG__Region_ID_TYPE",
"REGC__Region_ID", "REGC__Region_ID_TYPE",
null as "SLS__Sales_Comp_Key", null as "SLS__Sales_Comp_Key_TYPE",
"USR__User_ID", "USR__User_ID_TYPE"
FROM "Visiology_DM_Companies_LINK_BLOCK"

UNION SELECT
"DM_Entity",
null as "CAT__Category_ID", null as "CAT__Category_ID_TYPE",
"COM__Company_ID", "COM__Company_ID_TYPE",
"CON__Contact_ID", "CON__Contact_ID_TYPE",
null as "DATE", null as "DATE_TYPE",
null as "PLN__Plan_Comp_Key", null as "PLN__Plan_Comp_Key_TYPE",
null as "PRD__Product_ID", null as "PRD__Product_ID_TYPE",
"REG__Region_ID", "REG__Region_ID_TYPE",
"REGC__Region_ID", "REGC__Region_ID_TYPE",
null as "SLS__Sales_Comp_Key", null as "SLS__Sales_Comp_Key_TYPE",
"USR__User_ID", "USR__User_ID_TYPE"
FROM "Visiology_DM_Contacts_LINK_BLOCK"

UNION SELECT
"DM_Entity",
"CAT__Category_ID", "CAT__Category_ID_TYPE",
null as "COM__Company_ID", null as "COM__Company_ID_TYPE",
null as "CON__Contact_ID", null as "CON__Contact_ID_TYPE",
null as "DATE", null as "DATE_TYPE",
null as "PLN__Plan_Comp_Key", null as "PLN__Plan_Comp_Key_TYPE",
"PRD__Product_ID", "PRD__Product_ID_TYPE",
null as "REG__Region_ID", null as "REG__Region_ID_TYPE",
null as "REGC__Region_ID", null as "REGC__Region_ID_TYPE",
null as "SLS__Sales_Comp_Key", null as "SLS__Sales_Comp_Key_TYPE",
null as "USR__User_ID", null as "USR__User_ID_TYPE"
FROM "Visiology_DM_Products_LINK_BLOCK"

UNION SELECT
"DM_Entity",
null as "CAT__Category_ID", null as "CAT__Category_ID_TYPE",
null as "COM__Company_ID", null as "COM__Company_ID_TYPE",
null as "CON__Contact_ID", null as "CON__Contact_ID_TYPE",
null as "DATE", null as "DATE_TYPE",
null as "PLN__Plan_Comp_Key", null as "PLN__Plan_Comp_Key_TYPE",
null as "PRD__Product_ID", null as "PRD__Product_ID_TYPE",
"REG__Region_ID", "REG__Region_ID_TYPE",
null as "REGC__Region_ID", null as "REGC__Region_ID_TYPE",
null as "SLS__Sales_Comp_Key", null as "SLS__Sales_Comp_Key_TYPE",
null as "USR__User_ID", null as "USR__User_ID_TYPE"
FROM "Visiology_DM_Regions_LINK_BLOCK"

UNION SELECT
"DM_Entity",
null as "CAT__Category_ID", null as "CAT__Category_ID_TYPE",
null as "COM__Company_ID", null as "COM__Company_ID_TYPE",
null as "CON__Contact_ID", null as "CON__Contact_ID_TYPE",
null as "DATE", null as "DATE_TYPE",
null as "PLN__Plan_Comp_Key", null as "PLN__Plan_Comp_Key_TYPE",
null as "PRD__Product_ID", null as "PRD__Product_ID_TYPE",
null as "REG__Region_ID", null as "REG__Region_ID_TYPE",
"REGC__Region_ID", "REGC__Region_ID_TYPE",
null as "SLS__Sales_Comp_Key", null as "SLS__Sales_Comp_Key_TYPE",
null as "USR__User_ID", null as "USR__User_ID_TYPE"
FROM "Visiology_DM_Regions_for_companies_LINK_BLOCK"

UNION SELECT
"DM_Entity",
"CAT__Category_ID", "CAT__Category_ID_TYPE",
"COM__Company_ID", "COM__Company_ID_TYPE",
"CON__Contact_ID", "CON__Contact_ID_TYPE",
"DATE", "DATE_TYPE",
null as "PLN__Plan_Comp_Key", null as "PLN__Plan_Comp_Key_TYPE",
"PRD__Product_ID", "PRD__Product_ID_TYPE",
"REG__Region_ID", "REG__Region_ID_TYPE",
"REGC__Region_ID", "REGC__Region_ID_TYPE",
"SLS__Sales_Comp_Key", "SLS__Sales_Comp_Key_TYPE",
"USR__User_ID", "USR__User_ID_TYPE"
FROM "Visiology_DM_Sales_LINK_BLOCK"

UNION SELECT
"DM_Entity",
null as "CAT__Category_ID", null as "CAT__Category_ID_TYPE",
null as "COM__Company_ID", null as "COM__Company_ID_TYPE",
null as "CON__Contact_ID", null as "CON__Contact_ID_TYPE",
"DATE", "DATE_TYPE",
"PLN__Plan_Comp_Key", "PLN__Plan_Comp_Key_TYPE",
null as "PRD__Product_ID", null as "PRD__Product_ID_TYPE",
"REG__Region_ID", "REG__Region_ID_TYPE",
null as "REGC__Region_ID", null as "REGC__Region_ID_TYPE",
null as "SLS__Sales_Comp_Key", null as "SLS__Sales_Comp_Key_TYPE",
"USR__User_ID", "USR__User_ID_TYPE"
FROM "Visiology_DM_Sales_plan_LINK_BLOCK"

UNION SELECT
"DM_Entity",
null as "CAT__Category_ID", null as "CAT__Category_ID_TYPE",
null as "COM__Company_ID", null as "COM__Company_ID_TYPE",
null as "CON__Contact_ID", null as "CON__Contact_ID_TYPE",
null as "DATE", null as "DATE_TYPE",
null as "PLN__Plan_Comp_Key", null as "PLN__Plan_Comp_Key_TYPE",
null as "PRD__Product_ID", null as "PRD__Product_ID_TYPE",
"REG__Region_ID", "REG__Region_ID_TYPE",
null as "REGC__Region_ID", null as "REGC__Region_ID_TYPE",
null as "SLS__Sales_Comp_Key", null as "SLS__Sales_Comp_Key_TYPE",
"USR__User_ID", "USR__User_ID_TYPE"
FROM "Visiology_DM_Users_LINK_BLOCK";

DROP TABLE IF EXISTS "Visiology_DM_LINK_TABLE_TMP";
---Droping of link blocks
DROP TABLE IF EXISTS "Visiology_DM_Contacts_LINK_BLOCK";
DROP TABLE IF EXISTS "Visiology_DM_Companies_LINK_BLOCK";
DROP TABLE IF EXISTS "Visiology_DM_Categories_LINK_BLOCK";
DROP TABLE IF EXISTS "Visiology_DM_Products_LINK_BLOCK";
DROP TABLE IF EXISTS "Visiology_DM_Regions_LINK_BLOCK";
DROP TABLE IF EXISTS "Visiology_DM_Users_LINK_BLOCK";
DROP TABLE IF EXISTS "Visiology_DM_Regions_for_companies_LINK_BLOCK";
DROP TABLE IF EXISTS "Visiology_DM_Sales_LINK_BLOCK";
DROP TABLE IF EXISTS "Visiology_DM_Sales_plan_LINK_BLOCK";



--Measures join
--- Creating measures table
DROP TABLE IF EXISTS "Sales_MEASURES";
CREATE TABLE "Sales_MEASURES" AS SELECT 
"SLS__Sales_Comp_Key",
"SLS__Sales_Sum_VAT",
"SLS__Cost_Sum_VAT",
"SLS__Amount" FROM "Sales";

DROP TABLE IF EXISTS "Sales_plan_MEASURES";
CREATE TABLE "Sales_plan_MEASURES" AS SELECT 
"PLN__Plan_Comp_Key",
"PLN__Sales_plan" FROM "Sales_plan";




--- Joining measures table
DROP TABLE IF EXISTS "Visiology_DM_LINK_TABLE_JOIN";
CREATE TABLE "Visiology_DM_LINK_TABLE_JOIN" AS SELECT * FROM "Visiology_DM_LINK_TABLE"
LEFT JOIN "Sales_MEASURES" USING ("SLS__Sales_Comp_Key")
LEFT JOIN "Sales_plan_MEASURES" USING ("PLN__Plan_Comp_Key");

DROP TABLE IF EXISTS "Visiology_DM_LINK_TABLE";
ALTER TABLE "Visiology_DM_LINK_TABLE_JOIN" RENAME TO "Visiology_DM_LINK_TABLE";


--- Droping measures table
DROP TABLE IF EXISTS "Sales_MEASURES";
DROP TABLE IF EXISTS "Sales_plan_MEASURES";

© Habrahabr.ru