SQLize Online / PHPize Online  /  SQLtest Online

A A A
Share      Blog   Popular
Copy Format Clear
CREATE TABLE `f_search_address` ( `SKP_CLIENT` INT(11) NULL DEFAULT NULL, `SKP_ADDRESS` INT(11) NULL DEFAULT NULL, `CODE_ZIP` VARCHAR(300) NULL DEFAULT NULL, `SKP_REGION` INT(11) NULL DEFAULT NULL, `NAME_REGION` VARCHAR(125) NULL DEFAULT NULL, `NAME_DISTRICT` VARCHAR(765) NULL DEFAULT NULL, `NAME_LOCALITY` VARCHAR(765) NULL DEFAULT NULL, `NAME_TOWN` VARCHAR(765) NULL DEFAULT NULL, `NAME_TOWN_EXT` VARCHAR(150) NULL DEFAULT NULL, `NAME_STREET` VARCHAR(150) NULL DEFAULT NULL, `TEXT_BLOCK_NUMBER` VARCHAR(3000) NULL DEFAULT NULL, `TEXT_STREET_NUMBER` VARCHAR(25) NULL DEFAULT NULL, `TEXT_APARTMENT_NUMBER` VARCHAR(3000) NULL DEFAULT NULL, `CODE_STATUS` VARCHAR(300) NULL DEFAULT NULL, `DTIME_SOURCE_MODIFIED` DATETIME NULL DEFAULT NULL, `CODE_SOURCE_SYSTEM` VARCHAR(30) NULL DEFAULT NULL, `ID_SOURCE` VARCHAR(900) NULL DEFAULT NULL, `DATE_EFFECTIVE` DATE NULL DEFAULT NULL, `DTIME_INSERTED` DATETIME NULL DEFAULT NULL, `DTIME_UPDATED` DATETIME NULL DEFAULT NULL, `SK_PROC_INSERTED` BIGINT(20) NULL DEFAULT NULL, `SK_PROC_UPDATED` BIGINT(20) NULL DEFAULT NULL, `DTIME_INSERTED_ETL` DATETIME NULL DEFAULT NULL, INDEX `SX_SEARCH` (`TEXT_STREET_NUMBER`, `SKP_CLIENT`, `NAME_STREET`, `SKP_REGION`, `NAME_TOWN_EXT`) ) COLLATE='utf8_general_ci' ENGINE=MyISAM; CREATE TABLE `dc_client` ( `SKP_CLIENT` DOUBLE NULL DEFAULT NULL, `CODE_SOURCE_SYSTEM` VARCHAR(30) NULL DEFAULT NULL, `ID_SOURCE` VARCHAR(90) NULL DEFAULT NULL, `ID_PERSON` VARCHAR(30) NULL DEFAULT NULL, `ID_PERSON_CUID` DOUBLE NULL DEFAULT NULL, `SK_DATE_BIRTH` INT(11) NULL DEFAULT NULL, `SK_DATE_EMPLOYED_FROM` INT(11) NULL DEFAULT NULL, `SK_DATE_DEATH` INT(11) NULL DEFAULT NULL, `SK_CLIENT_DEMOGRAPHY` INT(11) NULL DEFAULT NULL, `CODE_INCOME_TYPE` VARCHAR(30) NULL DEFAULT NULL, `NAME_INCOME_TYPE` VARCHAR(765) NULL DEFAULT NULL, `NAME_FULL` VARCHAR(765) NULL DEFAULT NULL, `NAME_SEARCH` VARCHAR(765) NULL DEFAULT NULL, `NAME_FIRST` VARCHAR(240) NULL DEFAULT NULL, `NAME_LAST` VARCHAR(240) NULL DEFAULT NULL, `NAME_MIDDLE` VARCHAR(240) NULL DEFAULT NULL, `NAME_BORN` VARCHAR(240) NULL DEFAULT NULL, `NAME_BIRTH_PLACE` VARCHAR(240) NULL DEFAULT NULL, `TEXT_TITLE_BEFORE` VARCHAR(90) NULL DEFAULT NULL, `TEXT_TITLE_AFTER` VARCHAR(90) NULL DEFAULT NULL, `TEXT_OCCUPATION` VARCHAR(240) NULL DEFAULT NULL, `TEXT_IDENTITY_CARD_NUMBER` VARCHAR(240) NULL DEFAULT NULL, `CODE_GENDER` VARCHAR(30) NULL DEFAULT NULL, `AMT_INCOME_MAIN_OCCUPATION` DOUBLE NULL DEFAULT NULL, `CNT_CHILDREN` INT(11) NULL DEFAULT NULL, `CODE_STATUS` VARCHAR(30) NULL DEFAULT NULL, `DTIME_MODIF` DATETIME NULL DEFAULT NULL, `CODE_UNDESIRABLE` VARCHAR(30) NULL DEFAULT NULL, `NOTE_UNDESIRABLE` VARCHAR(240) NULL DEFAULT NULL, `DATE_UNDESIRABLE_FROM` DATE NULL DEFAULT NULL, `DATE_UNDESIRABLE_TO` DATE NULL DEFAULT NULL, `FLAG_CAR_OWNER` VARCHAR(3) NULL DEFAULT NULL, `FLAG_DELETED` VARCHAR(3) NULL DEFAULT NULL, `DATE_EFFECTIVE_INSERTED` DATE NULL DEFAULT NULL, `DATE_EFFECTIVE_UPDATED` DATE NULL DEFAULT NULL, `DTIME_INSERTED` DATETIME NULL DEFAULT NULL, `DTIME_UPDATED` DATETIME NULL DEFAULT NULL, `SK_PROC_INSERTED` BIGINT(20) NULL DEFAULT NULL, `SK_PROC_UPDATED` BIGINT(20) NULL DEFAULT NULL, `DTIME_INSERTED_ETL` DATETIME NULL DEFAULT NULL, INDEX `skp_client` (`SKP_CLIENT`), INDEX `date_effective` (`DTIME_INSERTED_ETL`) ) COLLATE='utf8_general_ci' ENGINE=MyISAM ; Select /*! SQL_CACHE */ skip_tracing.dc_client.skp_client, case when LENGTH(skip_tracing.dc_client.text_identity_card_number)>10 then 'XX' else skip_tracing.dc_client.text_identity_card_number end text_identity_card_number, skip_tracing.dc_client.NAME_FULL, skip_tracing.f_search_address.CODE_ZIP, skip_tracing.f_search_address.NAME_REGION, skip_tracing.f_search_address.NAME_DISTRICT, skip_tracing.f_search_address.NAME_TOWN_EXT, skip_tracing.f_search_address.NAME_STREET, skip_tracing.f_search_address.TEXT_STREET_NUMBER, case when skip_tracing.f_search_address.TEXT_BLOCK_NUMBER='XNA' then '' else skip_tracing.f_search_address.TEXT_BLOCK_NUMBER end TEXT_BLOCK_NUMBER, case when skip_tracing.f_search_address.TEXT_APARTMENT_NUMBER='XNA' then '' else skip_tracing.f_search_address.TEXT_APARTMENT_NUMBER end TEXT_APARTMENT_NUMBER, skip_tracing.f_search_address.CODE_STATUS from skip_tracing.f_search_address FORCE INDEX (SX_SEARCH) JOIN skip_tracing.dc_client ON skip_traing.dc_client.SKP_CLIENT = skip_tracing.f_search_address.SKP_CLIENT where skip_tracing.f_search_address.NAME_REGION='Санкт-Петербург' and match(skip_tracing.f_search_address.NAME_TOWN_EXT,skip_tracing.f_search_address.NAME_REGION,skip_tracing.f_search_address.NAME_STREET) against(' +Санкт-Петербург* +Петергофское*' in boolean mode) and skip_tracing.f_search_address.TEXT_STREET_NUMBER = '78' and skip_tracing.f_search_address.TEXT_BLOCK_NUMBER = '8' and skip_tracing.f_search_address.TEXT_APARTMENT_NUMBER = '6' order by skip_tracing.dc_client.skp_client
Stuck with a problem? Got Error? Ask ChatGPT!
Copy Clear