topic Re: Subquery in SQL Server gives "Field 'Id' not found" error in Connectivity & Data Prep

Subquery in SQL Server gives "Field 'Id' not found" error

AndyE — Tue, 28 Dec 2021 20:14:02 GMT

I'm connecting successfully to a Microsoft SQL Server database and am able to see results using simple join queries. When I add a subquery as a data field to a working join query, I get the error "Field 'Id' not found".

Actual samples below. Both samples work fine when using Visual Studio 2017 to run the same queries.
In Qlik, sample 1 works and sample 2 does not.
I saw posted somewhere that Qlik just passes the SQL directly through to SQL Server. However, the fact that I'm getting this error seems to indicate perhaps something more is going on.

Can anyone provide clues as to why this might be happening?
Thanks in advance.

Sample 1, working query:

[VerticalTraceView]: SELECT Id, RunId, Name, Requirement.Name as ReqName, VerticalTrace.ToRequirementId, FilterResult FROM RTM.dbo.Requirement as Requirement,RTM.dbo.VerticalTrace as VerticalTrace WHERE Requirement.Id=VerticalTrace.FromRequirementId;

Sample 2, non-working, generates error on Load data:

[VerticalTraceView]: SELECT Id, RunId, Name, (SELECT Requirement.Name from Requirement where Requirement.Id=VerticalTrace.ToRequirementId) as ReqName, VerticalTrace.FromRequirementId, FilterResult FROM RTM.dbo.Requirement as Requirement,RTM.dbo.VerticalTrace as VerticalTrace WHERE Requirement.Id=VerticalTrace.FromRequirementId;

Re: Subquery in SQL Server gives "Field 'Id' not found" error

BalaBhaskar_Qlik — Mon, 02 Mar 2020 10:09:58 GMT

The SQL format may not be fully supported by QlikSense.Check your 2nd query on deriving ReqName field, try the below options.

1. Try to fetch data into two tables, Use resident loads to join two tables.

2. Store data into two qvds, load qvds, fetch data from required fields, apply right, left joins.... or Concatenate them.

Re: Subquery in SQL Server gives "Field 'Id' not found" error

Qlik1_User1 — Mon, 02 Mar 2020 10:12:49 GMT

Sub queries may not work..try it with mapping load..by creating 2 different tables

Re: Subquery in SQL Server gives "Field 'Id' not found" error

AndyE — Wed, 04 Mar 2020 15:47:58 GMT

Thanks for your input. I guess the sql is not just passed through to SQL Server.

I wound up just creating views in SQL Server to solve the problem.

Re: Subquery in SQL Server gives "Field 'Id' not found" error

Clever_Anjos — Wed, 04 Mar 2020 16:25:47 GMT

Please run your SQL using a SQL client (not Qlik).

I'm guessing you have some error in your SQL

Re: Subquery in SQL Server gives "Field 'Id' not found" error

AndyE — Wed, 04 Mar 2020 22:21:42 GMT

"Both samples work fine when using Visual Studio 2017".

No SQL errors.

Re: Subquery in SQL Server gives "Field 'Id' not found" error

_ylc — Wed, 04 Mar 2020 22:51:52 GMT

what is the exact error in qlik?

I just tried and it's working for me.

Re: Subquery in SQL Server gives "Field 'Id' not found" error

AndyE — Wed, 04 Mar 2020 22:55:55 GMT

"Field 'Id' not found" is the exact error I get. When I remove the subquery the error goes away.

Do you have an example subquery that works that you could post?

Re: Subquery in SQL Server gives "Field 'Id' not found" error

_ylc — Wed, 04 Mar 2020 23:02:55 GMT

here's what I loaded:

select distinct

Orders.[Order ID],

Orders.[Order Date],

Orders.Region,

(SELECT Person from People$ where People$.Region=Orders.Region) as RegionName

from Orders$ as Orders, Returns$ as Returned

where Orders.[Order ID]=Returned.[Order ID];

Works fine on my side.

Re: Subquery in SQL Server gives "Field 'Id' not found" error

AndyE — Thu, 05 Mar 2020 16:43:40 GMT

Below is the exact error dialog I get after clicking on Load Data in the Data Load Editor.
I can't spot any significant difference between your query and mine, yet mine fails and yours works.

Are you running the latest Qlik Sense Desktop?

All I do to get rid of the error, is comment out the subquery line.
Again, this query works fine in VS 2017.

*********************************************
Started loading data

Connected

The following error occurred:

Field 'RequirementId' not found

The error occurred here:

SELECT distinct RequirementId, (select Requirement.Name from Requirement where Requirement.Id=Id-RequirementId) as ReqName, TestCaseId FROM RTM.dbo.HorizontalTrace

Data has not been loaded. Please correct the error and try loading again.
**********************************************************************************************

Re: Subquery in SQL Server gives "Field 'Id' not found" error

_ylc — Sun, 08 Mar 2020 20:16:26 GMT

I'm using QlikSense server (version Nov 2019).

Re: Subquery in SQL Server gives "Field 'Id' not found" error

rodneybarrett — Thu, 14 Apr 2022 17:23:43 GMT

Was there ever an answer to this. I am now gettting the same Field 'id' not found when accessing the nprinting postgres database. The id field is in the table plain as day.

Re: Subquery in SQL Server gives "Field 'Id' not found" error

rodneybarrett — Thu, 14 Apr 2022 17:33:03 GMT

nevermind. mine was something else. user error. 🙂