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
)
)
)
)
)
)
