The text below is selected, press Ctrl+C to copy to your clipboard. (⌘+C on Mac) No line numbers will be copied.
Guest
Merged_SQL_31_01_2019
By rajnish8987 on 31st January 2019 02:40:19 PM | Syntax: SQL | Views: 1



New paste | Download | Show/Hide line no. | Copy text to clipboard
  1.     CREATE TABLE BAC1 (Billing_Account_Number INT, Billing_account_key INT, Parent_type VARCHAR(50), Active_flag CHAR(50), Deleted_flag CHAR(50),
  2.     Created_date DATE, Edw_update_date DATE, Billing_status CHAR(20), Source_system CHAR(20), migrartion_date DATE);
  3.      
  4.     INSERT INTO BAC1 (Billing_Account_Number, Billing_account_key, Parent_type, Active_flag, Deleted_flag, Created_date, Edw_update_date,
  5.     Billing_status, Source_system, migrartion_date) VALUES(001, 111, 'Bill', 'Yes', 'No', '26-jan-2019', '27-jan-2019', 'Completed', 'Laptop', '27-jan-2019');
  6.      
  7.     INSERT INTO BAC1 (Billing_Account_Number, Billing_account_key, Parent_type, Active_flag, Deleted_flag, Created_date, Edw_update_date,
  8.     Billing_status, Source_system, migrartion_date) VALUES(002, 222, 'Bill', 'Yes', 'No', '26-jan-2019', '27-jan-2019', 'Completed', 'Laptop', '27-jan-2019');
  9.      
  10.     INSERT INTO BAC1 (Billing_Account_Number, Billing_account_key, Parent_type, Active_flag, Deleted_flag, Created_date, Edw_update_date,
  11.     Billing_status, Source_system, migrartion_date) VALUES(003, 333, 'Bill', 'Yes', 'No', '26-jan-2019', '27-jan-2019', 'Completed', 'Laptop', '27-jan-2019');
  12.      
  13.     INSERT INTO BAC1 (Billing_Account_Number, Billing_account_key, Parent_type, Active_flag, Deleted_flag, Created_date, Edw_update_date,
  14.     Billing_status, Source_system, migrartion_date) VALUES(004, 444, 'Bill', 'Yes', 'No', '26-jan-2019', '27-jan-2019', 'Completed', 'Laptop', '27-jan-2019');
  15.      
  16.      
  17.     INSERT INTO BAC1 (Billing_Account_Number, Billing_account_key, Parent_type, Active_flag, Deleted_flag, Created_date, Edw_update_date,
  18.     Billing_status, Source_system, migrartion_date) VALUES(005, 555, 'Bill', 'Yes', 'No', '26-jan-2019', '27-jan-2019', 'Completed', 'Laptop', '27-jan-2019');
  19.      
  20.      
  21.     INSERT INTO BAC1 (Billing_Account_Number, Billing_account_key, Parent_type, Active_flag, Deleted_flag, Created_date, Edw_update_date,
  22.     Billing_status, Source_system, migrartion_date) VALUES(006, 666, 'Bill', 'Yes', 'No', '26-jan-2019', '27-jan-2019', 'Completed', 'Laptop', '27-jan-2019');
  23.      
  24.      
  25.     INSERT INTO BAC1 (Billing_Account_Number, Billing_account_key, Parent_type, Active_flag, Deleted_flag, Created_date, Edw_update_date,
  26.     Billing_status, Source_system, migrartion_date) VALUES(007, 777, 'Bill', 'Yes', 'No', '28-jan-2019', '29-jan-2019', 'Completed', 'Laptop', '29-jan-2019');
  27.      
  28.      
  29.     INSERT INTO BAC1 (Billing_Account_Number, Billing_account_key, Parent_type, Active_flag, Deleted_flag, Created_date, Edw_update_date,
  30.     Billing_status, Source_system, migrartion_date) VALUES(008, 888, 'Bill', 'Yes', 'No', '29-jan-2019', '30-jan-2019', 'Completed', 'Laptop', '30-jan-2019');
  31.      
  32.      
  33.     INSERT INTO BAC1 (Billing_Account_Number, Billing_account_key, Parent_type, Active_flag, Deleted_flag, Created_date, Edw_update_date,
  34.     Billing_status, Source_system, migrartion_date) VALUES(009, 999, 'Bill', 'Yes', 'No', '29-jan-2019', '30-jan-2019', 'Completed', 'Laptop', '30-jan-2019');
  35.      
  36.      
  37.     INSERT INTO BAC1 (Billing_Account_Number, Billing_account_key, Parent_type, Active_flag, Deleted_flag, Created_date, Edw_update_date,
  38.     Billing_status, Source_system, migrartion_date) VALUES(010, 1000, 'Bill', 'Yes', 'No', '29-jan-2019', '30-jan-2019', 'Completed', 'Laptop', '30-jan-2019');
  39.      
  40.     SELECT * FROM BAC1;
  41.      
  42.      
  43.     CREATE TABLE BAC_asset_relation (Billing_Account_Number INT, Asset_Number INT, Asset_Status CHAR(20));
  44.      
  45.     INSERT INTO BAC_asset_relation (Billing_Account_Number, Asset_Number, Asset_Status) VALUES(001,00100,'Paid');
  46.      
  47.     INSERT INTO BAC_asset_relation (Billing_Account_Number, Asset_Number, Asset_Status) VALUES(002,00200,'Paid');
  48.      
  49.     INSERT INTO BAC_asset_relation (Billing_Account_Number, Asset_Number, Asset_Status) VALUES(003,00300,'Paid');
  50.      
  51.     INSERT INTO BAC_asset_relation (Billing_Account_Number, Asset_Number, Asset_Status) VALUES(004,00400,'Paid');
  52.      
  53.     INSERT INTO BAC_asset_relation (Billing_Account_Number, Asset_Number, Asset_Status) VALUES(005,00500,'Unpaid');
  54.      
  55.     INSERT INTO BAC_asset_relation (Billing_Account_Number, Asset_Number, Asset_Status) VALUES(006,00600,'Paid');
  56.      
  57.     INSERT INTO BAC_asset_relation (Billing_Account_Number, Asset_Number, Asset_Status) VALUES(007,00700,'Unpaid');
  58.      
  59.     SELECT * FROM BAC_asset_relation;
  60.      
  61.      
  62.     SELECT BAC1.Billing_Account_Number,BAC1.Billing_account_key, BAC1.Parent_type, BAC1.Active_flag, BAC1.Deleted_flag,
  63.     BAC1.Created_date, BAC1.Edw_update_date, BAC1.Billing_status, BAC1.Source_system,
  64.     BAC1.migrartion_date, BAC_asset_relation.Asset_Number, BAC_asset_relation.Asset_Status
  65.     FROM BAC1 INNER JOIN BAC_asset_relation ON BAC1.Billing_Account_Number=BAC_asset_relation.Billing_Account_Number ORDER BY BAC1.Billing_Account_Number ASC;
  66.      
  67.     SELECT BAC1.Billing_Account_Number,BAC1.Billing_account_key, BAC1.Parent_type, BAC1.Active_flag, BAC1.Deleted_flag,
  68.     BAC1.Created_date, BAC1.Edw_update_date, BAC1.Billing_status, BAC1.Source_system,
  69.     BAC1.migrartion_date, BAC_asset_relation.Asset_Number, BAC_asset_relation.Asset_Status
  70.     FROM BAC1 LEFT JOIN BAC_asset_relation ON BAC1.Billing_Account_Number=BAC_asset_relation.Billing_Account_Number ORDER BY BAC1.Billing_Account_Number DESC;
  71.      
  72.     ALTER TABLE BAC1 ADD Asset_Status CHAR(20);
  73.      
  74.     SELECT BAC1.Billing_Account_Number,BAC1.Billing_account_key, BAC1.Parent_type, BAC1.Active_flag, BAC1.Deleted_flag,
  75.     BAC1.Created_date, BAC1.Edw_update_date, BAC1.Billing_status, BAC1.Source_system,
  76.     BAC1.migrartion_date, BAC_asset_relation.Asset_Number, BAC_asset_relation.Asset_Status, BAC1.Asset_Status
  77.     FROM BAC1 INNER JOIN BAC_asset_relation ON BAC1.Billing_Account_Number=BAC_asset_relation.Billing_Account_Number ORDER BY BAC1.Billing_Account_Number ASC;
  78.      
  79.      
  80. SELECT * FROM BAC1;
  81. SELECT * FROM BAC_asset_relation;
  82. DELETE FROM BAC1 WHERE BAC1.Asset_Status='Unpaid';
  83.  
  84.  
  85.     MERGE INTO BAC1 e    
  86.     USING BAC_asset_relation h
  87.     ON (e.Billing_Account_Number = h.Billing_Account_Number)
  88.   WHEN MATCHED THEN
  89.     UPDATE SET e.Asset_Status = h.Asset_Status;



  • Recent Pastes