Skip to main content
Announcements
Qlik Introduces a New Era of Visualization! READ ALL ABOUT IT
cancel
Showing results for 
Search instead for 
Did you mean: 
pandabear_de
Luminary Alumni
Luminary Alumni

Oracle SQL with Joins not working

Hi,

I am trying to pull data from an Oracle database for which a tested connection is already available. This Oracle SQL also includes several joins, that I would like to leave in the SQL and not recreate the joins in Qlik.

I am getting a "Field 'field_a' not found' when loading the data"

How Do I fix it?
Based on my current tests, it looks like Qlik has an issue, that the joined table has a field with the same name. If I simply add two tables without common columns, I get no errors. (e.g. FROM "schemaA"."table1" c,  "schemaA"."table3" d) 

In this example, I can join table1 and table2 via the field 'field_a'

 

LIB CONNECT TO 'oracle_exmaple_connection';

LOAD field_a, 
	field_b
;
[tableFinal]:
SELECT 
	c."field_a" as "field_a", 
	c."field_b", 
FROM "schemaA"."table1" c 
	left join "schemaA"."table2" fm on fm."field_a" = c."field_a"
;

 

 

Thank you for your help!

Labels (3)
1 Solution

Accepted Solutions
Anil_Babu_Samineni

@pandabear_deThanks for your message. At the moment, Check simply with out Qlik script for now and see what it is returning

LIB CONNECT TO 'oracle_exmaple_connection';
[tableFinal]:
SELECT
c."field_a" as "field_a",
c."field_b",
FROM "schemaA"."table1" c
left join "schemaA"."table2" fm on fm."field_a" = c."field_a";

Best Anil, When applicable please mark the correct/appropriate replies as "solution" (you can mark up to 3 "solutions". Please LIKE threads if the provided solution is helpful

View solution in original post

6 Replies
Anil_Babu_Samineni

How is the statement for Table2 table?

Best Anil, When applicable please mark the correct/appropriate replies as "solution" (you can mark up to 3 "solutions". Please LIKE threads if the provided solution is helpful
zhadrakas
Specialist II
Specialist II

most of the time oracle SQL will convert the names to uppercase in QLIK syntax.

Try Load FIELD_A, FIELD_B in your presceding Load.

regards

tim

 

 

pandabear_de
Luminary Alumni
Luminary Alumni
Author

Hi,

yes you are correct. In my real case this is the case. The issue is not related to this oversigt.. Will update the original Post.

Jakob

pandabear_de
Luminary Alumni
Luminary Alumni
Author

In the moment table2 is only included in the join. I tried to keep things as simple as possible during problem solving.
An example would be to add fm.field_a as a field in the select.

Anil_Babu_Samineni

@pandabear_deThanks for your message. At the moment, Check simply with out Qlik script for now and see what it is returning

LIB CONNECT TO 'oracle_exmaple_connection';
[tableFinal]:
SELECT
c."field_a" as "field_a",
c."field_b",
FROM "schemaA"."table1" c
left join "schemaA"."table2" fm on fm."field_a" = c."field_a";

Best Anil, When applicable please mark the correct/appropriate replies as "solution" (you can mark up to 3 "solutions". Please LIKE threads if the provided solution is helpful
pandabear_de
Luminary Alumni
Luminary Alumni
Author

@Anil_Babu_Samineni - Thanks for your help!

I found the problem im my code... at the end of the code I included a WHERE condition. The column name was missing the prefix 'c.' as the others had in this example. 

After adding the prefix the code works now. Sorry for missing it. Thanks for your help!

Jakob