Skip to Content
Sep 01, 2011 at 12:26 PM

SELECT DISTINCT does not work - Another Problem!


Dear All

Previous Post

I just posted a thread, which has been answered by an expert. It's here:

SELECT DISTINCT does not work. Why?

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.


New Problem

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.


Leon Lai





Here's my Original Query

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]
 (T0.[UpdateDate] >= '[%2]' AND
T0.[UpdateDate]  <= '[%3]' AND
T0.[U_SupInv] IS NULL)
(T0.[UpdateDate] >= '[%4]' AND
T0.[UpdateDate]  <= '[%5]' AND 
T0.[U_SupInv] IS NOT NULL)