customers_v_new_SQL.sql

CREATE OR REPLACE VIEW "CUSTOMER_V" ("CUST_ID", "CUST_FIRST_NAME", "CUST_LAST_NAME", "CUST_GENDER", "CUST_YEAR_OF_BIRTH", "CUST_MARITAL_STATUS", "CUST_STREET_ADDRESS", 
  "CUST_POSTAL_CODE", "CUST_CITY", "CUST_CITY_ID", "CUST_STATE_PROVINCE", "CUST_STATE_PROVINCE_ID", "COUNTRY_ID", "CUST_MAIN_PHONE_NUMBER", "CUST_INCOME_LEVEL", 
  "CUST_CREDIT_LIMIT", "CUST_EMAIL", "CUST_TOTAL", "CUST_TOTAL_ID", "CUST_SRC_ID", "CUST_EFF_FROM", "CUST_EFF_TO", "CUST_VALID", "RECOMMENDATION") AS 
  select "CUST_ID","CUST_FIRST_NAME","CUST_LAST_NAME","CUST_GENDER","CUST_YEAR_OF_BIRTH","CUST_MARITAL_STATUS","CUST_STREET_ADDRESS","CUST_POSTAL_CODE","CUST_CITY",
    "CUST_CITY_ID","CUST_STATE_PROVINCE","CUST_STATE_PROVINCE_ID","COUNTRY_ID","CUST_MAIN_PHONE_NUMBER","CUST_INCOME_LEVEL","CUST_CREDIT_LIMIT","CUST_EMAIL",
	"CUST_TOTAL","CUST_TOTAL_ID","CUST_SRC_ID","CUST_EFF_FROM","CUST_EFF_TO","CUST_VALID", 
  (select c.cust_first_name || ' is ' || to_char(p.pred_probability * 100, '999.99') || '% likely to be interested in Y Box Games'
   from mluser1.cust_prediction p
   where p.cust_id = c.cust_id
   and p.prediction = 1
   and p.pred_probability > 0.8
  )
from sh.customers c