Qlik Community

Qlik Sense Advanced Authoring

Discussion board where members can learn more about Qlik Sense Advanced Authoring.

Announcements
BARC’s The BI Survey 19 makes it official. BI users love Qlik. GET REPORT
Cathalc
New Contributor III

SQL code works in SSMS but not in Qlik

HI all,

The following code works in SSMS but not in Qlik:

LIB CONNECT TO 'RALNHV (nhv_qlikadmin)';

PartInquiries:
SQL SELECT
[IssueDate]
,b.[PartNo]
,b.[Description]
,d.[SerialNo]
,d.[ReceivedQty]
,i.[TransactionType]
,g.[OrderNo]
,f.[TaskNo]
,c.[Priority]
,f.[TaskText]
,f.[CarriedOutText]
,f.[Description]
,f.[EstManHours]
,g.[PartNo] as AcModel
,g.[Reg]
,h.[Status]

-- Joining Date, PartNo, Priority and SerialNo:
FROM [RALNHV].[dbo].[sDemandPart] as a
LEFT JOIN [RALNHV].[dbo].[sPart] as b
ON a.[sPart_IDDemanded] = b.[ID]
lEFT JOIN [RALNHV].[dbo].[sPriority] as c
ON a.[sPriority_ID] = c.[ID]
LEFT JOIN [RALNHV].[dbo].[sOrderPartReceipt] as d
on a.[sOrderPartReceipt_ID] = d.[ID]

-- Joining the task Information:
LEFT JOIN [RALNHV].[dbo].[sDemand] as e
on a.[sDemand_ID] = e.[ID]
LEFT JOIN [RALNHV].[dbo].[sOrderTask] as f
on e.[sOrderTask_ID] = f.[ID]

--Joining the Order Information
LEFT JOIN [RALNHV].[dbo].[sOrder] as g
on f.[sOrder_ID] = g.[ID]

--Joining Status and TransactionType
LEFT JOIN [RALNHV].[dbo].[sDemandItemStatus] as h
on a.[sDemandItemStatus_IDLast] = h.[ID]
LEFT Join [RALNHV].[dbo].[sPartTransactionType] as i
on a.[sPartTransactionType_ID] = i.[ID]

-- To be checked what kind of WO can be left out of the table!
Where
a.IssueDate > Convert(datetime, '2016-01-01')
order by a.IssueDate

Error: 

The following error occurred:
Field names must be unique within table
 
Any ideas? I don't see any actions not supported by Qlik...
 
regards
Labels (2)
1 Solution

Accepted Solutions
Cathalc
New Contributor III

Re: SQL code works in SSMS but not in Qlik

Hi Erik,

thank you for your reply. I don't know what's happening because I can't even rename my columns. When I edit my code to this:

LIB CONNECT TO 'RALNHV (nhv_qlikadmin)';

PartInquiries:
SQL SELECT
a.[IssueDate]
,b.[PartNo]
,b.[Description] as Desc
,d.[SerialNo]
,d.[ReceivedQty]
,i.[TransactionType]
,g.[OrderNo]
,f.[TaskNo]
,c.[Priority]
,f.[TaskText]
,f.[CarriedOutText]
,f.[Description]
,f.[EstManHours]
,g.[PartNo] as AcModel
,g.[Reg]
,h.[Status]

....

 

I get the eroor: Connector reply error: ErrorSource: Microsoft OLE DB Provider for SQL Server, ErrorMsg: Incorrect syntax near the keyword 'Desc'.

View solution in original post

4 Replies
ErikWetterberg
Honored Contributor

Re: SQL code works in SSMS but not in Qlik

Hi,

You have got both b.[Description] and f.[Description]. Try renaming one of them.

Cathalc
New Contributor III

Re: SQL code works in SSMS but not in Qlik

Hi Erik,

thank you for your reply. I don't know what's happening because I can't even rename my columns. When I edit my code to this:

LIB CONNECT TO 'RALNHV (nhv_qlikadmin)';

PartInquiries:
SQL SELECT
a.[IssueDate]
,b.[PartNo]
,b.[Description] as Desc
,d.[SerialNo]
,d.[ReceivedQty]
,i.[TransactionType]
,g.[OrderNo]
,f.[TaskNo]
,c.[Priority]
,f.[TaskText]
,f.[CarriedOutText]
,f.[Description]
,f.[EstManHours]
,g.[PartNo] as AcModel
,g.[Reg]
,h.[Status]

....

 

I get the eroor: Connector reply error: ErrorSource: Microsoft OLE DB Provider for SQL Server, ErrorMsg: Incorrect syntax near the keyword 'Desc'.

View solution in original post

Highlighted
ErikWetterberg
Honored Contributor

Re: SQL code works in SSMS but not in Qlik

I believe Desc (for descending) is a reserved word. Try another name.

Partner
Partner

Re: SQL code works in SSMS but not in Qlik

You would need preceeding load to rename:

PartInquiries:

load [IssueDate]
,b.[PartNo]
,"b.[Description]" as DescB
,d.[SerialNo]
,d.[ReceivedQty]
,i.[TransactionType]
,g.[OrderNo]
,f.[TaskNo]
,c.[Priority]
,f.[TaskText]
,f.[CarriedOutText]
,"f.[Description]" as DescF
,f.[EstManHours]
,"g.[PartNo]" as AcModel
,g.[Reg]
,h.[Status];
SQL SELECT
[IssueDate]
,b.[PartNo]
,b.[Description]
,d.[SerialNo]
,d.[ReceivedQty]
,i.[TransactionType]
,g.[OrderNo]
,f.[TaskNo]
,c.[Priority]
,f.[TaskText]
,f.[CarriedOutText]
,f.[Description]
,f.[EstManHours]
,g.[PartNo]
,g.[Reg]
,h.[Status]