You can translate the question and the replies:

Joining JDBC and ODBC base views, fail execution (ODBC error)

I am doing the first course of the Denodo Developer Certification. I am trying to join (inner join, any type of join method) bv_wo_customer table, from an Oracle-JDBC based source, with bb_cc_service_call, from an MS Access-ODBC based source, by common customer_id field. Both individually can be queried with VQL without issues (MS Access is 64 bit odbc driver based as required by my VDP version). When I try to execute the join derived view, the result is: Finished with error: Error executing query. Total time 0.112 seconds. DV_CUSTOMER_CALLS [PROJECTION] [ERROR] DV_CUSTOMER_CALLS [JOIN] [ERROR] BV_CC_SERVICE_CALL [BASE] [ERROR] SERVICE_CALL [ODBC WRAPPER] [ERROR] Received exception with message 'Error obtaining some values for column(s): 'other_details'' SELECT t0.call_id, t0.location_id, t0.problem_code, t0.status_code, t0.date_ticket_issued, t0.date_call_received, t0.other_details, t0.caller_phone, t0.case_id, t0.agent_id, t0.customer_code FROM `C:\Users\Pablo\Downloads\00-SOFTWARE\calltracking.mdb`.service_call t0 WHERE t0.customer_code IS NOT NULL This is a field from the access view, that does not cause any issue if I execute the base view directly. The complete trace is: QUERY: SELECT * FROM dv_customer_calls CONTEXT ('i18n'='es_euro', 'cache_wait_for_load'='true') TRACE EXECUTION PLAN ( name = Execution startTime = Mon Nov 20 20:42:31 795 CET 2017 endTime = Mon Nov 20 20:42:31 907 CET 2017 responseTime = - numRows = 0 state = ERROR completed = false waitingTime = 0 STATIC OPTIMIZATION ( staticOptimized = false staticOptimizationTime = 8 staticOptimizationStart = Mon Nov 20 20:42:31 796 CET 2017 staticOptimizationEnd = Mon Nov 20 20:42:31 804 CET 2017 ) PROJECTION PLAN ( name = dv_customer_calls database = training01 startTime = Mon Nov 20 20:42:31 809 CET 2017 endTime = Mon Nov 20 20:42:31 907 CET 2017 responseTime = - numRows = 0 state = ERROR completed = false fields = [id, customer_code_internal, first_name, last_name, email, phone, main_address, main_postcode, main_country, registered_date, call_id, location_id, problem_code, status_code, date_ticket_issued, date_call_received, other_details, caller_phone, case_id, agent_id, customer_code] search conditions = [] filter conditions = [] orderByFields = [] ordered = false numOfFilteredTuples = 0 numOfDuplicatedTuples = 0 numOfSwappedTuples = 0 swapping = false memoryLimitReached = false projectedFields = [bv_wo_customer.id AS id, bv_wo_customer.customer_code_internal AS customer_code_internal, bv_wo_customer.first_name AS first_name, bv_wo_customer.last_name AS last_name, bv_wo_customer.email AS email, bv_wo_customer.phone AS phone, bv_wo_customer.main_address AS main_address, bv_wo_customer.main_postcode AS main_postcode, bv_wo_customer.main_country AS main_country, bv_wo_customer.registered_date AS registered_date, bv_cc_service_call.call_id AS call_id, bv_cc_service_call.location_id AS location_id, bv_cc_service_call.problem_code AS problem_code, bv_cc_service_call.status_code AS status_code, bv_cc_service_call.date_ticket_issued AS date_ticket_issued, bv_cc_service_call.date_call_received AS date_call_received, bv_cc_service_call.other_details AS other_details, bv_cc_service_call.caller_phone AS caller_phone, bv_cc_service_call.case_id AS case_id, bv_cc_service_call.agent_id AS agent_id, bv_cc_service_call.customer_code AS customer_code] INNER JOIN PLAN ( name = _dv_customer_calls_customer_9674521866592213292726585794851952059446328220870497443431238_1129395420 database = training01 startTime = Mon Nov 20 20:42:31 809 CET 2017 endTime = Mon Nov 20 20:42:31 907 CET 2017 responseTime = - numRows = 0 state = ERROR completed = false fields = [bv_wo_customer.id, bv_wo_customer.customer_code_internal, bv_wo_customer.first_name, bv_wo_customer.last_name, bv_wo_customer.email, bv_wo_customer.phone, bv_wo_customer.main_address, bv_wo_customer.main_postcode, bv_wo_customer.main_country, bv_wo_customer.registered_date, bv_cc_service_call.call_id, bv_cc_service_call.location_id, bv_cc_service_call.problem_code, bv_cc_service_call.status_code, bv_cc_service_call.date_ticket_issued, bv_cc_service_call.date_call_received, bv_cc_service_call.other_details, bv_cc_service_call.caller_phone, bv_cc_service_call.case_id, bv_cc_service_call.agent_id, bv_cc_service_call.customer_code] search conditions = [bv_wo_customer.customer_code_internal is not null , bv_cc_service_call.customer_code is not null ] filter conditions = [] orderByFields = [] ordered = false numOfFilteredTuples = 0 numOfDuplicatedTuples = 0 numOfSwappedTuples = 0 swapping = false type = HASH fstJoinFields = [customer_code_internal] sndJoinFields = [customer_code] joinOperators = [=] joinCondition = customer_code_internal = customer_code BASE PLAN ( name = bv_wo_customer database = training01 startTime = Mon Nov 20 20:42:31 810 CET 2017 endTime = Mon Nov 20 20:42:31 853 CET 2017 responseTime = Mon Nov 20 20:42:31 837 CET 2017 numRows = 2856 state = OK completed = true fields = [id, customer_code_internal, first_name, last_name, email, phone, main_address, main_postcode, main_country, registered_date] search conditions = [customer_code_internal is not null ] filter conditions = [] ordered = false numOfFilteredTuples = 0 numOfDuplicatedTuples = 0 numOfSwappedTuples = 0 swapping = false JDBC WRAPPER ( name = customer database = training01 startTime = Mon Nov 20 20:42:31 810 CET 2017 endTime = Mon Nov 20 20:42:31 853 CET 2017 responseTime = Mon Nov 20 20:42:31 838 CET 2017 numRows = 2856 state = OK completed = true searchConditions = [customer_code_internal is not null ] orderByFields = [] projectedFields = [id, customer_code_internal, first_name, last_name, email, phone, main_address, main_postcode, main_country, registered_date] additionalSubPlans = 0 additionalErroneousSubPlans = 0 JDBC ROUTE ( name = customer#0 datasource = ds_web_orders datasource database = training01 startTime = Mon Nov 20 20:42:31 811 CET 2017 endTime = Mon Nov 20 20:42:31 852 CET 2017 responseTime = Mon Nov 20 20:42:31 838 CET 2017 numRows = 2856 state = OK completed = true SQLSentence = SELECT t0.ID, t0.CUSTOMER_CODE_INTERNAL, t0.FIRST_NAME, t0.LAST_NAME, t0.EMAIL, t0.PHONE, t0.MAIN_ADDRESS, t0.MAIN_POSTCODE, t0.MAIN_COUNTRY, t0.REGISTERED_DATE FROM WEBSITE_SYS.CUSTOMER t0 WHERE t0.CUSTOMER_CODE_INTERNAL IS NOT NULL parameters = [] SQLSentence info = DBUri = jdbc:oracle:thin:@localhost:1521:xe userName = WEBSITE_SYS connectionTime = 21 cachedConnection = false ) ) ) BASE PLAN ( name = bv_cc_service_call database = training01 startTime = Mon Nov 20 20:42:31 811 CET 2017 endTime = Mon Nov 20 20:42:31 907 CET 2017 responseTime = Mon Nov 20 20:42:31 816 CET 2017 numRows = 2512 state = ERROR completed = false fields = [call_id, location_id, problem_code, status_code, date_ticket_issued, date_call_received, other_details, caller_phone, case_id, agent_id, customer_code] search conditions = [customer_code is not null ] filter conditions = [] ordered = false numOfFilteredTuples = 0 numOfDuplicatedTuples = 0 numOfSwappedTuples = 0 swapping = false ODBC WRAPPER ( name = service_call database = training01 startTime = Mon Nov 20 20:42:31 811 CET 2017 endTime = Mon Nov 20 20:42:31 907 CET 2017 responseTime = Mon Nov 20 20:42:31 816 CET 2017 numRows = 2512 state = ERROR completed = false exception = Error obtaining some values for column(s): 'other_details' searchConditions = [customer_code is not null ] orderByFields = [] projectedFields = [call_id, location_id, problem_code, status_code, date_ticket_issued, date_call_received, other_details, caller_phone, case_id, agent_id, customer_code] additionalSubPlans = 0 additionalErroneousSubPlans = 0 JDBC ROUTE ( name = service_call#0 datasource = ds_call_center datasource database = training01 startTime = Mon Nov 20 20:42:31 811 CET 2017 endTime = Mon Nov 20 20:42:31 906 CET 2017 responseTime = Mon Nov 20 20:42:31 816 CET 2017 numRows = 2512 state = OK completed = true SQLSentence = SELECT t0.call_id, t0.location_id, t0.problem_code, t0.status_code, t0.date_ticket_issued, t0.date_call_received, t0.other_details, t0.caller_phone, t0.case_id, t0.agent_id, t0.customer_code FROM `C:\Users\Pablo\Downloads\00-SOFTWARE\calltracking.mdb`.service_call t0 WHERE t0.customer_code IS NOT NULL parameters = [] SQLSentence info = DBUri = jdbc:odbc:MS Access Database userName = phone connectionTime = 2 cachedConnection = true ) ) ) ) ) )
user
20-11-2017 14:35:15 -0500

1 Answer

Hi, I was able to reproduce your scenario. The error "Error obtaining some values for column(s): other_details" could have occurred, when the schema change in the underlying datasource is not updated in Virtual DataPort. In order to overcome this error, in Virtual DataPort Administration Tool, I would navigate to 'Edit' tab of **bv_service_call** baseview and click on 'Source refresh' button. For more information on Source refresh, you could refer the [Source Refresh](https://community.denodo.com/docs/html/browse/6.0/vdp/administration/creating_views/importing_data_sources_and_creating_base_views/source_refresh#source-refresh) section of Virtual DataPort Administration Guide. Hope this helps !!
Denodo Team
21-11-2017 06:35:18 -0500
You must sign in to add an answer. If you do not have an account, you can register here