Oracle SQL with Joins not working

[pandabear_de]

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!

[Anil_Babu_Samineni]

How is the statement for Table2 table?

[zhadrakas]

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]

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]

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 · Accepted solution]

@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";

[pandabear_de]

@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