I just posted a thread, which has been answered by an expert. It's here:
My original problem was that the Query returned duplicate records, and SELECT DISTINCT did not work.
The solution was to change this select:
T0.[U_SupInv] AS 'Link'
... to this:
CAST(T0.[U_SupInv] as nvarchar(100)) AS 'Link'
The amended Query worked.
I just discovered that resolving the problem created a new one.
The field U_SupInv (Link) is in fact a UDF.
I created it so that all Suppliers' Invoices could be Scanned to pdf files, and these pdf could be linked
to the respective AP Invoice.
Originally, when I clicked 'Link' in my Query results, the pdf scan pops up.
When the Query is amended by adding CAST, the link becomes dead.
Could anyone tell me how I can make SELECT DISTINCT work, as well keeping the LINKS live?
The main purpose of my Query is to help users view the pdf scans by clicking on the links.
Here's my Original Query
SELECT T0.[UpdateDate] AS 'Update Dt', T0.[TaxDate] AS 'Doc Dt', CASE T5.[TransType] WHEN '18' THEN 'PU ' + CONVERT(VARCHAR(6), T0.[DocNum]) WHEN '19' THEN 'PC ' + CONVERT(VARCHAR(6), T0.[DocNum]) END 'SAP Ref.', T1.[ImportLog] AS 'Ship #', T0.[CardCode] + '' AS 'Supplier #', T0.[CardName] AS 'Supplier Name', T0.[DocTotal] AS 'Rs', T1.[BlockNum] AS 'Reqn #', T0.[DocNum] AS 'Doc No', T0.[U_SupInv] AS 'Link' FROM klship.[dbo].[OPCH] T0 INNER JOIN klship.[dbo].[PCH1] T1 ON T0.[DocEntry] = T1.[DocEntry] INNER JOIN klship.[dbo].[OJDT] T5 ON T0.[TransID] = T5.[TransID] WHERE ( (T0.[UpdateDate] >= '[%2]' AND T0.[UpdateDate] <= '[%3]' AND T0.[U_SupInv] IS NULL) OR (T0.[UpdateDate] >= '[%4]' AND T0.[UpdateDate] <= '[%5]' AND T0.[U_SupInv] IS NOT NULL) ) FOR BROWSE