Edited SQL create view statements

• Add alias for column id:
  • Replace id, → id idCol,

/* CREATE VIEW statements for SuiteCRM tables with _vw suffix */

-- View for accounts table
CREATE VIEW accounts_vw AS
SELECT 
    id idCol,
    name,
    date_entered,
    date_modified,
    modified_user_id,
    created_by,
    description,
    deleted,
    assigned_user_id,
    account_type,
    industry,
    annual_revenue,
    phone_fax,
    billing_address_street,
    billing_address_city,
    billing_address_state,
    billing_address_postalcode,
    billing_address_country,
    rating,
    phone_office,
    phone_alternate,
    website,
    ownership,
    employees,
    ticker_symbol,
    shipping_address_street,
    shipping_address_city,
    shipping_address_state,
    shipping_address_postalcode,
    shipping_address_country,
    parent_id,
    sic_code,
    campaign_id
FROM accounts;

-- View for accounts_contacts table
CREATE VIEW accounts_contacts_vw AS
SELECT 
    id idCol,
    contact_id,
    account_id,
    date_modified,
    deleted
FROM accounts_contacts;

-- View for accounts_opportunities table
CREATE VIEW accounts_opportunities_vw AS
SELECT 
    id idCol,
    opportunity_id,
    account_id,
    date_modified,
    deleted
FROM accounts_opportunities;

-- View for aop_case_updates table
CREATE VIEW aop_case_updates_vw AS
SELECT 
    id idCol,
    name,
    date_entered,
    date_modified,
    modified_user_id,
    created_by,
    description,
    deleted,
    assigned_user_id,
    case_id,
    contact_id,
    internal
FROM aop_case_updates;

-- View for aos_products_quotes table
CREATE VIEW aos_products_quotes_vw AS
SELECT 
    id idCol,
    name,
    date_entered,
    date_modified,
    modified_user_id,
    created_by,
    description,
    deleted,
    assigned_user_id,
    currency_id,
    part_number,
    item_description,
    number,
    product_qty,
    product_cost_price,
    product_cost_price_usdollar,
    product_list_price,
    product_list_price_usdollar,
    product_discount,
    product_discount_usdollar,
    product_discount_amount,
    product_discount_amount_usdollar,
    discount,
    product_unit_price,
    product_unit_price_usdollar,
    vat_amt,
    vat_amt_usdollar,
    product_total_price,
    product_total_price_usdollar,
    vat,
    parent_type,
    parent_id,
    product_id,
    group_id
FROM aos_products_quotes;

-- View for aos_quotes table
CREATE VIEW aos_quotes_vw AS
SELECT 
    id idCol,
    name,
    date_entered,
    date_modified,
    modified_user_id,
    created_by,
    description,
    deleted,
    assigned_user_id,
    approval_issue,
    billing_account_id,
    billing_contact_id,
    billing_address_street,
    billing_address_city,
    billing_address_state,
    billing_address_postalcode,
    billing_address_country,
    shipping_address_street,
    shipping_address_city,
    shipping_address_state,
    shipping_address_postalcode,
    shipping_address_country,
    expiration,
    number,
    opportunity_id,
    template_ddown_c,
    total_amt,
    total_amt_usdollar,
    subtotal_amount,
    subtotal_amount_usdollar,
    discount_amount,
    discount_amount_usdollar,
    tax_amount,
    tax_amount_usdollar,
    shipping_amount,
    shipping_amount_usdollar,
    shipping_tax,
    shipping_tax_amt,
    shipping_tax_amt_usdollar,
    total_amount
FROM aos_quotes;

-- View for cases table
CREATE VIEW cases_vw AS
SELECT 
    id idCol,
    name,
    date_entered,
    date_modified,
    modified_user_id,
    created_by,
    description,
    deleted,
    assigned_user_id,
    case_number,
    type,
    status,
    priority,
    resolution,
    work_log,
    account_id,
    state,
    contact_created_by_id
FROM cases;

-- View for contacts table
CREATE VIEW contacts_vw AS
SELECT 
    id idCol,
    date_entered,
    date_modified,
    modified_user_id,
    created_by,
    description,
    deleted,
    assigned_user_id,
    salutation,
    first_name,
    last_name,
    title,
    photo,
    department,
    do_not_call,
    phone_home,
    phone_mobile,
    phone_work,
    phone_other,
    phone_fax,
    lawful_basis,
    date_reviewed,
    lawful_basis_source,
    primary_address_street,
    primary_address_city,
    primary_address_state,
    primary_address_postalcode,
    primary_address_country,
    alt_address_street,
    alt_address_city,
    alt_address_state,
    alt_address_postalcode,
    alt_address_country,
    assistant,
    assistant_phone,
    lead_source,
    reports_to_id,
    birthdate,
    campaign_id,
    joomla_account_id
FROM contacts;

-- View for contacts_cases table
CREATE VIEW contacts_cases_vw AS
SELECT 
    id idCol,
    contact_id,
    case_id,
    contact_role,
    date_modified,
    deleted
FROM contacts_cases;

-- View for opportunities table
CREATE VIEW opportunities_vw AS
SELECT 
    id idCol,
    name,
    date_entered,
    date_modified,
    modified_user_id,
    created_by,
    description,
    deleted,
    assigned_user_id,
    opportunity_type,
    campaign_id,
    lead_source,
    amount,
    amount_usdollar,
    currency_id,
    date_closed,
    next_step,
    sales_stage,
    probability
FROM opportunities;