topic Re: SQL code works in SSMS but not in Qlik in App Development

SQL code works in SSMS but not in Qlik

Cathalc — Sat, 16 Nov 2024 19:28:50 GMT

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

Re: SQL code works in SSMS but not in Qlik

ErikWetterberg — Wed, 11 Dec 2019 10:53:48 GMT

Hi,

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

Re: SQL code works in SSMS but not in Qlik

Cathalc — Thu, 12 Dec 2019 08:48:36 GMT

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

Re: SQL code works in SSMS but not in Qlik

ErikWetterberg — Thu, 12 Dec 2019 08:50:54 GMT

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

Re: SQL code works in SSMS but not in Qlik

Arthur_Fong — Thu, 12 Dec 2019 09:04:58 GMT

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]