Reports based on Cross Subject areas that include Custom Subject Areas tend to fail with "Failed to load(Odbc driver returned an error (SQLExecDirectW)." (Doc ID 1967591.1)

Last updated on FEBRUARY 01, 2017

Applies to:

Oracle Fusion Common CRM Cloud Service - Version 11.1.8.0.0 and later
Information in this document applies to any platform.

Symptoms

On : 11.1.8.0.0 version, Technology Management - Reports/Analytics

When attempting to run a report with a similar Logical SQL, it fails with the errors below:

SELECT AAA."Customer Name" saw_0,
  AAA."Extension Attribute LOV 003" saw_1,
  AAA."Employee Name" saw_2,
  AAA."Opportunity Name" saw_3,
  AAA."Revenue Expect Amt" saw_4,
  AAA."Extension Attribute Character 003" saw_5,
  AAA."Extension Attribute Character 015" saw_6,
  AAA."Sales Stage Name" saw_7,
  BBB."Lead Name" saw_8,
  BBB."Product Name" saw_9,
  BBB."Extension Attribute LOV 011" saw_10,
  BBB."Full Name"
  ||'-'
  || BBB."Email Address" saw_11,
  BBB."Email Address" saw_12,
  BBB."Contact Phone#" saw_13,
  BBB."Extension Attribute Character 032"
  || '-'
  ||BBB."Extension Attribute Character 033" saw_14,
  BBB."Extension Attribute Character 033" saw_15,
  BBB."Extension Attribute Character 034" saw_16,
  CCC."AddDetails_c" saw_17,
  CCC."CreationDate" saw_18,
  CCC."FinanceInvoice_c" saw_19,
  CCC."Invoice_c_" saw_20,
  CCC."InvoiceAmount_c" saw_21,
  CCC."InvoiceDate_c" saw_22,
  CCC."InvoiceDesc_c" saw_23,
  CCC."InvoiceName_c" saw_24
FROM
  (SELECT "Sales - CRM Pipeline"."Customer Organization Extension"."Extension Attribute LOV 003",
  "Sales - CRM Pipeline"."Customer"."Customer Name",
  "Sales - CRM Pipeline"."Customer"."Customer Row ID",
  "Sales - CRM Pipeline"."Employee"."Employee Name",
  "Sales - CRM Pipeline"."Opportunity Extension"."Extension Attribute Character 003",
  "Sales - CRM Pipeline"."Opportunity Extension"."Extension Attribute Character 015",
  "Sales - CRM Pipeline"."Opportunity"."Opportunity Name",
  "Sales - CRM Pipeline"."Opportunity"."Owner Name",
  "Sales - CRM Pipeline"."Opportunity"."Sales Stage Name",
  "Sales - CRM Pipeline"."Product"."Product Name",
  "Sales - CRM Pipeline"."Revenue"."Revenue Base Revn Amt",
  "Sales - CRM Pipeline"."Revenue"."Revenue Expect Amt",
  "Sales - CRM Pipeline"."Pipeline Detail Facts"."# of Products"
  FROM "Sales - CRM Pipeline"
  ) AAA,
  (SELECT "Sales - CRM Pipeline"."Contact Extension"."Extension Attribute Character 032",
  "Sales - CRM Pipeline"."Contact Extension"."Extension Attribute Character 033",
  "Sales - CRM Pipeline"."Contact Extension"."Extension Attribute Character 034",
  "Sales - CRM Pipeline"."Contact"."Contact Phone#",
  "Sales - CRM Pipeline"."Contact"."Email Address",
  "Sales - CRM Pipeline"."Contact"."Full Name",
  "Sales - CRM Pipeline"."Customer"."Customer Row ID",
  "Sales - CRM Pipeline"."Customer"."Customer Name",
  "Sales - CRM Pipeline"."Lead Extension"."Extension Attribute LOV 010",
  "Sales - CRM Pipeline"."Lead Extension"."Extension Attribute LOV 011",
  "Sales - CRM Pipeline"."Lead"."Lead Name",
  "Sales - CRM Pipeline"."Lead Facts"."# of Leads",
  "Sales - CRM Pipeline"."Product"."Product Name"
  FROM "Sales - CRM Pipeline"
  ) BBB,
  (SELECT "custExt722883a0_de85_4ec9_8ef9_6e6137b8578b"."Invoice_c"."AddDetails_c",
  "custExt722883a0_de85_4ec9_8ef9_6e6137b8578b"."Invoice_c"."CreationDate",
  "custExt722883a0_de85_4ec9_8ef9_6e6137b8578b"."Invoice_c"."FinanceInvoice_c",
  "custExt722883a0_de85_4ec9_8ef9_6e6137b8578b"."Invoice_c"."Invoice_c_",
  "custExt722883a0_de85_4ec9_8ef9_6e6137b8578b"."Invoice_c"."InvoiceAmount_c",
  "custExt722883a0_de85_4ec9_8ef9_6e6137b8578b"."Invoice_c"."InvoiceDate_c",
  "custExt722883a0_de85_4ec9_8ef9_6e6137b8578b"."Invoice_c"."InvoiceDesc_c",
  "custExt722883a0_de85_4ec9_8ef9_6e6137b8578b"."Invoice_c"."InvoiceName_c",
  "custExt722883a0_de85_4ec9_8ef9_6e6137b8578b"."Invoice_c"."ScheduledInvoiceDate_c",
  "custExt722883a0_de85_4ec9_8ef9_6e6137b8578b"."Invoice_c"."Void_c",
  "custExt722883a0_de85_4ec9_8ef9_6e6137b8578b"."Opportunity0"."OpportunitiesName",
  "custExt722883a0_de85_4ec9_8ef9_6e6137b8578b"."Organization0"."OrganizationName"
  FROM "custExt722883a0_de85_4ec9_8ef9_6e6137b8578b"
  ) CCC
WHERE AAA."Customer Row ID" = BBB."Customer Row ID"
AND AAA."Customer Name" = CCC."OrganizationName"



ERROR
-----------------------

Failed to load(Odbc driver returned an error (SQLExecDirectW).


]]
[2015-01-09T09:37:32.92+00:00] [OracleBIServerComponent] [TRACE:2] [] [] [ecid: 00533JbtX8i3z035Rnh8id0003I60004ha,0:1:3:1:40:3] [tid: 47321940] [messageid: USER-23] [requestid: 32c9004a] [sessionid: 32c90000] [username: someemail@xyz.com] -------------------- General Query Info: [[
Repository: Star, Subject Area: Core, Presentation: Sales - CRM Pipeline

]]
[2015-01-09T09:37:32.101+00:00] [OracleBIServerComponent] [TRACE:2] [] [] [ecid: 00533JbtX8i3z035Rnh8id0003I60004ha,0:1:3:1:40:5] [tid: 47321940] [messageid: USER-52] [requestid: 32c9004a] [sessionid: 32c90000] [username: someemail@xyz.com] -------------------- Expression '# of Opportunities:[DAggr(Fact - CRM - Revenue.# of Opportunities by [ Dim - Opportunity Extension.Extension Attribute Character 003, Dim - Opportunity Extension.Extension Attribute Character 015, Dim - Item.Product Name, Dim - Customer.Customer Name, Dim - Opportunity.Opportunity Name, Dim - Opportunity.Sales Stage Name, Dim - Employee.Employee Name, Dim - Contact.Email Address, Dim - Contact.Full Name, Dim - Revenue.Revenue Expect Amt, Dim - Customer Organization Extension.Extension Attribute LOV 003] )]' converted to NULL because [nQSError: 14020] None of the fact tables are compatible with the query request # of Opportunities:[DAggr(Fact - CRM - Revenue.# of Opportunities by [ Dim - Opportunity Extension.Extension Attribute Character 003, Dim - Opportunity Extension.Extension Attribute Character 015, Dim - Item.Product Name, Dim - Customer.Customer Name, Dim - Opportunity.Opportunity Name, Dim - Opportunity.Sales Stage Name, Dim - Employee.Employee Name, Dim - Contact.Email Address, Dim - Contact.Full Name, Dim - Revenue.Revenue Expect Amt, Dim - Customer Organization Extension.Extension Attribute LOV 003] )]. [nQSError: 14081] You may be able to evaluate this query if you remove one of the following column references: Dim - Contact.Email Address, Dim - Contact.Full Name, Dim - Customer Organization Extension.Extension Attribute LOV 003, Dim - Customer.Customer Name, Dim - Employee.Employee Name, Dim - Item.Product Name, Dim - Opportunity Extension.Extension Attribute Character 003, Dim - Opportunity Extension.Extension Attribute Character 015, Dim - Opportunity.Opportunity Name, Dim - Opportunity.Sales Stage Name, Dim - Revenue.Revenue Expect Amt.
[2015-01-09T09:37:32.129+00:00] [OracleBIServerComponent] [TRACE:2] [] [] [ecid: 00533JbtX8i3z035Rnh8id0003I60004ha,0:1:3:1:40:5] [tid: 47321940] [messageid: USER-18] [requestid: 32c9004a] [sessionid: 32c90000] [username: someemail@xyz.com] -------------------- Sending query to database named oracle.apps.crm.model.analytics.applicationModule.CrmAnalyticsAM_CrmAnalyticsAMLocal (id: SQLBypass Gateway), connection pool named Connection Pool, logical request hash fa509fb5, physical request hash dbadf2d8: [[



STEPS
-----------------------
The issue can be reproduced at will with the following steps:
1. Create a report using the logical sql above
2. Run the report

Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms