Create view not working

  • Resolved colmanbyrne

    (@colmanbyrne)


    5 years, 5 months ago

    Hi Trying to create a view to use as a lookup , SQL runs ok , no errors , but no view created in data explorer , I want to return the single string as a lookup in a different table , rather than just either firstname or surname

    CREATE VIEW memberfullname AS SELECT iwai_members.id, iwai_members.firstname AS memberfirstname, ìwai_members.surname AS membersurname, CONCAT(ìwai_members.firstname, ‘ ‘, iwai_members.surname) as membername FROM iwai_members;

    table create sql , this table already exists

    CREATE TABLE iwai_members (
    id int(10) NOT NULL AUTO_INCREMENT,
    uid varchar(50) NOT NULL,
    surname varchar(40) NOT NULL,
    firstname varchar(40) NOT NULL,
    prefix varchar(4) DEFAULT NULL,
    partner varchar(30) DEFAULT NULL,
    initial varchar(15) DEFAULT NULL,
    org_nam varchar(50) DEFAULT NULL,
    a1 varchar(40) DEFAULT NULL,
    a2 varchar(40) DEFAULT NULL,
    town varchar(40) DEFAULT NULL,
    county varchar(40) DEFAULT NULL,
    post_code varchar(40) DEFAULT NULL,
    country_code varchar(4) DEFAULT NULL,
    post_sort varchar(20) DEFAULT NULL,
    tel varchar(30) DEFAULT NULL,
    mob varchar(30) DEFAULT NULL,
    email1 varchar(50) DEFAULT NULL,
    email2 varchar(50) DEFAULT NULL,
    email_status char(1) DEFAULT ‘Y’,
    sms_status char(1) DEFAULT ‘N’,
    vessel_nam varchar(30) DEFAULT NULL,
    mem_cat varchar(4) DEFAULT NULL,
    mem_year int(10) DEFAULT NULL,
    mem_fee decimal(10,0) DEFAULT NULL,
    mem_fee_curr varchar(4) DEFAULT NULL,
    mem_status varchar(1) DEFAULT NULL,
    pay_method varchar(4) DEFAULT NULL,
    dat_joined date DEFAULT NULL,
    dat_renewed date DEFAULT NULL,
    dat_card_issued datetime DEFAULT NULL,
    dat_dd_issued date DEFAULT NULL,
    dat_cancel date DEFAULT NULL,
    brno1 int(10) DEFAULT NULL,
    brno2 int(10) DEFAULT NULL,
    notes varchar(255) DEFAULT NULL,
    gift_aid char(1) DEFAULT NULL,
    role char(2) DEFAULT ‘U’,
    login_status char(1) DEFAULT ‘Y’,
    d_login datetime DEFAULT NULL,
    d_fail datetime DEFAULT NULL,
    prev_mem_status char(1) DEFAULT NULL,
    origin char(1) DEFAULT ‘W’,
    d_created datetime DEFAULT NULL,
    u_created int(10) DEFAULT NULL,
    d_amended datetime DEFAULT NULL,
    u_amended int(10) DEFAULT NULL,
    tag1 varchar(40) DEFAULT NULL,
    tag2 varchar(40) DEFAULT NULL,
    fail_login_count int(10) DEFAULT NULL,
    last_data_notice datetime DEFAULT NULL,
    PRIMARY KEY (id)
    ) ENGINE=InnoDB AUTO_INCREMENT=6846 DEFAULT CHARSET=utf8

Viewing 2 replies - 1 through 2 (of 2 total)
  • Plugin Author Passionate Programmer Peter

    (@peterschulznl)

    5 years, 5 months ago

    Hi @colmanbyrne,

    To execute SQL from a script file, your SQL statement must end with a semi colon directly followed by a new line. Can you please check if the new line character is there?

    Thanks,
    Peter

    Thread Starter colmanbyrne

    (@colmanbyrne)

    5 years, 5 months ago

    Hi Peter

    Ok semicolon was there , but not new line fixed this but then getting error

    ERROR: Import wpda_iwai_create_views.sql failed [check import file]

    But then I noticed above , did not see it in my text editor

    wrong table name ìwai_members , should be iwai_members very subtle for a human , but a big difference for computers ! , worked once I fixed this

    Thanks for support

Viewing 2 replies - 1 through 2 (of 2 total)

The topic ‘Create view not working’ is closed to new replies.