QlikView sourcing from Oracle: “Error: Field not found”

QlikView is case-sensitive on Field/Column names.

In Oracle, object names are stored in uppercase by default and it is not case-sensitive too. It can be made case-sensitive by explicitly using quotes, though.

Error: “Field not found – Field_Name”

becomes frequent error as the result of conflict between two technologies.

One such situation:

Executed below query and prompted with “Field not found” error message.

Load
sqlstr as Field_Name;
SQL Select
XXXXXXXXXXXXXX as sqlstr
From dual;

Cause:

sqlstr is stored in Oracle as SQLSTR. Since QlikView is case-sensitive on Field name, it throws this error.

To get a better picture of this, change preceding load as ” Load *; ” to see the way Oracle returns the field name.

Load
*;
SQL Select
XXXXXXXXXXXXXX as sqlstr
From dual;

Below image from QlikView data model (Ctrl + T) while using ” Loading *; “. Now it is obvious that Oracle returns the field name in uppercase.

Solution:

Change the field name to match Oracle way (Uppercase).

Data Blending

Data Blending allows using data from different data sources in a view. The view is nothing but a chart in Tableau.

Basically, Blending executes a query on each data source and joins the aggregated results.

Tableau Blending classifies Data sources into: Primary and Secondary Data Sources.

Primary Data Source (PDS) – Only one PDS per view. There are some recommendations and mandates for it. They are:

• If any one of the data sources is cube then that should be the PDS.
• Smaller and Faster data source as PDS gives better-performing View.
• If your sources don’t fall under any of the above two categories then whichever source you first use in your view becomes the PDS

Secondary Data Source (SDS) – There could be any number of SDS.

Left Outer Join is default join between PDS and SDS but still it can be changed to left, right and inner joins.

Note: Full outer join is not possible using blending.

Example Use Case:

Sales data collected in an Oracle database and Sales Goal data in an Excel spreadsheet. To compare actual sales to target sales, you can blend the data based on common dimensions to get access to the Sales Goal measure.

QlikView Error Message – “Error: SQL##f – SqlState: S0022, ErrorCode: 904”

SQL##f – SqlState: S0022, ErrorCode: 904, ErrorMsg: [Oracle][ODBC][Ora]ORA-00904: “Field_Name”: invalid identifier

Solution:

Enclose all the field names with double quotes.

Example:

Load *;

SQL Select “Field_Name”, “Field_Name1”, “Field_Name2” from dbo.Table_Name;