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

[AndyE]

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;

[balabhaskarqlik]

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.

[Qlik1_User1]

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

[AndyE]

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.

[Clever_Anjos]

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

I'm guessing you have some error in your SQL

[AndyE]

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

No SQL errors.

[_ylc]

what is the exact error in qlik?

I just tried and it's working for me.

[AndyE]

"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?

[_ylc]

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.

[AndyE]

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.
**********************************************************************************************